Well, I did
Dim parThreePrint parameter as variable string
and it is a dynamic string based on a content of my workbook sheets,
every time I run macro it may have randomly different elements without
repetitions from the twelve possible :
"ab", "ac", "ad", "ae", "af", "ag", "ah", ....."am"
I am not sure how could I Dim it as a static array?
That array would need to have dynamic elements and their number would
dynamic too.
Regards
George
Thanks for a reply anyway, maybe I did not fully understend your
solution?
Bob Flanagan wrote:
What you need to do is something like this: dim parthreeprint()redim
parthreeprint(1 to 8) parthreeprint(1) = "ad"parthreeprint(2) =
"af" etc then Sheets(parThreePrint).Select 'notice removal of
array() Its one of those cases where the macro recorder is not too
helpful. Robert FlanaganMacro SystemsDelaware, U.S.
302-234-9857http://www.add-ins.comProductivity add-ins and
downloadable books on VB macros for Excel
"Georgee" wrote in message
,
I need to print sheets on a condition of their content
I created a variable:
Dim parThreePrint As Variant
that returns a string of needed sheet names in a format:
"ad", "af", "ah", "ak", "al", "am", "ae", "aj"
and I inserted it ino a following procedure ( Macro Recorder
have recorded it for me)
Sheets(Array(parThreePrint)).Select
Error: Subscript out of range, pops up
if I copy the string from the edit box to the script it
works perfect!!!
How to circumvent the problem???
P.S. I do not know how include " in a string so I have
inserted their names in quotation markson on every Sheet in
cells(2,1)
So on Sheets("ab")
Cells(2,1) = "ab" ETC
Below I am attaching whole sub for reference
Sub Test()
'
' Test Macro
' Macro recorded 8/15/2004 by GK
'
' Keyboard Shortcut: Ctrl+Shift+T
'
' Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
' Sheets("Sheet1").Activate
Dim arrTabs(1 To 12) As String
Dim parOnePage As String
Dim parTwoPage As String
Dim parThreePage As String
Dim parOnePrint As Variant
Dim parTwoPrint As Variant
Dim parThreePrint As Variant
Dim parTabStep As Byte
parOnePage = ""
parTwoPage = ""
parThreePage = ""
parOnePrint = ""
parTwoPrint = ""
parThreePrint = ""
parTabStep = 1
arrTabs(1) = "ab"
arrTabs(2) = "ac"
arrTabs(3) = "ad"
arrTabs(4) = "ae"
arrTabs(5) = "af"
arrTabs(6) = "ag"
arrTabs(7) = "ah"
arrTabs(8) = "aI"
arrTabs(9) = "aj"
arrTabs(10) = "ak"
arrTabs(11) = "al"
arrTabs(12) = "am"
For parTabStep = 1 To 12
Sheets(arrTabs(parTabStep)).Select
If Cells(1, 1) = 3 Then
parThreePage = parThreePage & ", " & Cells(2, 1)
ElseIf Cells(1, 1) = 2 Then
parTwoPage = parTwoPage & ", " & Cells(2, 1)
ElseIf Cells(1, 1) = 1 Then
parOnePage = parOnePage & ", " & Cells(2, 1)
End If
Next
parOnePrint = Right(parOnePage & parTwoPage & parThreePage,
Len(parOnePage & parTwoPage & parThreePage) - 2)
parTwoPrint = Right(parTwoPage & parThreePage,
Len(parTwoPage & parThreePage) - 2)
parThreePrint = Right(parThreePage, Len(parThreePage) - 2)
Sheets(arrTabs(1)).Select
Cells(3, 1) = parOnePrint
Cells(4, 1) = parTwoPrint
Cells(5, 1) = parThreePrint
Sheets(Array(parThreePrint)).Select
Sheets("ab").Activate
End Sub