Combining data from different sheets you need to keep it from overlapping or simply overwriting. To do this you need to find the end row of the data and then paste just below it.
This is done with the following code .End(xlUp)(2).Select. It basically goes up from a set point in a particular column and selects the empty cell just below the data. If you use it without the (2) it would select the last cell row with data (overwriting it when you paste). Since Excel can have 65536 rows you start it from this point so that it will not start in the middle of data.
So the context going from the lowest row of column b possible and then finding highest empty row is
Range("B65536").End(xlUp)(2).Select
If I where to make a macro to combine 2 tables from different sheets both with same column titles I would do it this way.
Sub Macro2()
'
' Macro2 Macro
'
'
Sheets("Sheet1").Select
Range("Table1").Select
Selection.Copy
Sheets("Sheet3").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Range("Table2").Select
Selection.Copy
Sheets("Sheet3").Select
Range("B65536").End(xlUp)(2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
If I where to combine two sheets I would remove the table reference and insert range or row selections such as below:
.
Sub Macro3()
'
' Macro3 Macro
'
'
Sheets("Sheet1").Select
Rows("2:65536").Select
Selection.Copy
Sheets("Sheet3").Select
Range("a2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Rows("2:65536").Select
Selection.Copy
Sheets("Sheet3").Select
Range("a65536").End(xlUp)(2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
If this helps please comment. I am also available for consulting from 50.00. You may use my http://hilbrandsdesign.com/ website for contact or more information.
Thanks, John
Comments