Focus of cell formula

[vince g]

Dear forum,

I am new to VBA. If anyone has a little time I would be very grateful for some enlightenment. I'm hoping a simple answer and some ignorance on my part.

In short, I've done coding in my past and solid with excel and have now just decided to learn some VBA and working on a specific pension management problem i've set myslef. I've broken a bigger problem down into chunks and generate a number of subs and functions in the last week post reading a book and made good headway and was felling good. I've now hit an issue that I just dont understand so thinking I need to go back to basics again re my baseline understanding.

In short...

I have 1 workbook. I have multiple sheets. I have written code and attached this to sheet 1. The sheet 1 VBA code looks to use data in sheet 2. I have made an activate sheet 2 statement and the use code which uses multiple cell(x,y) references in the coding. The issue is the cell statements are using sheet 1 content and not sheet 2 as I intended. I had assumed that if I activated sheet 2 then any cell references would be to sheet 2 but it seems they continue to referenec the sheet where the vba code is attached ie sheet 1. Is this a basic error or am I missing something. I have fixed via the application of Activesheet. in front of all cell formula but I just dont understand at present what I am missing here. ANy constructive inputs would be much appreciated.

 

[Fluff]

If the code is in a regular module any unqualified ranges will look at the active sheet, however if the code is in a sheet module, the unqualified ranges will look at that sheet & not the active sheet.

 

[rehanemis]

Hi,
If you want to make reference to other sheet/s then set that sheet with object variable as:

Dim ws as worksheet
dim ws1,ws2,ws3 as worksheet
set ws=sheets("Sheet1")
set ws1=sheets("Sheet2")
Set ws2=sheets("sheet3")
'now you can easily reference to any sheet like below
ws1.cells(1,1).value=45
'the above line of code only add 45 to first row and first column cell so you don't need to activate the sheet as it is not good programming practice.

Hope it helps.

 

[vince g]

Many thanks. I understand now and code working fine. I had VB under a sheet and not as a module.

 

[rehanemis]

Great!

 

[Fluff]

You're welcome & thanks for the feedback.