PDA

View Full Version : [SOLVED] Copy/select specific row to last row



Hansen
10-24-2016, 02:31 AM
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
10-24-2016, 02:51 AM
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
10-24-2016, 03:26 AM
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
10-24-2016, 03:43 AM
thanks for the feedback and marking the thread as solved.