I am trying to copy a range from one workbook to a table in another workbook to capture production data. I am able to copy the correct range and open the workbook with the table successfully. However, when it tries to paste the information into the next available row in the table I get the error 13. I am rather new to vba and can't seem to find a solution, any help would be greatly appreciated.
Dim wbTime As Workbook
Set wbTime = ThisWorkbook
Dim wbData As Workbook
Dim N As Long
N = Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim UsdRws As Long
UsdRws = Cells.Find("*", After:=Range("A32"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
wbTime.ActiveSheet.Range("A6:O" & UsdRws).Copy
Set wbData = Workbooks.Open("S:\Lean Carrollton Initiative\Allen\Buffering Interrupters\1st Shift\B10\Data.xlsx")
wbData.ActiveSheet.Paste Destination:=Worksheets(Sheet1).Range(N & "A")
wbData.Close SaveChanges:=True
End Sub
Your range needs to be for correct syntax
Range("A" & N)
You could also use
Cells(N, "A")
And sheet1 should be "Sheet1" i.e. Worksheets("Sheet1"). Sheet1 by itself, unquoted, will be seen as a variable. Unless you have a string variable by that name? Use Option Explicit at the top of your module to check for variable declarations.
Additional comment from @MathieuGuidon:
I'll add that Sheet1 is the default code name of the "Sheet1" worksheet in a new workbook; it's a project-scoped identifier VBA creates for free, giving you a compile-time reference to that specific Worksheet object - thus, retrieving that object from the Worksheets collection is redundant altogether: Sheet1.Range(...) would be preferable -- and ideally, that sheet's (Name) property should be modified to a meaningful identifier, e.g. SummarySheet, which makes SummarySheet.Range(...) legal, without needing to declare any SummarySheet variable