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

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
if you think it would be easier to assist me with
this outside the forum (I may have more questions).

MIKE

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"dc_area_mcse" wrote:

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?