How can I use a variable to refer to multiple worksheets?

[Guest]

I need to refer to the same cells in multiple worksheets. How can I do this,
i.e. WorksheetX, where X is used in a loop increasing until all the sheets
are processed?

 

[NickHK]

You have the Worksheets collection
e.g Worksheets(2)

NickHK

 

[Guest]

If you really mean you have worksheets names
worksheet1
worksheet2
worksheet3
etc, then

Dim bcontinue as boolean, x as Long
Dim sh as Worksheet, rng as Range
bContinue = True
x = 1
do while true
On Error Resume Next
set sh = nothing
set sh = worksheets("Worksheet" & x)
if not sh is nothing then
set rng = sh.Range("A1:B9")
msgbox rng.Address(0,0,xlA1,True)
Else
bcontinue = False
end if
Loop

 

[Guest]

Tom and Nick,

First of all thanks for your posts trying to help me. I'm new to this
and still a bit confused; perhaps I didn't explain the situation correctly.
I have a workbook that imports a number of text files from a website, each
file onto a different worksheet, starting at worksheet 4. Then I need it to
do a "text to columns" on cells A9-A11. Then I need the results of B9-11
stored in Cx-Ex, where x is the worksheet number. It should look *SOMETHING*
like this (please help me with the commands and my errors, my comments using
the ' (single quote, I believe that is the "ignore" sign in VB, right?):





x=4
Do until x=41 '(if the last worksheet is 40, correct?)

Sub Breakup() '(this is the code to do it on the current worksheet, again
I need to do this on multiple worksheets)

Range("A8").Select
Selection.TextToColumns Destination:=Range("A8"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A9").Select
Selection.TextToColumns Destination:=Range("A9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A10").Select
Selection.TextToColumns Destination:=Range("A10"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
Range("A11").Select
Selection.TextToColumns Destination:=Range("A11"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)),
TrailingMinusNumbers:=True
End Sub

If $B8 = 1 (if the value in B8 on that worksheet is 1)
Then '(this is I believe the code to get the values from Cells B9-11 on
worksheet4 and place them in cells C4-E4 on worksheet1)
Sheets("Sheet1").Select
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[5]C[-1]"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=Sheet4!R[6]C[-2]"
Range("D5").Select

Else

x=x+1
Loop




Again, thanks to all who can help me feel free to email me at
(e-mail address removed) if you think it would be easier to assist me with
this outside the forum (I may have more questions).

MIKE