Issue with sheet in vba

Nandhu19940 

It is hard to suggest why your code doesn't work as desired without looking at the code. It would be better if you post the whole code or rather upload the macro file to tweak the existing code and test it to make sure it does what it is supposed to do.

As far as referencing a worksheet is concerned, here are some ways to do that...

Declaration:

Dim Mysheet As WorkSheet

Method1:

Set Mysheet = ThisWorkbook.worksheets("Sheet1")

In above method you need to pass either the worksheet name enclosed with double quotes.

Sheet1 is the worksheet's name and it is passed as "Sheet1"

Or you may pass the worksheet index without quotes like below

Set Mysheet = ThisWorkbook.worksheets(1)

So remember to enclose the worksheet's name with double quotes in the above method.

Also since you are qualifying the worksheet reference with ThisWorkbook workbook reference that means the code will look for the Sheet1 in the workbook in which this macro is placed irrespective of which workbook is currently active in case more than one workbooks are opened at the same time.

Method2:

Set Mysheet = Worksheets("Sheet1")

OR

Set Mysheet = Worksheets(1)

In the above method, the code will look for Sheet1 in the workbook which is currently active.

So if your macro is written in WorkbookA and currently WorkbookB is also opened and active, the code will try to find the Sheet1 in WorkbookB and if doesn't find any sheet called Sheet1 in WorkbookB, it will raise the run-time error 9 'Subscript out of range" which means the object you are referring to isn't available.

Method3

Refer to the worksheet using it's code name.

Each worksheet in the workbook has it's own unique code name e.g. when you open a blank workbook, it opens with a default worksheet called "Sheet1" with a code name Sheet1 as well.

Even if you rename your tab from "Sheet1" to something else say "MySheet1", the code name remains unchanged and it's still Sheet1.

It is useful in case when you want to refer to a specific worksheet in the workbook irrespective of it's visible tab name so if use changes the tab/worksheet name to something else, the code will search for the referred Sheet by looking at it's code name not by looking it's tab name.

You may set the worksheet using the code name like below...

Set Mysheet = Sheet1

Where Sheet1 is the code name of the worksheet.

Once you set the worksheet variable using any of the above methods and if you refer to any cell or range on the worksheet, you need to qualify the cell or range variable with the worksheet reference also to make sure the action you want to perform with the cell/range is actually performed on the cell/range of the referred worksheet irrespective of which worksheet is currently active in the workbook.

e.g.

Dim MySheet As Worksheet

Dim Rng As Range

Set MySheet = ThisWorkbook.Worksheets("Sheet1")

Set Rng = MySheet.Range("A2:A10")

Rng.Font.Size = 14

The above code will always change the font size of the range A2:A10 to 14 on Sheet1 even if Sheet1 is not the active sheet.

But if you set your range like this...

Set Rng = Range("A2:A10")

Rng.Font.Size = 14

The above code will set the font size of the range A2:A10 of the sheet which is currently active while you are expecting it to set the font size of the range on Sheet1 but since you didn't qualify the Range variable with the worksheet reference, the Rng variable will always refer to the range A2:A10 on the active sheet.

Hope this will help you to resolve the issue you are having.