Macro that copies a table from worksheet under a table in another 1

[RyanScharfy]

Example (Using Excel 2003)

Sheet1 - Table1, variable rows

Sheet2 - Table2, same format, variable rows.

I want to copy Table2 directly underneat Table 1 so it is one big table (basically merge the tables)

Here's my code - less the missing piece

Sub CopyTable()
'
' CopyTable Macro
'
' Go to row 2 and highlight all data below and copy
Sheets("Sheet2").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Go to Sheet 1, and scroll down column A to get to the
' end of the table
Sheets("Sheet1").Select
Range("A1").Select
Selection.End(xlDown).Select

' How to do go one more cell down?

' Paste
ActiveSheet.Paste
End Sub

 

[SkipVought]

Hi,

I AVOID using the Select and Activate methods...

Sub CopyTable()
'
' CopyTable Macro
'
    Dim lRow As Long
' Go to row 2 and highlight all data below and copy
    Range(Sheets("Sheet2").Rows("2:2"), Sheets("Sheet2").Rows("2:2").End(xlDown)).Copy

' Go to Sheet 1, and scroll down column A to get to the
' end of the table
    With Sheets("Sheet1")
        lRow = .Range("A1").End(xlDown).Row + 1
        .Cells(lRow, "A").PasteSpecial xlPasteAll
    End With
End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RyanScharfy]

Skip,

Worked like a charm. If I might be so bold as to ask... you have short version of why you dislike the Select and Activate method? Does it tend to get a little buggy compared to your method depending on the lists in question?

 

[SkipVought]

Good coders use the Select and Activate methods sparingly.

faq707-4105

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]