Forum Discussion
Issue with sheet in vba
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"Thanks lot once again that solution works.
One more query from my side:
How to increase the performance of macro?
My macro deals with below data.
1) Copying of data from one workbook to another by file upload option.
2) Source excel will have minimum 1,20,000 rows & 30 columns and another source file will have minimum 50,000 rows & 28 columns.
3) Doing concatenation, vlooukp from another sheet data and then sting length reduce for particular column.
In this above case i did performance analysis which is taking almost for 34,000 rows it is taking 14 minutes of time to execute all the above steps.
My though here is like for 34,000 it is 14 min and when it comes to 1,20,000 rows almost an hour execution will be there.
Please suggest me some ideas to increase the performance.
