VBA Excel error when code is moved from worksheet to module

Question

I have a Excel file with multiple tabs. I have a worksheet with some code which is working fine. This code also refers to data on some "master" tabs. I need to duplicate this sheet so I moved the common functions from there to a module. Now I get a VBA 1004 error when trying to access a range on the same worksheet.

 Dim selectedRange As Range
 Set selectedRange = Worksheets(name).Range("A1", _
 Range("A" & Rows.count).End(xlUp)) 'Error Line

This code worked fine till I moved it to a module. It works if I put a

Worksheets(name).Select 

before it, but I will have to do it too many times. Based on this query: VBA error 1004 - select method of range class failed the code should work fine without a .Select. And it does as long as the code is within the worksheet. Why does moving code to a module create a problem?

Answer 1

U use Range and Rows properties without an object qualifier. When used without an object qualifier, this properties are a shortcut for ActiveSheet.Range / ActiveSheet.Rows.

So the code does this:

Worksheets(Name).Range("A1", ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp))

But Worksheets(name) could be different from active sheet so better is:

Worksheets(Name).Range("A1", Worksheets(Name).Range("A" & Worksheets(Name).Rows.Count).End(xlUp))

In With-End With block:

With Worksheets(name)
      Set selectedRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With

So it is ensured that the Range/Rows properties are applied on Worksheets(name) worksheet object.

Answer 2

When you do things on a sheet, you do not really need explicit declarations to that sheet.

However when working on a module and interacting with other sheets, you need to specify which Sheet you want to work with. So select the sheet before you can select the range. To say, SELECT PARENT BEFORE YOU SELECT THE CHILDREN :) Please note, following is just the logic explanantion. Not the exact code syntax.

So I suggest you create the following worksheet variable and set your worksheet object that you need into that.

e.g.

Dim WS as Worksheet
Dim selectedRange As Range

Set WS = Sheets("Shee1")
Set selectedRange = WS.Range("A1", _
Range("A" & Rows.count).End(xlUp)) 'Error Line

Or else if you want to refer to all sheets, you may use each sheet's index

E.g. ThisWorkBook.Sheets(i) 'i is an integer Then loop or whatever as it deems to your programme structure.

Further you do not have to use Select on the worksheet to point to a range in that worksheet. As per above code you could set the worksheet and set the range you need to process. When optimizing VBA execution, select is usually a taboo. Also Excel 2007 does not retain the active sheet the way older versions used to.