Print Array in Cell

I

ianripping

I have a list of sheets in cell A1:-

"Index", "Area 1", "Area 2"

I want to create a macro that will print out these sheets, some thin
like

sheetrange = range("a1")
sheets(array(sheetrange)).select
selectedsheets.printpreview

can someone help me out on this one
 
T

Tom Ogilvy

With worksheets("Sheet1")
varr = Split(.Range("A1"))
End With
sheets(varr).Select
ActiveWindows.SelectedSheets.PrintPreview

Works in xl2000 or later.
 
S

sp00nix

I havent tested this, but maybe something like:

Sub testSub()
mySheets = Split(Sheets("Sheet1").Range("A1").Value, ",")
For i = 0 To UBound(mySheets)
mySheets(i) = Replace(mySheets(i), """", "")
'MsgBox mySheets(i)
Sheets(mySheets(i)).PrintOut
Next i
End Sub

Change "Sheet1" to whatever sheet you keep your array in - try it ou
 
T

Tom Ogilvy

I left off the second argument for split
With worksheets("Sheet1")
varr = Split(.Range("A1"),",")
End With
sheets(varr).Select
ActiveWindows.SelectedSheets.PrintPreview


it would be easier to adjust the values in A1 to be

Index,Area 1,Area 2

rather than to write code to eliminate the doublequotes. Particularly since
you will probably be changing this list frequently.
 
I

ianripping

I have tried both of these methods, but the problem is i get acompli
error "Sub or function not defined" for Split.

I am running excel 97. Could this be the problem that the Spli
function isnt supported?

If so, what can I do
 
T

Tom Ogilvy

Works in xl2000 or later.

was in my original post. Split wasn't introduce until Excel 2000.

You could do this

Sub tester3()
With Worksheets("Sheet1")
sStr = .Range("A1")
End With
varr = Evaluate("{" & sStr & "}")
Sheets(varr).Select
ActiveWindow.SelectedSheets.PrintPreview
End Sub

This assume you use your original format in the cell with the double quotes:
"Index", "Area 1", "Area 2"
 
Top