CODEWITHSUNDEEP
vba
A.Oreo
A.Oreo

Reputation: 321

Keep the same variable in different Active Sheets

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

Upvotes: 0

Views: 65

Answers (2)

Kostas K.
Kostas K.

Reputation: 8518

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

Upvotes: 0

QHarr
QHarr

Reputation: 84465

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

Upvotes: 1

Related Questions