| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copy data from sheet2 to sheet1 when sheet2 has variable # of rows
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copy data from sheet2 to sheet1 when sheet2 has variable # of
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copy data from sheet2 to sheet1 when sheet2 has variable # of
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! |
|
#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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copy data from sheet2 to sheet1 when sheet2 has variable # of
You can copy the data from sheet2 to sheet1 by the macro below;
Sub test() With Sheets("Sheet2") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row ..Rows("1:" & lastrow).Copy Destination:=Sheets("Sheet1").Range("A1") End With End Sub What do you really want to do? Merger all 650 worksheets into one? Do you just want to copy, or link? "Anne" wrote: 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! |
| Reply |
| 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) | |||
Hybrid Mode
