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 definedws
as a sheet inwb
. – SJR