How to copy and paste all but row 1 from from a different work sheet

Question

I want to select all the rows in “Data” worksheet except the first row and then copy and paste these selected rows into the next availbale row of my “Historical Data” sheet. I can copy and paste from data to historical data using code 1. And I can select all but row one in the active worksheet using code 2. But how can I combine them?

Code 1:

Copying and pasting 
Sheets("Data").Range("A2:H2").Copy
With Sheets("Historical Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
End With

Code 2:

Selecting all but first Row
Dim xColIndex As Integer
Dim xRowIndex As Integer
xIndex = Application.ActiveCell.Column
xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select

Change the `Select` to `Copy`... but use `Long` instead of `Integer`, and qualify which worksheet the `Range` and `Cells` are on. — BigBen, Jan 21 '20 at 14:16
Use a `With` statement: `With ThisWorkbook.Sheets("Data")`, then add a period `.` before each instance of `Range` and `Cells`. — BigBen, Jan 21 '20 at 14:26
**[1.]** Find last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) **[2.]** Then construct your range. For example `Set rng = Range("A2:H" & LastRow)`. You can then copy this range **[3.]** If you do not know your last column then you can use the same logic to find the last column as well. — Siddharth Rout, Jan 21 '20 at 14:29
Will `H` always be your last column? And are both sheets `Data` and `Historical Data` in the same workbook? — Siddharth Rout, Jan 21 '20 at 14:37
yes H will always be my last column. Data and Historical data are different sheets in the same workbook — kitchen800, Jan 21 '20 at 14:38

score 1 · Accepted Answer · answered Jan 21 '20 at 14:45

yes H will always be my last column. Data and Historical data are different sheets in the same workbook – pete800 6 mins ago

As I mentioned in the comment, find last row and then construct your range to copy across.

Is this what you are trying? Paste this code in a module of the workbook which has both sheets.

Option Explicit

Sub Sample()
    Dim wsThis As Worksheet, wsThat As Worksheet
    Dim lRow As Long
    Dim rng As Range

    Set wsThis = Sheets("Data")
    Set wsThat = Sheets("Historical Data")

    '~~> Find last row in Data Sheet
    lRow = wsThis.Range("A" & wsThis.Rows.Count).End(xlUp).Row

    '~~> Construct your range
    Set rng = wsThis.Range("A2:H" & lRow)

    '~~> Copy the range
    rng.Copy

    '~~> Paste the range
    With wsThat.Range("A" & wsThat.Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial Paste:=xlPasteColumnWidths
        .PasteSpecial Paste:=xlPasteValues
    End With
End Sub

Interesting reads

As @BigBen was trying to suggest, avoid the use of .Select. You may want to see How to avoid using Select in Excel VBA
Find Last Row

score 1 · Answer 2 · answered Jan 21 '20 at 15:19

I noticed that answer did not provide how to copy other formats, or clear range in case old data is already there. So I'm adding my take of your question. Activate at end of code will open sheet "Historical Data" for you.

Option Explicit
Sub Macro1()
Dim ShD As Worksheet
Dim ShHD As Worksheet
Dim xIndex As Long
Dim LastRow As Long
Dim i As Long
    Set ShD = ThisWorkbook.Sheets("Data")
    Set ShHD = ThisWorkbook.Sheets("HD")
    'Change column width
    For i = 1 To 8
        ShHD.Columns(i).ColumnWidth = ShD.Columns(i).ColumnWidth
    Next
    xIndex = ShD.Range("A2").Column
    LastRow = CountRow(ShD.Name, xIndex)
    'Clears formats and values
    ShHD.Range("A2:H" & LastRow).Clear
    'Copying and pasting
    ShHD.Range("A2:H" & LastRow).Value = ShD.Range("A2:H" & LastRow).Value
    ShD.Range("A2:H" & LastRow).Copy
    ShHD.Range("A2:H" & LastRow).PasteSpecial xlFormats
    'Activate sheet
    ShHD.Activate
End Sub

'Function to count rows in column
Function CountRow(SheetName As String, Col As Long) As Long
    CountRow = ThisWorkbook.Sheets(SheetName).Cells(Rows.Count, Col).End(xlUp).Row
End Function

How to copy and paste all but row 1 from from a different work sheet

2 Answers2