Printing selected sheets using an Index page with page #

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

I am currently using this code to print my reports. This code collects all the selected worksheets and numbers the pages on an Index page (worksheet). Is it possible to adjust the code that instead of using the worksheet name, it will use a text at a target range on that selected worksheet. I found that there are times the worksheet name may be different from the actual text on the sheet. SAMPLE, say one of my selected worksheets would be “Heating and Cooling” but I want the index page to only show Heating or Cooling or anything else.

The first 4 worksheets ("Cover Page", "Index", "Client Information", "Summary") and the last 2 worksheets ("Additional Photos", "Informational") will never change.

ONLY ("Utilities", "Grounds", "Structural Systems", "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Interior", "Bedroom", "Laundry", "Bathroom(s)", "Kitchen", "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa) MAY CHANGE. The target range is A7 for each of the selected worksheets.

NOTED HANS HAS ASSISTED ME PREVIOUSLY ON AN OLDER THREAD

Code: Select all

    Dim blnSelected As Boolean
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Dim i As Long
    Dim arrSheets As Variant

    'The order of array reflects order of index page
    arrSheets = Array("Cover Page", "Index", "Client Information", "Summary", "Utilities", "Grounds", "Structural Systems", _
        "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Interior", "Bedroom", "Laundry", "Bathroom(s)", "Kitchen", _
        "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa", "Additional Photos", "Informational")

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False
    Sheets("Index").Unprotect ""

    Application.ScreenUpdating = False

    If CheckBox2.Value = True Then
        Sheets("Index").Visible = xlSheetVisible
    Else
        Sheets("Index").Visible = xlSheetHidden
    End If


    Set wshI = Worksheets("Index")
    lngPage = 1
    lngRow = 6
    wshI.Cells.ClearContents
    Sheets("Index").Range("B3").Value = " Inspection Report Directory "
    Sheets("Index").Range("B5").Value = " Inspected locations "
    Sheets("Index").Range("C5").Value = " Page # "

    For i = 1 To 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            wshI.Range("B" & lngRow) = arrSheets(i - 1)
            wshI.Range("C" & lngRow) = lngPage
            lngRow = lngRow + 1
            Sheets(arrSheets(i - 1)).Activate
            lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
        End If
    Next i

    For i = 1 To 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            Sheets(arrSheets(i - 1)).Select Replace:=Not blnSelected
            blnSelected = True
        End If
    Next i

    If blnSelected = True Then
        ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
        Unload Me
        Unload UserForm4
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True
    Else
        MsgBox "No check boxes were selected and/or selections not open"
        Sheets("Index").Visible = xlSheetHidden
        ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True
    End If
    Application.ScreenUpdating = True
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Printing selected sheets using an Index page with page #

Post by HansV »

Will the first four and last two sheets also have the text to be displayed in A7, or only the "variable" sheets?
Regards,
Hans

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Re: Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

No they are in a different target location. Those sheets if selected will keep there sheet name for the Index page
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Printing selected sheets using an Index page with page #

Post by HansV »

Try this:

Code: Select all

    Dim blnSelected As Boolean
    Dim wshI As Worksheet
    Dim lngPage As Long
    Dim lngRow As Long
    Dim i As Long
    Dim arrSheets As Variant

    'The order of array reflects order of index page
    arrSheets = Array("Cover Page", "Index", "Client Information", "Summary", "Utilities", "Grounds", "Structural Systems", _
        "Detached Structure", "Roof & Attic", "Fireplace & Chimney", "Interior", "Bedroom", "Laundry", "Bathroom(s)", "Kitchen", _
        "Kitchen Appliances", "Heating and Cooling", "Water Heater", "Pool Spa", "Additional Photos", "Informational")

    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False
    Set wshI = Worksheets("Index")
    wshI.Unprotect

    Application.ScreenUpdating = False

    If CheckBox2.Value = True Then
        wshI.Visible = xlSheetVisible
    Else
        wshI.Visible = xlSheetHidden
    End If

    lngPage = 1
    lngRow = 6
    wshI.Cells.ClearContents
    wshI.Range("B3").Value = " Inspection Report Directory "
    wshI.Range("B5").Value = " Inspected locations "
    wshI.Range("C5").Value = " Page # "

    For i = 1 To 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            If i > 4 And i < 20 Then
                wshI.Range("B" & lngRow).Value = Worksheets(arrSheets(i - 1)).Range("A7").Value
            Else
                wshI.Range("B" & lngRow).Value = arrSheets(i - 1)
            End If
            wshI.Range("C" & lngRow).Value = lngPage
            lngRow = lngRow + 1
            Sheets(arrSheets(i - 1)).Activate
            lngPage = lngPage + Application.ExecuteExcel4Macro("Get.Document(50)")
        End If
    Next i

    For i = 1 To 21
        If Me.Controls("CheckBox" & i).Value And Sheets(arrSheets(i - 1)).Visible = xlSheetVisible Then
            Sheets(arrSheets(i - 1)).Select Replace:=Not blnSelected
            blnSelected = True
        End If
    Next i

    If blnSelected = True Then
        ActiveWindow.SelectedSheets.Application.Dialogs(xlDialogPrint).Show
        Unload Me
        Unload UserForm4
    Else
        MsgBox "No check boxes were selected and/or selections not open"
    End If
    wshI.Visible = xlSheetHidden
    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    Application.ScreenUpdating = True
Regards,
Hans

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Re: Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

Works perfectly thank you very much Hans
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Re: Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

One more thing, I noticed all my sheets are grouped, I would like to ungroup without selecting a specific worksheet
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Printing selected sheets using an Index page with page #

Post by HansV »

One sheet will have to be the active sheet at the end. which one do you prefer?
Regards,
Hans

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Re: Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

I guess I could use this

Code: Select all

Sheets("Cover Page").Select
At the end of code of course :grin:
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller

User avatar
HansV
Administrator
Posts: 74155
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Printing selected sheets using an Index page with page #

Post by HansV »

Yes, indeed.
Regards,
Hans

User avatar
ABabeNChrist
SilverLounger
Posts: 1806
Joined: 25 Jan 2010, 15:57
Location: Conroe, Texas

Re: Printing selected sheets using an Index page with page #

Post by ABabeNChrist »

Better yet, on a rare occasion the Cover Page sheet may be hidden so this might better

Code: Select all

    Sheets("Cover Page").Visible = xlSheetVisible
    Sheets("Cover Page").Select
When one door of happiness closes, another opens,
but often we look so long at the closed door that we do not see
the one that has been opened for us.
Helen Keller