1

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 
2
  • 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. Commented Feb 21, 2018 at 12:34
  • 1
    Makes sense now, thank you, it worked! how can I mark the comment as answer? Commented Feb 21, 2018 at 12:41

1 Answer 1

0

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.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.