Home |
Search |
Today's Posts |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy data from sheet2 to sheet1 when sheet2 has variable # of
OK, there isn't a way to combine the
=IF(Sheet2!A1="","",Sheet2!A1) formula with something like another IF statement, where I could say ISBLANK = TRUE and get Excel to select the rows in sheet2 that aren't blank (i.e., all the rows that have data in them)? I have something like 650 worksheets to work through, so any automation on the copy/paste from sheet2 to sheet1 would be WONDERFUL. Anne "Sheeloo" wrote: You can find, by a formula, the last row in Col of Sheet2 but you still will have to copy the formula down unless you do that through a macro... "Anne" wrote: Hello! Thanks! When I do =IF(Sheet2!A1= "","",Sheet2!A1) that works, but then I have to manually copy the formula down however many rows I have in sheet2 (which varies considerably, depending on the report) in sheet1. For example, if I have 436 rows in sheet2, I have to manually copy the formula down 435 rows in sheet1. Is there a way, using the formula above, to check in sheet2 column A for the last row with data? Thanks Anne "Sheeloo" wrote: You can not... without using formulas. In any Excel you have a formula or you don't... One way is to use something like =IF('sheet2'!a2="","",'sheet2'!a2) so that the value shows up in Sheet1 only if it is there in Sheet2 With macro you need two steps 1. Find the last row in Sheet2 With Sheets("Sheet2") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row End With 2. Copy and paste values/formulas from sheet2 to sheet1 "Anne" wrote: Hello! I'd like to be able to copy data from sheet2 to sheet1. I know the data always starts on A2 in both sheets. However, in sheet2, the row count can be anywhere from 1 to ??. How can I do a formula that allows me to automate the copy procedure from sheet2 to sheet1 this way? I know the way to copy from sheet2 to sheet1 for a single row is: in sheet1:A2, type in the formula ='sheet2'!a2 If I copy and paste special/formula from sheet1:a3 to a-whatever down the page in sheet1, it works. But I'd like to be able to tell Excel how to adjust for the varying numbers of rows in sheet2. Thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Copy data from a list in sheet1 and paste into sheet2 | Excel Discussion (Misc queries) | |||
how do copy "sheet1!A1+1 in sheet2 to sheet 3 and get "sheet2!A1+ | Excel Discussion (Misc queries) | |||
Copy result from sheet1 to sheet2 | Excel Discussion (Misc queries) | |||
Display Rows From Sheet1 In Sheet2 (Import) | Excel Worksheet Functions | |||
Copy values from Sheet1 to Sheet2 | Excel Discussion (Misc queries) |