I am copying data from one workbook to another, both declared as variables, but my Copy Destination method throws us either Runtime Error 13 'Type mismatch' or Runtime Error 438 'Object doesn't support this property or method'.
Could you please help me with the Syntax? Is it possible to have something like
Range("").Copy Destination:=wb1.ws.Range("a" & Rows.Count).End(xlUp).Offset(1)
or
Range("").Copy Destination:=wb1.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
My code
Sub Sundry_AllFiles(wb1, wbsource, rl, wsctrl, Mths6, Mths12) (==Declared elsewhere - Dim wb1,wbsource as Workbook Set wb1 = ThisWorkbook Set wbsource = Workbooks.Open(FPath & Finame, ReadOnly:=True, Local:=True) ===) Dim ws As Worksheet Dim r, rw As Long Dim fnd As String Dim fnm As String fnd = "TOTAL" fnm = wbsource.Name Set ws = wb1.Worksheets("Sundry") '=====Macro runs from wb1 and analyses data in the wbsource If IsEmpty(Range("A1")) = True Then wbsource.Close SaveChanges:=False Exit Sub Else Application.CutCopyMode = False 'POSSIBLE SOLUTION DELETE IF DIDNT WORK Columns("A:A").Select Selection.Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove With wbsource.Worksheets(1) For rw = 2 To .Cells(Rows.Count, 6).End(xlUp).Row .Cells(rw, 1) = Application.VLookup(fnd, wbsource.Worksheets(1). _ Range("B:E"),4, False) Next rw For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row .Cells(rw, 15) = "=IF(RC[-13]<=TODAY()-90,""Yes"",""No"")" Next rw .AutoFilterMode = False End With 'Autofilter by tranDate>90 days, copy and append to the ws sheet With Range("N" & Rows.Count).End(xlUp) .AutoFilter 15, "Yes" With wbsource.Worksheets(1) Dim LR As Long On Error Resume Next LR = Range("N" & Rows.Count).End(xlUp).Row Range("A2:N" & LR).SpecialCells(xlCellTypeVisible).Copy _ Destination:=wb1.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) '#Alternative to Copy Destination that works 'wb1.Activate 'ws.Range("a" & Rows.Count).End(xlUp).Offset(1).PasteSpecial End With End With wbsource.Close SaveChanges:=False End If End Sub
Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)as you have already definedwsas a sheet inwb.