VBA: Run time error 1004 when called from different worksheet?

0
votes

If this sub is called via the worksheet "sheetCTF" then it works. If it is called via the worksheet "sheetEXTRACTED" then I get

"Run time error 1004 - Method 'Range' of object '_Worksheet' failed"

Why?

Public Const ctfHeadingRow As Long = 1
Public Const ctfLastRow As Long = 200

Sub SUB_copyCtfColsToExtracted(sourceCol As Long, destCol As Long)

sheetCTF.Range(Cells(ctfHeadingRow, sourceCol), Cells(ctfLastRow,sourceCol)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=sheetEXTRACTED.Range(Cells(extractedFirstRow, destCol).Address)

End Sub

(Sheet are set in different module).

1
vbaexcel
Where do you set extractedFirstRow? Is that a public / global variable which is available to the routine? Are the sheetEXTRACTED and sheetCTF the actual sheetnames in VBE or are they the tab names? In that case you'd need Worksheets("sheetEXTRACTED") – Rik Sportel
Sorry, yes extractedFirstRow is a global variable in a different module. And yes they are both the actual sheet names set in a different module. – Slab

1 Answers

1
votes

I suggest:

Sub SUB_copyCtfColsToExtracted(sourceCol As Long, destCol As Long)

sheetCTF.Range(sheetCTF.Cells(ctfHeadingRow, sourceCol), sheetCTF.Cells(ctfLastRow,sourceCol)).SpecialCells(xlCellTypeVisible).Copy _
Destination:=sheetEXTRACTED.Range(sheetEXTRACTED.Cells(extractedFirstRow, destCol).Address)

End Sub

This should resolve the problem. The error apears if the reference to a object like "range()" or "Cells" is not clearly defined.