Changing Active sheet to a different sheet

[karunakaranindia]

Hi All,
I would like to know how to make the vba code to change the current active worksheet to a different worksheet.
For example, I have three sheets.
1. Summary
2. Product1
3. Product2

I am going to write my vba code on Summary worksheet. But I want the code to work on worksheet "Product1". Just wanted to avoid prefixing ActiveSheet everytime. I have a sequence of operations that needs to be done on the "product1" worksheet and return back to doing further tasks in "Summary" worksheet.

Sincerely,
KK

[johnjohns]

I didnot get you fully. But you can select different w.sheets thru macro

  ActiveWorkbook.Sheets("Summary").Select
  MsgBox ActiveSheet.Name
  ActiveWorkbook.Sheets("Product1").Select
  MsgBox ActiveSheet.Name

rgds

johnjohns

[karunakaranindia]

@johnjohns Hi,Thanks for the reply. I will explain it little bit in detail. Please look at the following code:

Sheets("Product1").Activate
ActiveCell.Offset(2, 0).Select
ActiveCell.Value = ActiveSheet.Name

The code is written on the Summary worksheet. I am activating the product1 worksheet. But for the next line, it is coming back to the "Summary" worksheet. I dont want the control to transfer back to the "Summary" worksheet and want it to stay in "Product" worksheet itself for carrying out further operations.

KK.

[mikerickson]

There is almost no need to change the active sheet in VB routines.

If you put the code in a normal module (Insert Module from the VBEditor, not a Class Module) you can call it from any sheet. There is no need to Activate a different sheet.

Your snippet could be written (Altering the range depending on where you want it to go.)

Sheets("Product1").Range("A1").Offset(0,2).Value = "Product1"