VBA printing idea...Is this possible?

[amxtomzo]

hello

I am trying to come up with a way to print a selected group of worksheets through a command button

the selected group of worksheets will change weekly
The worksheets that will print will be named as zip codes IE: 01001, 01010, 01030...ect

I thought maybe...on a worksheet...

in column B, all zip codes we use would be listed,
in column D, would be day shift, and I could put the text PRINT in the same row as a worksheet I wanted to print
in column E, would be night shift, and I could put the text PRINT in the same row as the worksheet I wanted to print

So if I had a code the would look for PRINT in in the rows of column D then
print the same worksheets that are listed in the same rows of column B

I cant seem to put anything together to make this work

thanks for your help

Thomas

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Zip​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Day​

[/td][td="bgcolor:#F3F3F3"]

Night​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]01001[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]01002[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]01003[/td][td][/td][td]Print[/td][td]Print[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]01004[/td][td][/td][td]Print[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]01005[/td][td][/td][td]Print[/td][td]Print[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]01006[/td][td][/td][td]Print[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]01007[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]01008[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]01009[/td][td][/td][td]Print[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]01010[/td][td][/td][td][/td][td]Print[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]01011[/td][td][/td][td][/td][td]Print[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]01012[/td][td][/td][td]Print[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]01013[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Sub amx()
  Dim iRow          As Long

  For iRow = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If LCase(Cells(iRow, "D").Value2) = "print" Then
      Worksheets(Cells(iRow, "B").Value2).PrintOut
    End If
  Next iRow
End Sub

Change "D" to "E" for night shift.

 

[amxtomzo]

shg

Thank you for helping out

The is a runtime Error, says "Subscript out of range"
and highlights this row

Worksheets(Cells(iRow, "B").Value2).PrintOut

any ideas?

Thomas

 

[shg]

That means there is no worksheet having the name in col B.

Col B MUST be formatted as Text, not a number formatted as 00000. The code could be changed to accommodate, but the same problem will likely bite you elsewhere:

Worksheets(Cells(iRow, "B").Text).PrintOut

 

[amxtomzo]

shg

We are getting closer
I formatted column B as text
I renamed 3 worksheets , for testing, as 1001, 1027, 1030

Debug "Script out of range" still came up
However, 1 sheet printed, the 1st one 01001, (renamed 1001)

any thoughts

 

[shg]

Formatting numeric cells as text doesn't make them Text, though it appears to.

You can reenter them manually. Or you can do data > text to column, next, next, and select text, then edit to add the leading zeros. Or you can use the revised code.

 

[amxtomzo]

shg

we got something now

changed column B back to Zip Code format
changed columns D:E to Text format
renamed test worksheets back to Zip Codes , 01001, 01027, 01030

Sub PrintDayShift()

  Dim iRow As Long

  For iRow = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    If LCase(Cells(iRow, "D").Text) = "print" Then
      Worksheets(Cells(iRow, "B").Text).PrintOut
    End If
  Next iRow
End Sub

seems to be Rocking and Rolling in Overdrive "George Thorogood"

Thank you very much, you have been a big help

Thomas

 

[shg]

You're welcome.