Free Microsoft Excel 2013 Quick Reference

- Formulas from columns to rows between data sheets
- Copy Formula to Same Cell New Worksheet and Increment Row by 1
- #REF errors on formula laden sheet when data on the referenced sheet moves...??
- Macro to copy selected columns, convert to number and paste to new tab
- Conditional Formatting using a formula referring to another sheet
- Add value to a formula
- Use VBA to copy two columns from one sheet to another
- Copy Sheets From 1 Workbook Into Multiple Files
- Paste Values To Last Used Row in Column
- Replace Sheet Name in Formulas
- IF unique cell, copy row to another worksheet
- Copying New First/Last names from Roster and Inserting into New Rows in diff sheet
- Compare 2 Columns & Copy Common Cell Rows
- Copy Dynamic Range Without Duplicates
- Move Rows Based On A Criteria
- Fill Formula Row Down Based On Data In Another Sheet
- Code to query data from multiple worksheets
- VBA: Copy - Pastespecial Format
- Loop thru rows & copy to a column
- Getting data from a row to a column?

I couldnt find anything on here so i thought id pop the question its probably really simple but i just cant seem to figure it out,

What it is i have two data sheets.

i want to display the content of data sheet1 on to data sheet2,

Which is easy i know how to do that how ever i want the columns to be displayed as rows and the rows as columns as i have quiet a bit of data to convert between the two, the formula im using is

but if i copy it across it gives me E37 where as i want D38 etc

after that i will need to do E37 across

so any help would be great full

Regards

Shane

I have a workbook with 2 worksheets. Sheet1 contains transactional data for entities. For each entity on sheet1 there are 7 rows of data. The next 7 rows on sheet1 are for the second entity and so on down sheet1. Sheet2 contains a bunch of formula cells that reference and aggregate the data for the first entity(top 7 rows) on sheet1. My plan is to copy the aggregated data off sheet2 for the first entity into document A, then delete the first 7 rows of data from sheet1 to move the data for the second entity into the top 7 rows on sheet1, recalc sheet2 to aggregate the data for the second entity, copy the aggregated data for sheet2 into document A....and so on down through the entity data in sheet1.

When I do the row deletions from sheet1 I get #REF errors in sheet2 even though new data moved into the sheet1 rows that are referenced by the formulas on sheet2. How can I change the formula's in sheet2 so they don't #REF?

I need a macro that will copy selected columns (from 2 different worksheets), paste to a new tab and convert the existing formulas to values (I need the conversion done as I have another autofilter macro that I need to run and it won't work unless the formulas are converted to statuic values).

Background: This macro will be run by very basic excel users on a monthly basis (showing monthly totals [sheet 1] and annual totals [sheet 2] together). Each user only wants to see certain columns in their report, so I want to set up macro assigned buttons for them that will show only the columns they want to see in the new tab that is created.

Example: User1 only wants to see columns that show "Acting" hours, so the end result will be to pull columns [E:F, H:I, J:K, AD:AH] [from both sheet1 and sheet2] to a new sheet3

User2 only wants to see "Tech" hours, so the end result will be to pull columns [Y:AC] [from both sheet1 and sheet2] to a new sheet3

I don't need separate macros - just one will do and I can alter it to filter for different requests

TIA

Work book attached - note: the values in this workbook have already been converted from forumlas as they would normally be taken from another workbook, but it gets complicated

The formula I currently have setup in Sheet1: =NOT(EXACT(A1,'Sheet2'!A1)). The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)). In the 'Applies to' box in the Conditional Formatting Rules Manager, I've got =$A:$IV.

This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconciliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area.

Is there a way within the Conditional Formatting to apply the formula statically to the entire worksheet no matter what is copied/cut/inserted/pasted/deleted? I tried an Index formula but couldn't get it to work. I have posted this also to Excel Forum (http://www.excelforum.com/excel-gene...62#post2752662), and I did get one response; however, that response did not work either.

I would appreciate any advice. Thanks.

Frank

Does anyone know if it is possible to add a value to a formula?

I know Excel does this by itself when you copy and paste the formula in a different cell, but I need the value to increase with 5, not 1.

What I have done is type in the formula:

[=Sheet1!D10] in one cell [=Sheet1!E10] in the cell to it's right, etc in five columns. (D to H)

I have sets of five columns in my current spreadsheet (Sheet2) with this formula, but the formula in the sets points to different rows in Sheet1.

Sheet1 has 800 rows of data that are sorted numeric row by row. In Sheet2 the data is sorted in five columns. (The data go from being displayed vertically to horizontal)

What I want is to copy 5 formulas from say row 10 in Sheet2: [=Sheet1!D10] to [=Sheet1!H10] and add a value of 5 to get the following formula in the next row: [=Sheet1!D15] to [=Sheet1!H15].

