Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a
worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
Sub movedata()
With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
Joel-
Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
Sorry, didn't realize this was the General Question site and not the
Programming site. People ask the same questions on both sites. This is VBA code which is another name for a macro or subroutine or function. The code is referencing the worksheet names on the bottom of the spreadsheet (sheet1,sheet2,sheet3). Change these names if they don't match you worksheets. You called them W1, W2, W3. You have to copy and paste this code into the VBA. The code starts at the word "SUB" and ends at "END SUB". Now follow these instructions. 1) Go to Tools MENU - MACRO - Visual Basic Editor 2) From VBA window - INSERT MENU - MODULE 3) Paste code in this window. 4) Go back to worksheet 5) Run macro by going to Tools MENU - MACRO - MACRO - MOVEDATA (this is the name of the subroutine that you added to the VBA window) The macro just ran and the data should be moved to the 3rd worksheet. "Olmsted57" wrote: Joel- Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
Thanks again Joel. I implemented this VBA, and it works good. HOWEVER, I
oversimplified my Spreadsheet because I thought I would be able to make the minor changes I needed.... Yeah, right... I actually have 6 Worksheets (Day1, Day2, Day3, Day4, Day5, Day6) that I need to copy data over from (to the AllDeals sheet)- no problem really, I think I can see how to change the code to include 6 sheets instead of 2. My problem is that each sheet actually contains 22 Columns of data, 20 rows. The first column is Customer, the other 21 are each values which are unique to that customer. 20 rows, because at most I would sell to 20 customers in any given day of the 6 day sale. When I copy the data over to the AllDeals sheet, I need all 22 columns of data to transfer. I wasn't able to determine how to change the range in the code to include everything from A6:W25 (without the blank rows). The range is the same on Day1 thru Day6 (A6:W25). I know you don't get paid for helping with this, but if there is any way I can repay you for your help... THANKS A MILLION! Mike "Joel" wrote: Sorry, didn't realize this was the General Question site and not the Programming site. People ask the same questions on both sites. This is VBA code which is another name for a macro or subroutine or function. The code is referencing the worksheet names on the bottom of the spreadsheet (sheet1,sheet2,sheet3). Change these names if they don't match you worksheets. You called them W1, W2, W3. You have to copy and paste this code into the VBA. The code starts at the word "SUB" and ends at "END SUB". Now follow these instructions. 1) Go to Tools MENU - MACRO - Visual Basic Editor 2) From VBA window - INSERT MENU - MODULE 3) Paste code in this window. 4) Go back to worksheet 5) Run macro by going to Tools MENU - MACRO - MACRO - MOVEDATA (this is the name of the subroutine that you added to the VBA window) The macro just ran and the data should be moved to the 3rd worksheet. "Olmsted57" wrote: Joel- Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
I'll teach you how to simply make the change
the secret is in this statement Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) The left cell is the starting cell and the right the end cell Column 22 is V Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "V")) Make this change in 3 places, or 6 with your other change "Olmsted57" wrote: Thanks again Joel. I implemented this VBA, and it works good. HOWEVER, I oversimplified my Spreadsheet because I thought I would be able to make the minor changes I needed.... Yeah, right... I actually have 6 Worksheets (Day1, Day2, Day3, Day4, Day5, Day6) that I need to copy data over from (to the AllDeals sheet)- no problem really, I think I can see how to change the code to include 6 sheets instead of 2. My problem is that each sheet actually contains 22 Columns of data, 20 rows. The first column is Customer, the other 21 are each values which are unique to that customer. 20 rows, because at most I would sell to 20 customers in any given day of the 6 day sale. When I copy the data over to the AllDeals sheet, I need all 22 columns of data to transfer. I wasn't able to determine how to change the range in the code to include everything from A6:W25 (without the blank rows). The range is the same on Day1 thru Day6 (A6:W25). I know you don't get paid for helping with this, but if there is any way I can repay you for your help... THANKS A MILLION! Mike "Joel" wrote: Sorry, didn't realize this was the General Question site and not the Programming site. People ask the same questions on both sites. This is VBA code which is another name for a macro or subroutine or function. The code is referencing the worksheet names on the bottom of the spreadsheet (sheet1,sheet2,sheet3). Change these names if they don't match you worksheets. You called them W1, W2, W3. You have to copy and paste this code into the VBA. The code starts at the word "SUB" and ends at "END SUB". Now follow these instructions. 1) Go to Tools MENU - MACRO - Visual Basic Editor 2) From VBA window - INSERT MENU - MODULE 3) Paste code in this window. 4) Go back to worksheet 5) Run macro by going to Tools MENU - MACRO - MACRO - MOVEDATA (this is the name of the subroutine that you added to the VBA window) The macro just ran and the data should be moved to the 3rd worksheet. "Olmsted57" wrote: Joel- Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
I'm so close I can taste it!!!
It is now copying all the data over to the "AllDeals" sheet, but instead of listing it left to right, then the next row of data, so on... it lists everything in 1 column Another simple fix? "Joel" wrote: I'll teach you how to simply make the change the secret is in this statement Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) The left cell is the starting cell and the right the end cell Column 22 is V Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "V")) Make this change in 3 places, or 6 with your other change "Olmsted57" wrote: Thanks again Joel. I implemented this VBA, and it works good. HOWEVER, I oversimplified my Spreadsheet because I thought I would be able to make the minor changes I needed.... Yeah, right... I actually have 6 Worksheets (Day1, Day2, Day3, Day4, Day5, Day6) that I need to copy data over from (to the AllDeals sheet)- no problem really, I think I can see how to change the code to include 6 sheets instead of 2. My problem is that each sheet actually contains 22 Columns of data, 20 rows. The first column is Customer, the other 21 are each values which are unique to that customer. 20 rows, because at most I would sell to 20 customers in any given day of the 6 day sale. When I copy the data over to the AllDeals sheet, I need all 22 columns of data to transfer. I wasn't able to determine how to change the range in the code to include everything from A6:W25 (without the blank rows). The range is the same on Day1 thru Day6 (A6:W25). I know you don't get paid for helping with this, but if there is any way I can repay you for your help... THANKS A MILLION! Mike "Joel" wrote: Sorry, didn't realize this was the General Question site and not the Programming site. People ask the same questions on both sites. This is VBA code which is another name for a macro or subroutine or function. The code is referencing the worksheet names on the bottom of the spreadsheet (sheet1,sheet2,sheet3). Change these names if they don't match you worksheets. You called them W1, W2, W3. You have to copy and paste this code into the VBA. The code starts at the word "SUB" and ends at "END SUB". Now follow these instructions. 1) Go to Tools MENU - MACRO - Visual Basic Editor 2) From VBA window - INSERT MENU - MODULE 3) Paste code in this window. 4) Go back to worksheet 5) Run macro by going to Tools MENU - MACRO - MACRO - MOVEDATA (this is the name of the subroutine that you added to the VBA window) The macro just ran and the data should be moved to the 3rd worksheet. "Olmsted57" wrote: Joel- Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining data from multiple worksheets.
I should of tested the last change. I thought something simple was going to
work. The problem with VBA is code that works in one application doesn't work when you make minor changes. I had to change the way I did the copy. Changed V back to a Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then cell.EntireRow.Copy Destination:= _ Sheets("Sheet3").Cells(RowCount, "A") RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then cell.EntireRow.Copy Destination:= _ Sheets("Sheet3").Cells(RowCount, "A") RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I'm so close I can taste it!!! It is now copying all the data over to the "AllDeals" sheet, but instead of listing it left to right, then the next row of data, so on... it lists everything in 1 column Another simple fix? "Joel" wrote: I'll teach you how to simply make the change the secret is in this statement Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) The left cell is the starting cell and the right the end cell Column 22 is V Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "V")) Make this change in 3 places, or 6 with your other change "Olmsted57" wrote: Thanks again Joel. I implemented this VBA, and it works good. HOWEVER, I oversimplified my Spreadsheet because I thought I would be able to make the minor changes I needed.... Yeah, right... I actually have 6 Worksheets (Day1, Day2, Day3, Day4, Day5, Day6) that I need to copy data over from (to the AllDeals sheet)- no problem really, I think I can see how to change the code to include 6 sheets instead of 2. My problem is that each sheet actually contains 22 Columns of data, 20 rows. The first column is Customer, the other 21 are each values which are unique to that customer. 20 rows, because at most I would sell to 20 customers in any given day of the 6 day sale. When I copy the data over to the AllDeals sheet, I need all 22 columns of data to transfer. I wasn't able to determine how to change the range in the code to include everything from A6:W25 (without the blank rows). The range is the same on Day1 thru Day6 (A6:W25). I know you don't get paid for helping with this, but if there is any way I can repay you for your help... THANKS A MILLION! Mike "Joel" wrote: Sorry, didn't realize this was the General Question site and not the Programming site. People ask the same questions on both sites. This is VBA code which is another name for a macro or subroutine or function. The code is referencing the worksheet names on the bottom of the spreadsheet (sheet1,sheet2,sheet3). Change these names if they don't match you worksheets. You called them W1, W2, W3. You have to copy and paste this code into the VBA. The code starts at the word "SUB" and ends at "END SUB". Now follow these instructions. 1) Go to Tools MENU - MACRO - Visual Basic Editor 2) From VBA window - INSERT MENU - MODULE 3) Paste code in this window. 4) Go back to worksheet 5) Run macro by going to Tools MENU - MACRO - MACRO - MOVEDATA (this is the name of the subroutine that you added to the VBA window) The macro just ran and the data should be moved to the 3rd worksheet. "Olmsted57" wrote: Joel- Thanks for the help, BUT... I'm kind of a rookie :-( Can you tell me what to do with this? Thanks a million! "Joel" wrote: Sub movedata() With Sheets("sheet1") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With RowCount = 1 For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell With Sheets("sheet2") Lastrow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set copyrange = _ Range(.Cells(1, "A"), .Cells(Lastrow, "A")) End With For Each cell In copyrange If Not IsEmpty(cell) Then Sheets("Sheet3").Cells(RowCount, "A") = cell RowCount = RowCount + 1 End If Next cell End Sub "Olmsted57" wrote: I have 3 worksheets (W1, W2, W3). W1 has data in cells A1 through A10 (in a worksheet that has 25 rows). W2 has data in cells A1 through A5 (in a worksheet that has 25 rows). I would like W3 to list W1 A1:A10, then W2 A1:A5 in its A column, but ignoring any blank rows. HELP!!!! I know there must be an easy way to do this! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Multiple Worksheets | Excel Discussion (Misc queries) | |||
combining multiple worksheets | Excel Discussion (Misc queries) | |||
Combining multiple worksheets | Excel Worksheet Functions | |||
Combining data from multiple worksheets and separate spreadsheets | Excel Discussion (Misc queries) | |||
Combining specific ranges from multiple worksheets into one | Excel Worksheet Functions |