Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use a variable to refer to multiple worksheets?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use a variable to refer to multiple worksheets?
You have the Worksheets collection
e.g Worksheets(2) NickHK "dc_area_mcse" ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I use a variable to refer to multiple worksheets?
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to refer variable while assigning string value? | Excel Programming | |||
variable to refer to "this worksheet"? | Excel Worksheet Functions | |||
Using a variable to refer a cell | Excel Programming | |||
How to refer a Variable across Workbooks ? | Excel Programming | |||
Refer to Combo-box using a part + variable | Excel Programming |