I don't know if this is possible, but if anyone could teach me how to do this, I'd be very grateful.

Thank you.

I've used the record macro function to write some code that makes formatting a spreadsheet a bit easier for me.

The code needs to copy the formulas from two columns in one sheet (say columns J and K) and then paste them to columns J and K in another sheet.

The problem i'm running into is that the macro selects an entire table of columns (14 columns total) and then pastes the whole range starting from J in the next sheet. Nothing I can see in the code should cause this, i've stepped through debugger and watched the macro select the correct columns and then the next step it suddenly selects 14 columns and copies.

Here's a section of the code:

VB:Sheets("Sheet1").Select Columns("J:K").Select Selection.Copy ActiveWindow.ScrollWorkbookTabs Position:=xlLast Sheets("Sheet2").Select Range("J1").Select ActiveSheet.PasteIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

From stepping through debugger, it appears that the J:K selection is actually selecting H:U somehow and copying those columns.

If anyone can help me out here it would be appreciated, I can't find anything else in searches of a similar incident

i am making annual changes to our companies performance review. All job descriptions (roughly 350) have their own excel workbook containing three sheets. the first is a template that the manager fills in relative to any given job description. the second two sheets take that raw data and use formulas to allow for performance review (sheet 2) and and an action plan (sheet 3). I need to make changes to the second and third sheet, while preserving the first sheet on all 350 files. i have a source file "Job Description-Review 3.0 macro.xls" that contains the improvements to sheets 2&3 that i need to copy and paste to all the 350 files. The sheets i need to change are protected, but not with a password. The ideal solution would serially open the 350 books, unprotect the sheets, copy the new sheets, paste in the two new sheets, re-protect the sheets, then rename the respective files by replacing "2.0" with "3.0". All 350 files have "2.0" at the end of their title currently.

the source from which i want to copy is entitled/located at:

U:ODPerf review2010toolsTest folderJob Description-Review 3.0 macro.xls

an example name/location of the 350 files would look like this:

U:ODPerf review2010toolsTest folder128 Unit Secretary DSC 2.0.xls

The name of the sheets in all workbooks are identical:

Sheet1 = "Job Description"

Sheet2 = "Job Specific Eval"

Sheet 3 = "Ed-Comp Plan"

thanks in advance for any help you can give.

kuriti

In second sheet, I have a simple If condition that checks to see if new items from Sheet1 meet a condition (eg, if startdate from sheet1 = today()).

I am trying to copy the cells from column A and paste the values in my other sheet. I want it to append after the last cell in column A of my sheet 3.

VB:The macro does not paste at all.CopyNewCRs() Sheets("Sheet2").Select Range("A2").End(xlDown).Select Application.CutCopyMode = True Selection.Formula = Selection.Value Worksheets("Sheet3").Activate Sheets("Sheet3").Select Set myRange = Sheets("Sheet3").Range("A2").End(xlDown).Offset(1, 0) Worksheets("SDP_CR_Status").Range("myRange").PasteSpecial xlPasteValues, SkipBlanks _ :=True, Transpose:=False End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please help. Really appreciate it.

PS: I have been using excel for a while but I just starting hardcoding VBA.

Thanks in advance for your help.

This is my first post, so I hope I do this correctly.

I found another thread here that explains how to do the find and replace portion and even says "Also you may need to change the LookAt part to search formulas rather than the calculated value of the cell."

I have looked and looked but can not find or figure out how, or what, to change to search formulas instead of the calculated value of the cell.

I am writing code that will copy 2 sheets to new sheets and then rename the new sheets. Sheet1 and Sheet2 are the original sheets with Sheet2 having formulas that reference cells in Sheet1. I am creating new Sheet3 from Sheet1 and new Sheet4 from Sheet2 and wanto to find and replace all references to Sheet1 in Sheet4 to reference Sheet3 instead.

Hopefully this makes sense.

TIA,

Jeff

I want to thank you in advance. Because I am a SUPER NEWBIE AT THIS (but I am willing to try).

Dilemma is...Workbook contains 8 worksheets which are titled (SHARE, NONSHARE, FRASER,ELLIOTT,UNIVERSAL,CHEET SHEET,SHEET1 AND SHEET2)

In SHEET1 have formula =IF(ISNA(MATCH('CHEET SHEET'!B2,Sheet2!B:B,0)),"No Match",INDEX(Sheet2!A:A,MATCH('CHEET SHEET'!B2,Sheet2!B:B,0))) which just lists a NAME. (ie-SHEET1,A1="FRASER")

