Copy/select specific row to last row

[Hansen]

Hello everyone,

I want select rows, starting from a specific row (row number 8) to the last row, using the .end property.

Currently I am using the following approach:

dailyrecapwb.Sheets(1).Rows("8:" & Rows.Count).Copy

dailyrecapwb is an already opened workbook.

So far this code is working as expected and copying all rows beginning from row number 8 to the very last row.
In my table I have data from column A to column W.
Under this table there are some foot notes which are written in the column F to column Q, which I do not want to copy, but are currently being copied. This is why I want to select the rows starting at row 8 using the .end(xldown) property, as it should only copy the rows until the footnotes.

I tried using the following code:

dailyrecapwb.Sheets(1).Rows("8:" & Rows("8").End(xlDown)).Copy

Unfortunately this does not work, and returns the runtime error '13' "Type mismatch".

I know this is probably a stupid question, but since I am very new to VBA and tried looking for an answer for some time now, I figured I might just ask. Any help will be greatly appreciated!

[mancubus]

you dont need to copy/paste to transfer data from one workbook to another.

try something like below

Sub test()

    Dim LastRow As Long
    Dim dailyrecapwb As Workbook
    Dim destSheet As Worksheet
    
    Set dailyrecapwb = Workbooks("WB_Name_Here")
    Set destSheet = ThisWorkbook.Sheets("Sheet1") 'change workbook and sheet name to suit
    
    With dailyrecapwb.Sheets(1)
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(8, .Columns.Count).End(xlToLeft).Column
        destSheet.Cells(1).Resize(LastRow - 8 + 1, LastCol).Value = .Range(.Cells(8, 1), .Cells(LastRow, LastCol)).Value
    End With

End Sub

[Hansen]

Wow thanks for the fast reply!!

The code works perfectly.

My final code looks like this now:

Private Sub CommandButton2_Click()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim LastRow As Long
    Dim LastCol As Long
    Dim dailyrecap As String
    Dim dailyrecapwb As Workbook
    Dim destSheet As Worksheet
    
    dailyrecap = Application.GetOpenFilename("CSV Files (.csv), *.csv", , "Please select daily recap")
    Workbooks.Open (dailyrecap)
    Set dailyrecapwb = Workbooks.Open(dailyrecap, Local:="true")
    Set destSheet = ThisWorkbook.Sheets("Data")
     
    With dailyrecapwb.Sheets(1)
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(7, .Columns.Count).End(xlToLeft).Column
        destSheet.Cells(1).Resize(LastRow - 7 + 1, LastCol).Value = .Range(.Cells(7, 1), .Cells(LastRow, LastCol)).Value
    End With
    
    dailyrecapwb.Close
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub

I wanted to use copy and paste, since I have some Pivot tables based on this data, which I want to update automatically. I just assumed that this might cause problems, but tried it out now and it works perfectly.

THanks again!

[mancubus]

thanks for the feedback and marking the thread as solved.