To paste the data on the last row of data inputed?

Question

I currently have Code which works fine and pastes the correct data when copying 1 sheet, however I now want it to pull data from 3 different sheets, pasting each sheet under the next one as one big data set. Below is the code I tried to use, however stops at .Range(LastRow)

Sub PipelineData()

Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

Set DestWbk = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
If Fname = "False" Then Exit Sub
Set SrcWbk = Workbooks.Open(Fname)

On Error Resume Next

Sheets("BID").ShowAllData
Sheets("DELIVERY").ShowAllData
Sheets("Complete or Cancelled").ShowAllData

On Error GoTo 0

SrcWbk.Sheets("BID").Range("A3:AP200").Copy DestWbk.Sheets("Pipeline").Range("A1")

SrcWbk.Sheets("DELIVERY").Range("A3:AP200").Copy DestWbk.Sheets("Pipeline").Range(LastRow)

SrcWbk.Sheets("Complete or Cancelled").Range("A3:AP200").Copy DestWbk.Sheets("Pipeline").Range(LastRow)

SrcWbk.Close False

End Sub

You need to actually [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Mar 17 '20 at 14:18
^ Twice because you will overwrite your data the the next time you use it. — Warcupine, Mar 17 '20 at 14:19
`DestWbk.Sheets("Pipeline").Range("A" & DestWbk.Sheets("Pipeline").Rows.Count).End(xlUp).Offset(1)` — Scott Craner, Mar 17 '20 at 14:22
How would I add this to the Code above to make it work? thanks for helping guys — Hsmith, Mar 17 '20 at 14:30
I added the above code to the two second lines of code which is pulling the 2nd and 3rd sheets data, but I get back Error Code 438 - Object doesn't support this property or method.....BTW using Excel 2010 — Hsmith, Mar 17 '20 at 14:34

Warcupine · Accepted Answer · 2020-03-17T15:05:28.650

Sub PipelineData()

Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

Set DestWbk = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
If Fname = "False" Then Exit Sub
Set SrcWbk = Workbooks.Open(Fname)

On Error Resume Next

Sheets("BID").ShowAllData
Sheets("DELIVERY").ShowAllData
Sheets("Complete or Cancelled").ShowAllData

On Error GoTo 0

SrcWbk.Sheets("BID").Range("A3:AP200").Copy DestWbk.Sheets("Pipeline").Range("A1")
dim lastrow as long
with DestWbk.Sheets("Pipeline")
    lastrow = .cells(.rows.count, 1).end(xlup).row 'Get last row
    SrcWbk.Sheets("DELIVERY").Range("A3:AP200").Copy .Range("A" & LastRow)
    lastrow = .cells(.rows.count, 1).end(xlup).row 'Get new last row
    SrcWbk.Sheets("Complete or Cancelled").Range("A3:AP200").Copy .Range("A" & LastRow)
end with
SrcWbk.Close False

End Sub

Hi Thanks for this, however I get back error '1004' Application defined Object defined? On 6th line from bottom — Hsmith, Mar 17 '20 at 14:59

To paste the data on the last row of data inputed?

1 Answers1