Forum Discussion
Issue with sheet in vba
Hope everyone doing good amid of COVID19.....
My query:
One workbook with two sheets and I wrote a macro program in module one and module two.
When I was trying to run macro for second sheet it's not running and even though it run also it's producing only one row output.
My macro is like this :
Method:1
Dim Mysheet As WorkSheet
Set Mysheet = ThisWorkbook.worksheets(sheet1)
Method:2
Worksheets(sheet1)
Method:3
ThisWorkbook.worksheets(sheet1)
Am I referencing this worksheet is correct or any other way is left ??
But one thing is like when I put my mouse pointer on sheet 1 it's running my code without any issues and producing results in all expected rows in particular column.
Is there any other way I can reference the worksheet to make this action perfect.
I have task to do by macro in both the worksheet and facing same issue in both the worksheet without mouse pointing to that sheet it runs on default mouse pointing sheet and running macro on the mouse pointer sheet itself...
Suggest me some solution please
5 Replies
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.
Subodh_Tiwari_sktneer Thanks for clarifying the concept.
This is my sample code here.
Dim my_sheet2 As WorkSheet
Dim Lastrange As Long
Set my_sheet2 = ThisWokbook.Worksheets("Sheet1")
Lastrange = my_sheet2.Cells(Rows.Count, "F").End(xlUp).Row
my_sheet2.Range("E2").Formula = "=A:A & B:B"
my_sheet2.Range("E2").Copy Range("E3:E" & Lastrange) -> Copy instruction
my_sheet2.Range("E1").Value = "Final"
It's running fine but it's implementing formula in E2 column alone and it's not copying the formula for rest of the rows in E column.
Suppose my F column has 100 rows the new E column also need to be 100 rows with that formula implemented
The issue maybe the last row you are trying to find in column F. Maybe column F doesn't have any data in it?
Also, this can be written in the following way...
Dim my_sheet2 As Worksheet Dim LastRow As Long Set my_sheet2 = ThisWorkbook.Worksheets("Sheet1") 'This line will find the last row with data in column F 'Make sure that column F is populated with data so that LastRow is evaluated correctly LastRow = my_sheet2.Cells(Rows.Count, "F").End(xlUp).Row my_sheet2.Range("E1").Value = "Final" my_sheet2.Range("E2:E" & LastRow).Formula = "=A2&B2"
