Define Columns to print VBA

[Folksteve]

Hi, I have the below Macro to route through my Spreadsheet and only print the sheets with data in a certain cell:

Sub CHECKSHEET()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible And Sh.Range("A101").Value <> "" Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub

I also have another macro for another spreadsheet which only prints the one sheet but defines which columns not to print:

Sub W_CHECKSHEET()

If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False

With ActiveSheet
.Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = True
.PrintOut
.Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = False
End With

Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

I have tried to add to the first Macro to define which columns not to print but am not sure how.

Thanks

 

[JLGWhiz]

Try this

Sub CHECKSHEET()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = xlSheetVisible And Sh.Range("A101").Value <> "" Then
             N = N + 1
             ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
        End If
        With Worksheets(Arr)
            .Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = True
            .PrintOut
            .Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = False
        End With
    Next
End Sub

 

[Folksteve]

Just tried your code. It says 'Run-time error 13. Type mismatch'

 

[JLGWhiz]

Just tried your code. It says 'Run-time error 13. Type mismatch'

You did not need the array, unless you planned to use it elsewhere in the code. But for printing the sheets with columns hidden, this is all you need.

Sub CHECKSHEET()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = xlSheetVisible And Sh.Range("A101").Value <> "" Then
            With Sh
                .Range("B1, H1, K1:N1, P1").EntireColumn.Hidden = True
                .PrintOut
                .Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = False
            End With
        End If
    Next
End Sub

 

[Folksteve]

Thanks! That works fine, but I still need the array for if I need to print to PDF it prints as a single file rather than separate files per page.

Have tried adding it back in but cant seem to get around the 'Block if' error

Sorry to be a pain

 

[JLGWhiz]

I don't understand what the array has to do with PDF but here it is.

Sub CHECKSHEET()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
    For Each Sh In ActiveWorkbook.Worksheets
        If Sh.Visible = xlSheetVisible And Sh.Range("A101").Value <> "" Then
            N = N + 1
            ReDim Preserve Arr(1 To N)
            Arr(N) = Sh.Name
            With Sh
                .Range("B1, H1, K1:N1, P1").EntireColumn.Hidden = True
                  .PrintOut
                .Range("B1,H1,K1:N1,P1").EntireColumn.Hidden = False
            End With
        End If
    Next
End Sub

 

[Folksteve]

Thanks for your help. I'm probably not being very clear, still very new to VBA. With the previous code there was a section at the end that I'm pretty sure grouped all of the active sheets together in to a single print. E.g. Instead of 4 prints it would be 1 file with 4 pages...At least I.m sure that's what it does

Sub CHECKSHEET()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
N = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Visible = xlSheetVisible And Sh.Range("A101").Value <> "" Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = Sh.Name
End If
Next
With ActiveWorkbook
.Worksheets(Arr).PrintOut
End With
End Sub

I'm struggling to figure out how to maintain that print process while also defining which columns to print. As sometimes I need to print to PDF as well as hard copies.

Have tried adding this back to your last code but to no avail. I may be missing something...

 

[JLGWhiz]

The only difference between printing PDF and regular print is the type of file you are printing. PDF is a type of file, meaning "Printable Document File". It is largely used for files which contain forms. Most inkjet and laser printers will print PDF files just like any other file. The code that I suggested, without the array, will print Excel spreadsheet files and PDF files if the printer has the capability to print. The only difference between the code with the array and the code I suggested is the method used to identify the separate sheets.