How to switch worksheets with vba ?

  • Thread starter Thread starter tmb
  • Start date Start date
T

tmb

I have a vba macro with a lot of references like...

Range("A" & 1).Select
strData = ActiveCell.Value

This has defaulted to WorkSheet 1

Now I want to put every thing on WorkSheetX down in the Workbook

Isn't there a vba statment that says... in effect... any references from
here on till I say stop are to WorkSheetX. Like...

References are now to WorksheetX
code
code
code
References are now to WorksheetABC
code
code
etc
thanks for any help.

tmb
 
If you drop the .select and .activates, you can usually work on the range
directly:

with worksheets("sheet1")
strData = .range("a1").value
end with

with worksheets("sheet2")
.range("a2:b3").clearcontents
end with
....

If you're looping through the sheets and doing the same thing each to each
sheet, you can do something like:

dim wks as worksheet
for each wks in activeworbook.worksheets
with wks
.range("a2").value = 99
end with
next wks
 
Perhaps take a look at 'With / End With' eg

Set Sht1 = Worksheets("Sheet1")
Set Sht2 = Worksheets("Sheet2")

With Sht1
.xxx
.xyz
.yyy
.do more stuff
End With

With Sht2
.xxx
.xyz
.yyy
.do more stuff
End With


Type 'With statement' into VBA help and this is what you will hopefully
get:-

Using With Statements
===============
The With statement lets you specify an object or user-defined type once for
an entire series of statements. With statements make your procedures run
faster and help you avoid repetitive typing.

The following example fills a range of cells with the number 30, applies
bold formatting, and sets the interior color of the cells to yellow.

Sub FormatRange()
With Worksheets("Sheet1").Range("A1:C10")
.Value = 30
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0)
End With
End Sub

You can nest With statements for greater efficiency. The following example
inserts a formula into cell A1, and then formats the font.
Sub MyInput()
With Workbooks("Book1").Worksheets("Sheet1").Cells(1, 1)
.Formula = "=SQRT(50)"
With .Font
.Name = "Arial"
.Bold = True
.Size = 8
End With
End With
End Sub
 
There are several ways of doing what you want. One is to have your code
select the sheet and have the code act on the active sheet. Then the code
can select another sheet and again, the code would act on the active sheet.
Another way is to use a With/End With construct. It works like this:
With Sheets("FirstShtName")
strData = .Range("A" & 1).Value
End With

With Sheets("SecondShtName")
strVar = .Range("F4").Value
End With

The With/End With construct eliminates selection of sheets. The active
sheet stays the active sheet throughout. Note the period before the word
"Range". That means that the range is in the sheet of the With statement.
You can put code within the With/End With construct that doesn't have the
period and that reference would be to the active sheet. HTH Otto
 
Back
Top