on CHEET SHEET (have listbox with several names-(A2) and (B2)-=LEFT(A2,2)*1 (which SHEET1 formula reads to populate "FRASER")

lastly I have manually inputted data in row CHEET SHEET(D2:G2) which is data for FRASER.

(Hopefully you can keep up with this)

Now, what I want to do is create a Macro SHEET1-column A which lists names pulled from CHEET SHEET (formula above) to be able to copy the data from CHEET SHEET (D2:G2) into the correct worksheet by the name (ex: FRASER is populated in SHEET1 A1 and data D2:G2 will copy into FRASER A2)

If SHEET1 A2 has UNIVERSAL that data from D3:G3 would copy into the worksheet named UNIVERSAL, and if the SHEET1 A3 was UNIVERSAL the data from CHEET SHEET (D4:G4) would consecutively populated after the last line in the UNIVERSAL worksheet (ie-UNIVERSAL -A3...).

I have attached a "trial" for you to be able to look at what I want to accomplish--I had to remove other WORKSHEETS, but left FRASER in there.

I know this seems like a lot of confusion--hopefully you can help me.

Cheers,

Letsgowild

I've been able to somewhat achieve this by vlookup, but I would prefer a macro loop that goes through the dynamic named range and finds new entries and inserts a new row in sheet2.

I have attached the workbook with dummy values. Sheet2 is where all the action happens. To give an idea of the process, a technician exports values from the web into an Excel file. They then "copy" the Last Name & First Name columns from the export file and "paste" them into Sheet1. I need a macro that, when executed, compares Existing Last Name & First Name from Sheet2 to Sheet1 and creates new formatted rows for the new names (the rows will be blank, but will have the same format as the row above).

I've been researching this for days, trying different variations, etc., and have all but given up. I can feel that it is just out of reach, but all of the different function terms are starting to confuse me. I have some experimental sheets in the book, but all I care about are Sheet1 and Sheet2. You can see what Sheet2 should look like, it just needs to be automated.

In the book I attached, there is a new client in Sheet1 that is not in the Sheet2. I have a "manual" insert row macro that keeps the same format, but doesn't compare to the other list to find "new" names.

Also, if Sheet2 could expand and contract as needed by adding rows for new clients on Sheet1, and deleting names no longer on Sheet1, then I wouldn't have to "pre-format" 500 rows. End-user simplicity is preferred, and I will be hiding all sheets other than Sheet1 and Sheet2, so, anything we can do to automate as much as possible is the way to go.

If someone knows how to do it, but is only willing to write this up for a fee, please inbox me with what it would take for you to do the code. I am doing this as part of my internship, so I can't really afford to pay for it. But, if it's the difference between getting the macro and "TLDR", then I'll do what I have to.

Thanks in advance for any advice you can offer. I will check back frequently to see if there are any questions or suggestions.

To summize as this is not quite working, I have two worksheets and in Column A are id#s. Worksheet 1 has100's more rows of data than Worksheet 2. I need to find the unique ids from column A in Worksheet 1 that match Unique IDs in worksheet 2.

I need to copy the row of cells from worksheet 1 where the unique id equals that u.id in worksheet 2. That row needs to be copied to Sheet 2 that has the same unique id, and after the pre-existing cells which are already present.

So for instance: if Sheet1 A100 = Sheet2 A24, then copy row A100from Sheet 1 and paste it to Row A24 of SHeet 2 start with the first empty cell- just say Sheet2 G24 as an example.

Using the formula I was given before, it finds matches, but the pasted rows are off. If the match occurs with Sheet 1 A450 and Sheet 2 A36, it will copy the value from ROW/SHeet 1 A36 instead of ROW/Sheet 1 A450

Thanks for any help

Being a newbie to excel/vba I'm not sure where to begin with this one. Basically in sheet 1 column A I have a long list of part codes. This is an ever expanding list so I assume I need to make it a dynamic range. This list has duplicate entries of certain part codes which is fine. I need to keep sheet 2 up to date with these part codes but I don't want duplicates on this sheet. I was then going to use vlookup to derive some but not all information for the part from sheet 1. I would also like the vlookup formula to expand as new parts are added to sheet1 and then subsequently copied to sheet2.

Any help would be much appreciated :-)

Cell a1:a3000 contains a lot of trackingnumbers.

Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria.

What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"

I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature.

Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet

About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B

Can you help me?

I've been searching for months and haven't managed to find an answer to my conundrum.

I Have a workbook with 2 sheets.

Sheet1 is where I import my raw data from SAP for manipulation. no formulas etc, just data. each row is a unique data set. there can be a different amount of data every time I import. The constants on this sheet are: headerrow is row6, row7 is always blank, dataset starts on row8 and can be just a couple of rows or hundreds of rows

Sheet 2 is where I manipulate the data with formulas and macros. the sheet presents my data in a nice printable readable fashion. I already have macros that generate my formulas for my header row (row6) and my data set (row7) (again blank) constants on this sheet match sheet 1 (ie headerrow is row6, row 7 will have no data but it does have my formulas that need to be copied) actual output starts at row8

