I'm stumped and can't find any past message that work.
The worksheets to be printed are different each time. The user prints to fax so the print must be one file.
Here's what I thought would work but I get subscript out of range on the third last line:
Sub Print()
Dim strArray As String
strArray = Chr(34) & "Sheet2" & Chr(34)
If Sheets("Sheet3").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet3" & Chr(34)
End If
If Sheets("Sheet4").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet4" & Chr(34)
End If
If Sheets("Sheet5").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet5" & Chr(34)
End If
If Sheets("Sheet6").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet6" & Chr(34)
End If
If Sheets("Sheet7").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet7" & Chr(34)
End If
Sheets(Array(strArray)).Select 'GET THE ERROR HERE
Sheets("Sheet2").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
I tried getting rid of the quotes around the end of the array and that doesn't work either.
Help!
The worksheets to be printed are different each time. The user prints to fax so the print must be one file.
Here's what I thought would work but I get subscript out of range on the third last line:
Sub Print()
Dim strArray As String
strArray = Chr(34) & "Sheet2" & Chr(34)
If Sheets("Sheet3").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet3" & Chr(34)
End If
If Sheets("Sheet4").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet4" & Chr(34)
End If
If Sheets("Sheet5").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet5" & Chr(34)
End If
If Sheets("Sheet6").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet6" & Chr(34)
End If
If Sheets("Sheet7").Cells(2, 1).Value = "YES" Then
strArray = strArray & ", " & Chr(34) & "Sheet7" & Chr(34)
End If
Sheets(Array(strArray)).Select 'GET THE ERROR HERE
Sheets("Sheet2").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
I tried getting rid of the quotes around the end of the array and that doesn't work either.
Help!