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]

	B​	C​	D​	E​
1​	Zip​	​	Day​	Night​
2​	01001
3​	01002
4​	01003		Print	Print
5​	01004		Print
6​	01005		Print	Print
7​	01006		Print
8​	01007
9​	01008
10​	01009		Print
11​	01010			Print
12​	01011			Print
13​	01012		Print
14​	01013

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.