Tuesday, September 24, 2019

Excel VBA Copy Destination syntax with variables for Workbook/Worksheet



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


Answer



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.


No comments:

Post a Comment

hard drive - Leaving bad sectors in unformatted partition?

Laptop was acting really weird, and copy and seek times were really slow, so I decided to scan the hard drive surface. I have a couple hundr...