Combining data from multiple worksheets.

[Olmsted57]

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!

 

[Joel]

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]

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]

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]

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]

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]

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]

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