| 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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
copy data from sheet2 to sheet1 when sheet2 has variable # of
Hello!
We have many reports which are Excel files. Each of these reports has to be copied into a specially formatted Excel sheet. So... I set up the specially formatted sheet and the Excel report (from MicroStrategy) in one workbook. I know that: Sheet1!A1:C1 has column headers of Vendor Name, Description and Amount Paid. Sheet2!B1:D1 has column headers of Vendor Name, Description and Amount Paid. Sheet2!B2 to B(whatever) has vendor names (character data). Sheet2!C2 to C(whatever) has description data (again, character data). Sheet2!D2 to D(whatever) has the amount paid to each vendor (as currency data). Sheet2 can run anywhere from 2 rows of vendor data to over 4000 rows. So I was hoping for something like the macro you've shown me (haven't tested it yet) to help the copying/pasting process from Sheet2 (the MicroStrategy vendor report, exported as Excel) to Sheet1 (the formatted report I will eventually export as a PDF file for posting to a web site). Thanks for your help... I'll try the macro and see what happens. Anne "Sheeloo" wrote: 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) | |||
Linear Mode
