1
votes

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
1
The destination should be Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) as you have already defined ws as a sheet in wb.SJR
Makes sense now, thank you, it worked! how can I mark the comment as answer?user8449681

1 Answers

0
votes

Rather than

Destination:=wb.ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

the destination should be

Destination:=ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

as you have already defined ws as a sheet in wb.