Keep the same variable in different Active Sheets

Question

Suppose

Sheets("Sheet1").Activate
Dim Begin as Range
Set Begin = Range("A10")

When I use

Sheets("Sheet2").Activate

Begin seems not the Range("A10") in Sheet2, then how to make the Begin become the Range("A10") in Sheet2?

I show my full code following, you can see that for each loop, I want deal with each sheet for same operation. But my code seems very tedious, and if I want to change Range("A10") to Range("A9"), then I should change each one manually.

Module1.Re() will also use Range("A10"), actually in each loop, I want to pass the variable Range("A10").Offset(i,0) into each sheet, but it is the cell in the corresponding sheet.

That's why I want to replace Range("A10") by a common variable Begin. Could you help me to simplify my code, actually I am totally new in VBA.

In Modules2

Sub Main()
Sheets("Sheet1").Activate

Z = 5
Y = 10

Dim Column As Integer
Dim Row As Integer

Set Begin = Range("A10")


Row = Begin.End(xlDown).Row - Begin.Row

For i = 1 To Row
    Sheets("1 HK").Activate
    Module1.Re (i)
    Sheets("5 HK").Activate
    Module1.Re (i)

    Sheets("1 HK").Activate
    Set Begin1 = Range("A10").Offset(i + 1, 4)
    Set Begin2 = Range("A10").Offset(i + Z, 4 + Y)
    Range(Begin1, Begin2).Clear

    Sheets("5 HK").Activate
    Set Begin1 = Range("A10").Offset(i + 1, 4)
    Set Begin2 = Range("A10").Offset(i + Z, 4 + Y)
    Range(Begin1, Begin2).Clear                
Next i   
End Sub

Relevant: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Tim Williams, Oct 12 '17 at 06:35

QHarr · Accepted Answer · 2017-10-12T05:54:25.183

1

You have selected the activesheet to be sheet1 with

Sheets("Sheet1").Activate

Either change this to

Sheets("Sheet2").Activate

If you want that to be the active sheet then set the range OR

Better still is to fully qualify your variables and ensure your declarations are at the start so you don't get errors

Option Explicit 'Putting this means variables must be declared and spelling is checked for you

Dim wb as Workbook
Dim ws as Worksheet
Dim ws1 as Worksheet
Dim Begin as Range

Set wb = ThisWorkbook  'ensure you end up working with the right workbook
Set ws = wb.Sheets("Sheet2") 'ensure you end up working with the right sheet    
Set ws1 =  wb.Sheets("Sheet1")
Set Begin = ws.Range("A10")

Then later on you can use a With statement for more efficient code.

With ws1 'using Sheet1

  'code for working with Sheet1
End With

Note: Setting Option Explicit

edited Oct 12 '17 at 05:54

answered Oct 12 '17 at 05:48

QHarr

83,427
12
54
101

What is Module1.Re ? Don't use Row/Column as a variable row use something like totalRows/totalColumns. It is more descriptive and not confused with the objects Row/Column. You are creating new undeclared variables within a loop. Don't. Please use Option Explicit and Declare variables either at top or close to where used (but not after where attempting to assign). Have your outermost loops be your Sheets (e.g. 1 to Sheets.count) – QHarr Oct 12 '17 at 06:42
you can regard `Module1.Re()` as a function I defined in other modules. You mean it's better to use `Option Explicit` and declare each variable by `Dim as ` – A.Oreo Oct 12 '17 at 06:46
Yes. It will mean your variables are checked for type and spelling. Will make your life much easier in the long run. Allows for early binding so faster code running. Where you place the declarations is more a matter of opinion, some say all at top, others say close to where used (always before attempting to assign a value of course). Personally, i do a mixture. I try to keep the number at the top not too numerous and as code gets longer i like to keep the declarations close to their use, especially with Loops and the like. – QHarr Oct 12 '17 at 06:50
thanks, I happen to a new problem, when I change in the `Model1` `Sub Re(Begin as range)...End Sub` and `Set ws1 = Worksheets("1 HK"), Set Begin1 = ws1.Range("A10"),` but there is a error `Object required` on `Module1.Re(Begin1)` – A.Oreo Oct 12 '17 at 07:09
It seems `Range` can not be parameter in a `Sub`. – A.Oreo Oct 12 '17 at 07:15
Range is an object so you can declare a variable to be a Range object. This can be passed as a parameter to a Sub or a Function not as a name for the variable but as the type to be expected i.e. a Range object. I don't know what is in Module1.Re but does it expect a Range object? In the function definition you would need to state it should expect a range object. This is the kind of thing Option Explicit is good for spotting. – QHarr Oct 12 '17 at 07:31
Here I use the `.Offset()` to select the cells in the loop whole code, is it the standard way? – A.Oreo Oct 13 '17 at 01:55

score 0 · Answer 2 · answered Oct 12 '17 at 06:32

How about a public Property in a standard module?

Public Property Get BeginRange() As Range
    Set BeginRange = Application.ActiveSheet.Range("A10")
End Property

Dim r As Range
Set r = ModuleName.BeginRange  'ModuleName is not required, only added for clarity

Keep the same variable in different Active Sheets

2 Answers2