Need to print different sheets conditionaly
 

Hi,

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

Need to print different sheets conditionaly
 

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 Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Georgee" wrote in message ...
Hi,
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

Need to print different sheets conditionaly
 

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