What I need is a macro that will find out how many rows of data I have on sheet1, and then copy my row of formulas on sheet2 from row7 all the way down for the amount of data that I have in sheet 1.

workbook is coded for excel 2003.

Thanks

In â€śsheet1â€ť how do I find the value generated by formula in ck145:ck148 in every instance from the source range (D5:IT7) in sheet2 named â€ś Sheet 11 â€ť and copy the contents in this sheet from the cell in the previous column on the same row as the match to â€śsheet1â€ť, in the CL column starting on (row 145 for ck145 matches) and (row 146 for ck146 matches) and (row 147 for ck147 matches) and (row 148 for ck148 matches) .

Also, I have other worksheets I would like to update the code to query data from into sheet 1.

rngArray = Array("D5:IT7", "D5:IT28", "D5:IT144", "D5:IT645") : sheet 11, 12, 13 and 14

OPArray = Array("ck145:ck148", "ck149:ck152", "ck153:ck156", "ck157:ck160") :sheet1

a = Sheets("sheet " & 11 + n).Range(rngArray(n)).Value : sheet 11, 12,13 and 14

The values in ck145:ck160 are preselected by a formula and should not be by this code. The code should go and find the matches to the values generated by the formulas from ck145:ck160 in sheet1, to those in sheet 11, 12, 13 and 14, and place the content from the cell in the previous column same row in each instance into the CL column of sheet1.

For example if ck145=10 in sheet1, go to sheet 11 and find all matches in the selected range that equal 10, then for each true occurrence, copy the content in the cell from the previous column on the same row and paste into sheet1 starting at CL145.

Use the same function for whatever value is generated (eg. =MAX('Sheet 11'!$D$5:$IT$7)), in ck146, ck147 and ck148. Find the values from those cells in sheet 11, then for each true occurrence, copy the content in the cell from the previous column on the same row and paste into sheet1 starting at CL146 for the matches to ck146 and CL147 for the matches to CK147 and CL148 for the matches to CK148.

Also, the content output should be listed in numerical order.

example 1,3 before 6,16 or 9,10 before 13,21

(see partial sample worksheets in the attachment)

I'm trying to combine copy and pastespecial format to one line.

I was pointed to these example codes. I love the idea of copy and paste without using the clipboard but I cannot convert to Pastespecial- Format only.

VB:NoCopyAndPaste() 'Instead of: Sheet1.Range("A1:A200").Copy Sheet2.Range("B1").pasteSpecial Application.CutCopyMode=False 'Clear Clipboard 'Use: 'By-passes the Clipboard Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1") 'Or, if only values are needed: Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value 'Or, if only formulae are needed: Sheet2.Range("B1:B200").Formula = Sheet1.Range("A1:A200").Formula 'See also FormulaArray and FormulaR1C1 etc 'Instead of: Sheet1.Range("A1:A200").Copy Sheet1.Range("A1:A200").PasteSpecial xlPasteValues Application.CutCopyMode=False 'Clear Clipboard 'Use: Sheet1.Range("A1:A200") = Sheet1.Range("A1:A200").Value End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a workbook with multiple worksheets. In Sheet1, I have an unknown number of rows (corresponding to products), each with unknown number of columns (corresponding to time periods).

I need to copy and transpose the data from each row, reading one row at a time and writing it into column C in Sheet2. So, I need to write a macro which:

- Loops through all non-blank rows of Sheet1

- Copies the value in each non-blank cell of each non-blank row

- Writes the values into column C of Sheet2

- Repeats the process (overwriting or deleting the previous row's data) until we find the first blank row of Sheet1.

In case it matters, I have formulas in Sheet2 which act upon each rows data.

I will later add a similar set of code that takes the formula output resulting from each loop, from Sheet2 and writing it to Sheet3 before allowing the loop to continue to get the next row of data from Sheet1.

Being a newbie to macros I've tried several ways to do this, but always end in failure (or those Oh-so-informative Microsoft error messages). So any help you can give will be greatly appreciated.

Thanks in advance!

This is no problem, I used

=+'Sheet1'!B39

or even

='Sheet1'B39

The problem is that when I want B13 on Sheet1 to equal C39 on Sheet2, I have to type in the individual formula in order to make it work. If I copy and paste it Sheet1 B13 equals Sheet2 B40 (wrong column, wrong row).

I tried a formula like this:

=INDIRECT(ADDRESS(COLUMN()-26,2))

but it only worked for the same sheet and even then I couldn't figure it out.

Anyone know of a way for me to copy and paste the formula so that the orientation (data retrieval from Sheet2) will be horizontal rather than vertical?

,Thanks

Justis

No luck finding an answer? You could always try Google.