Thank you for the help peeps, I've found this bit code that does almost exactly what I want. The only slight problem is that it doesn't let me paste a specific range, it just copies the entire row. However I overcame this by just saving the souce data in the same columns.
Code:
Private Sub LastRowToExport()
Dim lastS1Row As Long 'Last Source Row
Dim nextS2Row As Long 'Next Target Row
Dim lastCol As Long 'Last Column on Source Sheet
Dim s1Sheet As Worksheet, s2Sheet As Worksheet
Dim source As String 'Source worksheet name
Dim target As String 'Target worksheet name
Dim path As String
'---SET SHEET NAMES HERE---
source = "Product" 'Source Worksheet Name
path = "C:\data.xlsx" 'Target File Path including file name and extension
target = "exportsheet" 'Target Worksheet Name
'WARNING - THIS LINE WILL DISABLE NORMAL BREAKING - IN CASE OF LOOP
Application.EnableCancelKey = xlDisabled 'Disables breaking when opening new book
'Define worksheets
Set s1Sheet = ThisWorkbook.Sheets(source) 'Source Sheet
Set s2Sheet = Workbooks.Open(path).Sheets(target) 'Target Sheet
'Get the last row on each sheet and set the NEXT Row on the target. Also total columns.
lastS1Row = s1Sheet.Range("A" & Rows.count).End(xlUp).row
nextS2Row = s2Sheet.Range("A" & Rows.count).End(xlUp).row + 1
lastCol = s1Sheet.Cells(1, Columns.count).End(xlToLeft).column 'Headers in Row 1
'---COPY ENTIRE ROW--- Loop through Cells by column
For lCol = 1 To lastCol
s2Sheet.Cells(nextS2Row, lCol) = s1Sheet.Cells(lastS1Row, lCol)
Next lCol
'WRAP UP, SAVE EXPORTED SHEET, REACTIVATE SOURCE SHEET
s2Sheet.Activate
ActiveWorkbook.Close SaveChanges:=True
s1Sheet.Activate
End Sub
Social Networking Bookmarks