dcsimg
Page 1 of 2 12 LastLast
Results 1 to 40 of 43

Thread: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Hi,

    I am very new to VBA and need to be able to take data from multiple worksheets in a workbook and consolidate it into one worksheet in the same workbook based on check box selections. (sample file attached to show what I am trying to accomplish)

    I would need the code to be smart enough to copy formatting, etc.

    Appreciate your time.

    Thanks!
    Attached Files Attached Files

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    you can test this to see if it works for you

    Code:
    Dim s As Worksheet, rng As Range, dest As Worksheet
    Set dest = Sheets("Consolidated invoice")
    For ch = 1 To 4
    Set s = Sheets("intake form")
        With s.Shapes("Check Box " & ch)
            If .OLEFormat.Object.Value = 1 Then
                Set rng = Sheets(.OLEFormat.Object.Caption).UsedRange
                Set rng = rng.Offset(2).Resize(rng.Rows.Count - 2)
                rng.Copy dest.Cells(Rows.Count, 3).End(xlUp).Offset(2)
            End If
        End With
    Next
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you can test this to see if it works for you

    Code:
    Dim s As Worksheet, rng As Range, dest As Worksheet
    Set dest = Sheets("Consolidated invoice")
    For ch = 1 To 4
    Set s = Sheets("intake form")
        With s.Shapes("Check Box " & ch)
            If .OLEFormat.Object.Value = 1 Then
                Set rng = Sheets(.OLEFormat.Object.Caption).UsedRange
                Set rng = rng.Offset(2).Resize(rng.Rows.Count - 2)
                rng.Copy dest.Cells(Rows.Count, 3).End(xlUp).Offset(2)
            End If
        End With
    Next

    Hi Westconn1,

    I tried to do as suggested by copying your code under the View Code on the Consolidated Invoice tab but nothing happened. Please let me know if I am supposed to do something differently.

    I essentially want the VBA code to consolidate the data from the different tabs into 1 based on the different combinations of selections the customer can make on the Intake Form.

    Thanks!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    all code has to be within procedures (subs or functions), the code as posted is just a snippet, and can be included into an existing procedure or an event, like for a button click etc

    the code did what you asked but it is up to you how you want to call the code, one way would be to add a button on your worksheet with the checkboxes, then paste the code into it's click event, or create a procedure then assign that (assign macro) to the button, or you can just go to the vba editor, create a procedure, paste the code and run the procedure (run button or F5)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Could you help show me how to do this using the sample file I provided?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Beautiful thanks!! Is it pretty easy to do a print to PDF of the end result, the consolidated invoice tab? How do I make sure the print area of the consolidated invoice captures all of the results?

  8. #8

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Also, are you able to break down line by line what the vba code is doing so I can understand how to incorporate this to my actual data? This is really brilliant and I am so grateful for your help.

  9. #9

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    how does the code know to get the data that is on the Product A tab by just clicking on the Product A checkbox? Is the code dependent on the tab name matching the check box name?

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Is the code dependent on the tab name matching the check box name?
    basically yes
    this line
    Code:
    Set rng = Sheets(.OLEFormat.Object.Caption).UsedRange
    it is getting the usedrange of the sheet with name of the checkbox caption
    the next line trims the range down to the size of the information
    the following line copies the range and pastes it directly to the invoice sheet after any previous data
    the first line sets a sheet object variable to the invoice sheet

    Is it pretty easy to do a print to PDF of the end result, the consolidated invoice tab?
    yes quite easy

    How do I make sure the print area of the consolidated invoice captures all of the results?
    trim the used range to the data area you want
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Do you know why I get the below error message when I try to execute the code in my workbook?

    Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found.


    When I debug the line that is causing issues highlighted in yellow is:

    With s.Shapes("Check Box " & ch)

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    The item with the specified name wasn't found.
    what ever number ch is, at that point, there is no checkbox of that name, if you have changed the names of your checkboxes, no longer have 4 checkboxes, or have otherwise changed the code, that error would occur

    it certainly worked correctly when i tested
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Thank you, yes, I figured out the issue and had to rename the checkboxes.

    Could you please help me with the VBA code to do a print to PDF while making sure the margins adjust to the print area on the active sheet?

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    VBA code to do a print to PDF
    you want to print the area to a printer? or save as a pdf? or print to a pdf printer driver?

    the first and last are the same code, just change the active printer to match the printer you want to use
    what is the actual range you want to print? probably the area from Consolidated invoice

    you can test this to see if it does as you want
    Code:
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With
    r.PrintOut ActivePrinter:="FreePDF"
    change printer name and range etc to suit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you want to print the area to a printer? or save as a pdf? or print to a pdf printer driver?

    the first and last are the same code, just change the active printer to match the printer you want to use
    what is the actual range you want to print? probably the area from Consolidated invoice

    you can test this to see if it does as you want
    Code:
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With
    r.PrintOut ActivePrinter:="FreePDF"
    change printer name and range etc to suit
    Thank you, I will test!

  16. #16

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Hi Westconn1,

    I tried to do as suggested by copying your code under the View Code on the Consolidated Invoice tab but nothing happened. Please let me know if I am supposed to do something differently.

    I essentially want the VBA code to consolidate the data from the different tabs into 1 based on the different combinations of selections the customer can make on the Intake Form.

    Thanks!

    For some reason, when I try to execute the above code in my workbook. I get an error message that states: "Run Time Error: 1004: Copy method of Range class failed."

    The below line is then highlighted in yellow:
    rng.Copy dest.Cells(Rows.Count, 3).End(xlUp).Offset(2)

    Do you know why that would be?

    In your workbook sample, it also copied in the lines by order of product, A and then C for example. In my file, it is overwriting and posting C cut off by A at the bottom. I'm stumped why it is not working the way yours is. I've copied the code as is...

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    rng.Copy dest.Cells(Rows.Count, 3).End(xlUp).Offset(2)
    in this 3 is the column number, tested for end of data, you may need to change this to be the longest column in your target

    but i have no idea why it would give error
    if you changed the checkbox names it could affect the order the products are added

    i only went by the setup in the original workbook you posted
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    in this 3 is the column number, tested for end of data, you may need to change this to be the longest column in your target

    but i have no idea why it would give error
    if you changed the checkbox names it could affect the order the products are added

    i only went by the setup in the original workbook you posted
    Not a problem, you have been a great help. I started from scratch and it worked. Must have been something in my file, not sure...

  19. #19

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Thank you, I will test!
    How would I adjust the above code to print to PDF on my screen? I can then save to a location on my computer.

    So my goal is to have the consolidated invoice page populated with all of the pricing for the different products, and then print to PDF on screen (ranges could vary depending on what products are selected).

    If I have tons of products the Consolidated invoice tab can get quite long, how do I ensure the page breaks for the PDF file occur where I want them to, so I don't cut off pricing for product Z in the middle but rather page break before if there isn't enough space to display all of its usedrange results?

    I promise this will be the last of my questions You have been wonderful to work with, I really appreciate your time and willingness to help. I'm truly amazed at what you can do with VBA coding.

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    If I have tons of products the Consolidated invoice tab can get quite long, how do I ensure the page breaks for the PDF file occur where I want them to, so I don't cut off pricing for product Z in the middle but rather page break before if there isn't enough space to display all of its usedrange results?
    this is actually more difficult, there was a thread recently (last few months) in this forum to do very similar, set all the pagebreaks to desired positions, where i posted some code, try a search for that, or i will try to post a link later

    save the file to some temporary directory\file, then use shellexecute to display the file in your default pdf browser, the user can then saveAs,
    you can keep overwriting the temp file, so as to not fill a folder with temp files, how to do this may depend on your pdf printer
    alternatively if your worksheet is clean then you can just save the entire worksheet as a pdf file
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  21. #21

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you want to print the area to a printer? or save as a pdf? or print to a pdf printer driver?

    the first and last are the same code, just change the active printer to match the printer you want to use
    what is the actual range you want to print? probably the area from Consolidated invoice

    you can test this to see if it does as you want
    Code:
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With
    r.PrintOut ActivePrinter:="FreePDF"
    change printer name and range etc to suit

    Thanks! How would I adjust your code above to have it print to PDF on my screen?

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    you need to look at whatever pdf driver you use to see hoe\w to save to a predefined location\filename, and change the name for activeprinter to match that printer driver

    here is a link to the thread about setting pagebreaks to suit your product grouping
    http://www.vbforums.com/showthread.p...ight=pagebreak
    Last edited by westconn1; Apr 17th, 2019 at 03:55 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you need to look at whatever pdf driver you use to see hoe\w to save to a predefined location\filename, and change the name for activeprinter to match that printer driver

    here is a link to the thread about setting pagebreaks to suit your product grouping
    http://www.vbforums.com/showthread.p...ight=pagebreak

    I got the PDF to work Thanks for sharing the link, will take a look.

  24. #24

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you need to look at whatever pdf driver you use to see hoe\w to save to a predefined location\filename, and change the name for activeprinter to match that printer driver

    here is a link to the thread about setting pagebreaks to suit your product grouping
    http://www.vbforums.com/showthread.p...ight=pagebreak


    So I got the print to PDF to work out great, by replacing the printer name as suggested

    I've also checked out the code on the page break. How do I combine the Print to PDF vba code with the Page Break code shown below? I tried to see if I could run the below code on its own and it gave me an error message saying: Compile Error: Invalid or unqualified reference. It is highlighting the .Item in this part of the code: Set r = PrintVersion.Cells(.Item(pb).Location.Row, 1). How do I advise it to run the page break code on the Consolidated Invoice sheet/tab?

    Print to PDF VBA CODE:

    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    End With
    r.PrintOut ActivePrinter:="Microsoft Print to PDF"



    Page Break VBA CODE:

    With PrintVersion.HPageBreaks

    pb = 1
    Do
    ' check if first column is empty
    Set r = PrintVersion.Cells(.Item(pb).Location.Row, 1)
    If r.Value = "" Then
    ' find previous cell in column 1 which is not empty
    Set fnd = PrintVersion.Columns(1).Find("*", r, , , , xlPrevious)
    ' set page break 1 row above it
    Set .Item(pb).Location = fnd.Offset(-1, 0)
    DoEvents

    End If
    pb = pb + 1
    If pb > .Count Then Exit Do
    Loop
    End With

    Thanks!!

  25. #25
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    looks like you copied the code from post #19, but you needed some extra lines that were in post #16, where printversion was assigned to a worksheet

    if you incorporate the pagebreak code above into the print code, it should look like

    Code:
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With
    s..ResetAllPageBreaks
    with s.HPageBreaks
    pb = 1
    Do
        ' check if first column is empty
        Set r =  s.Cells(.Item(pb).Location.Row, 1)   ' change column to suit on this line
        if not isempty(r.offset(-1)) then                                   ' if column 1 is not empty then move page break
            ' find previous cell in column 1 which is not empty
            Set fnd = s.Columns(1).Find("*", r, , , , xlPrevious)
            ' set page break 1 row above it
            Set .Item(pb).Location = fnd.Offset(-1, 0)
        End If
        pb = pb + 1
        If pb > .Count Then Exit Do
    Loop
    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"
    this is all written in the browser, so totally untested and may contain typos

    when you post code, use code tags
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  26. #26

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    looks like you copied the code from post #19, but you needed some extra lines that were in post #16, where printversion was assigned to a worksheet

    if you incorporate the pagebreak code above into the print code, it should look like

    Code:
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated invoice")
    Set r = s.Range("L7").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
    End With
    s..ResetAllPageBreaks
    with s.HPageBreaks
    pb = 1
    Do
        ' check if first column is empty
        Set r =  s.Cells(.Item(pb).Location.Row, 1)   ' change column to suit on this line
        if not isempty(r.offset(-1)) then                                   ' if column 1 is not empty then move page break
            ' find previous cell in column 1 which is not empty
            Set fnd = s.Columns(1).Find("*", r, , , , xlPrevious)
            ' set page break 1 row above it
            Set .Item(pb).Location = fnd.Offset(-1, 0)
        End If
        pb = pb + 1
        If pb > .Count Then Exit Do
    Loop
    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"
    this is all written in the browser, so totally untested and may contain typos

    when you post code, use code tags

    Thanks so much for your quick response!

    So when I enter this vba code into my file, I get an Run Time Error '9': Subscript out of range and the below line is highlighted in yellow:

    Set r = s.Cells(.Item(pb).Location.Row, 2)



    Thanks!

  27. #27
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    you will get that error if there are no horizantal page breaks, a small change can fix
    also as mentioned in the other thread you must change to page break preview, to move the page breaks

    Code:
    s.Parent.Windows(1).View = xlPageBreakPreview
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
        ' check if first column is empty
        Set r = s.Cells(.Item(pb).Location.Row, 12)    ' change column to suit on this line
        If Not IsEmpty(r.Offset(-1)) Then                                   ' if column x is not empty then move page break
            ' find previous cell in column 1 which is not empty
            Set fnd = s.Columns(12).Find("Product", r, , , , xlPrevious)    ' find previous  Product header
            ' set page break 1 row above it
            Set .Item(pb).Location = fnd
        End If
        pb = pb + 1
    '    If pb > .Count Then Exit Do
    Loop
    End With
    i tested this with your original sample
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  28. #28

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    you will get that error if there are no horizantal page breaks, a small change can fix
    also as mentioned in the other thread you must change to page break preview, to move the page breaks

    Code:
    s.Parent.Windows(1).View = xlPageBreakPreview
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
        ' check if first column is empty
        Set r = s.Cells(.Item(pb).Location.Row, 12)    ' change column to suit on this line
        If Not IsEmpty(r.Offset(-1)) Then                                   ' if column x is not empty then move page break
            ' find previous cell in column 1 which is not empty
            Set fnd = s.Columns(12).Find("Product", r, , , , xlPrevious)    ' find previous  Product header
            ' set page break 1 row above it
            Set .Item(pb).Location = fnd
        End If
        pb = pb + 1
    '    If pb > .Count Then Exit Do
    Loop
    End With
    i tested this with your original sample

    Hi Westconn1,

    I tested the below code and the page breaks didn't adjust on the Pricing Proposal page. The page break command looks like it was applied on the Deal Input Form where the check box options to select the products are. How do I tell it to perform the page break actions on the Pricing Proposal worksheet? I didn't get any run time errors this time which is good

    Also I noticed the page breaks were horizontal, shouldn't they be vertical? My products run down on the excel sheet and I would want the page break to come in before a product if its used range results will be split between 2 sheets.


    Updated Code:

    Sub Printtopdf()

    Dim r As Range, s As Worksheet
    Set s = Sheets("Pricing Proposal")
    Set r = s.Range("C2").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    End With
    s.Parent.Windows(1).View = xlPageBreakPreview
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
    ' check if first column is empty
    Set r = s.Cells(.Item(pb).Location.Row, 12) ' change column to suit on this line
    If Not IsEmpty(r.Offset(-1)) Then ' if column x is not empty then move page break
    ' find previous cell in column 1 which is not empty
    Set fnd = s.Columns(12).Find("Product", r, , , , xlPrevious) ' find previous Product header
    ' set page break 1 row above it
    Set .Item(pb).Location = fnd
    End If
    pb = pb + 1
    ' If pb > .Count Then Exit Do
    Loop
    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"

    End Sub


    Thanks!

  29. #29

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Hi Westconn1,

    I tested the below code and the page breaks didn't adjust on the Pricing Proposal page. The page break command looks like it was applied on the Deal Input Form where the check box options to select the products are. How do I tell it to perform the page break actions on the Pricing Proposal worksheet? I didn't get any run time errors this time which is good

    Also I noticed the page breaks were horizontal, shouldn't they be vertical? My products run down on the excel sheet and I would want the page break to come in before a product if its used range results will be split between 2 sheets.


    Updated Code:

    Sub Printtopdf()

    Dim r As Range, s As Worksheet
    Set s = Sheets("Pricing Proposal")
    Set r = s.Range("C2").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    End With
    s.Parent.Windows(1).View = xlPageBreakPreview
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
    ' check if first column is empty
    Set r = s.Cells(.Item(pb).Location.Row, 12) ' change column to suit on this line
    If Not IsEmpty(r.Offset(-1)) Then ' if column x is not empty then move page break
    ' find previous cell in column 1 which is not empty
    Set fnd = s.Columns(12).Find("Product", r, , , , xlPrevious) ' find previous Product header
    ' set page break 1 row above it
    Set .Item(pb).Location = fnd
    End If
    pb = pb + 1
    ' If pb > .Count Then Exit Do
    Loop
    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"

    End Sub


    Thanks!


    Forgot to mention that my PrinttoPDF macro is assigned to a button on the Deal Input tab. I didn't want my button to be displayed on the printed sheet (Consolidated Invoice/Pricing Proposal) as it will be customer facing so I put it on the Deal Input tab.

  30. #30
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    try s.activate before s.Parent.Windows(1).View = xlPageBreakPreview
    i had not done that but the worksheet i was working with was already active, but i had that in the code in the original thread

    i also have found that when changing the printarea you will also need .fittopagestall= false in the pagesetup before trying to move the pagebreaks, else it will try to fit all to a single page height

    you can change back to the original tab and view if required after the pagebreaks have been repositioned
    Code:
    s.Parent.Windows(1).View = xlNormalView
    Also I noticed the page breaks were horizontal
    this is correct they go across the page (horizontally) to set the height of the pages

    if you do not want customer to see workings, you can use
    application.sreenupdating = false or s.Parent.Windows(1).Visible = false at the beginning of the code, but make sure to revert before the code finishes, probably best before doing the printout
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  31. #31

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    try s.activate before s.Parent.Windows(1).View = xlPageBreakPreview
    i had not done that but the worksheet i was working with was already active, but i had that in the code in the original thread

    i also have found that when changing the printarea you will also need .fittopagestall= false in the pagesetup before trying to move the pagebreaks, else it will try to fit all to a single page height

    you can change back to the original tab and view if required after the pagebreaks have been repositioned
    Code:
    s.Parent.Windows(1).View = xlNormalView
    this is correct they go across the page (horizontally) to set the height of the pages

    if you do not want customer to see workings, you can use
    application.sreenupdating = false or s.Parent.Windows(1).Visible = false at the beginning of the code, but make sure to revert before the code finishes, probably best before doing the printout

    Hi Westconn1,

    I've read through your message and tried to incorporate it into my updated code shown below. But am having no luck. When I included the s.activate before s.Parent.Windows(1).View = xlPageBreakPreview, it showed the pricing proposal/consolidated invoice tab in page break view. But the page breaks aren't adjusting, it's still cutting in between the results section between 2 sheets. When I added .fittopagestall= false or s.fittopagestall= false, I got a compile error where that line was highlighted in yellow. If I just include it as FitToPagesTall = False in the same section in below code the error goes away but I still don't have the page breaks in the right place. Not sure how to get the page breaks where I need them...is this possible? If you could kindly include entire code, I would really appreciate your help.



    Sub Printtopdf()

    Dim r As Range, s As Worksheet
    Set s = Sheets("Pricing Proposal")
    Set r = s.Range("C2").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    End With
    s.Activate
    s.Parent.Windows(1).View = xlPageBreakPreview
    s.FitToPagesTall = False
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
    ' check if first column is empty
    Set r = s.Cells(.Item(pb).Location.Row, 12) ' change column to suit on this line
    If Not IsEmpty(r.Offset(-1)) Then ' if column x is not empty then move page break
    ' find previous cell in column 1 which is not empty
    Set fnd = s.Columns(12).Find("Product", r, , , , xlPrevious) ' find previous Product header
    ' set page break 1 row above it
    Set .Item(pb).Location = fnd
    End If
    pb = pb + 1
    ' If pb > .Count Then Exit Do
    Loop
    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"

    End Sub

    Thanks!

  32. #32

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Are you able to apply this code to the file I shared with you earlier so I know how it works? Since I'm so new to VBA code, I may not understand how the row and column references are working and maybe that is what is throwing me off... I was able to back into the code last time based on the sample file you provided me with the coding.

  33. #33

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Are you able to apply this code to the file I shared with you earlier so I know how it works? Since I'm so new to VBA code, I may not understand how the row and column references are working and maybe that is what is throwing me off... I was able to back into the code last time based on the sample file you provided me with the coding.


    In the attached sample file, all of my products are named Product A, Product B, Product C. What if that wasn't the case and my products all had unique names like. Product A, Combination B, Package C, etc. Not sure if that has any impact on the coding as the usedname ranges don't always start with Product.

  34. #34

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Are you able to apply this code to the file I shared with you earlier so I know how it works? Since I'm so new to VBA code, I may not understand how the row and column references are working and maybe that is what is throwing me off... I was able to back into the code last time based on the sample file you provided me with the coding.


    In the attached sample file, all of my products are named Product A, Product B, Product C. What if that wasn't the case and my products all had unique names like. Product A, Combination B, Package C, etc. Not sure if that has any impact on the coding as the usedname ranges don't always start with Product.
    Attached Files Attached Files

  35. #35
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Not sure if that has any impact on the coding as the usedname ranges don't always start with Product.
    the code as written is based on the checkbox caption matching the sheet name, regardless of the content of the sheetname, this can be changed, but i could, at the time, only go by the format of the original sample
    looking at your new sample it should still work fine, but i did not test

    When I added .fittopagestall= false or s.fittopagestall= false
    fit to pages tall is a member of the pagesetup, and should be like
    Code:
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    .fittopagestall = 0
    End With
    you learn nothing if i just post the entire code for copy /paste
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  36. #36

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by westconn1 View Post
    the code as written is based on the checkbox caption matching the sheet name, regardless of the content of the sheetname, this can be changed, but i could, at the time, only go by the format of the original sample
    looking at your new sample it should still work fine, but i did not test

    fit to pages tall is a member of the pagesetup, and should be like
    Code:
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    .fittopagestall = 0
    End With
    you learn nothing if i just post the entire code for copy /paste

    I agree. Can you help me decipher the below so I know what number to put in? What resources do you recommend for learning VBA? I'm just googling as I go. I'll spend more time looking at the previous thread you referenced as well.

    Set r = s.Cells(.Item(pb).Location.Row, 12) ' change column to suit on this line


    Thanks!

  37. #37
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    pb is the index number for the hpagebreak........ starting at pb = 1 then incrementing as it deals with each in turn

    12 is the column (L) to check for data, should also be the one that contains the "Product" subheading

    I'm just googling as I go.
    that is what i do too, plus trial and error with debugging
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  38. #38

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Sub Printtopdf()

    Dim r As Range, s As Worksheet, fnd As Range
    Set s = Sheets("Pricing Proposal")
    Set r = s.Range("C2").Resize(, 4)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
    .PrintArea = r.Address
    .CenterHorizontally = True
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    End With


    s.Activate
    s.Parent.Windows(1).View = xlPageBreakPreview

    ' first clear any set page breaks
    On Error Resume Next
    For Each pb In s.HPageBreaks
    pb.Delete
    Next
    On Error GoTo 0

    ' move preposed breaks to top of segement
    With s.HPageBreaks
    For pb = 1 To .Count
    Set r = Cells(.Item(pb).Location.Row, 3)
    Set fnd = Range("c:c").Find("*", r, , , , xlPrevious)
    If Not Intersect(fnd.Offset(, -1).Resize(fnd.Offset(, 1).End(xlDown).Row - fnd.Row + 5, 3), r) Is Nothing Then
    Set .Item(pb).Location = fnd
    DoEvents
    End If
    Next

    End With
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"

    End Sub


    I couldn't get my previous code to work so I used the code from the previous thread and am still not having any luck. Not sure where I am going wrong.

    What is this part of the code below trying to do? Maybe i'm not referencing the right row/column #s. No matter what I change the number values to in the below code, nothing changes in my file, the page breaks stay where they are....
    If Not Intersect(fnd.Offset(, -1).Resize(fnd.Offset(, 1).End(xlDown).Row - fnd.Row + 5, 3), r) Is Nothing Then Set .Item(pb).Location = fnd

    Thanks!

  39. #39
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,212

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    this is tested to work with the sample workbook in post #34

    as the data is now in column C, whereas in your original sample it was in column L, all references to column 12 had to be changed to column 3

    the pages breaks for the sample were moved to the following rows
    65
    130
    191
    256
    317
    382
    443
    508
    569
    634
    695
    760
    821
    886
    947
    Code:
    Sub Printtopdf()
    
    Dim r As Range, s As Worksheet
    Set s = Sheets("Consolidated Invoice")
    Set r = s.Range("C2").Resize(, 6)
    Set r = r.Resize(s.Cells(Rows.Count, r.Column).End(xlUp).Row - r.Row + 1)
    With s.PageSetup
        .PrintArea = r.Address
        .CenterHorizontally = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    s.Activate
    s.Parent.Windows(1).View = xlPageBreakPreview
    'FitPagestotall = False
    s.ResetAllPageBreaks
    With s.HPageBreaks
    pb = 1
    Do While .Count >= pb
        Set r = s.Cells(.Item(pb).Location.Row, 3)    ' change column to suit on this line
        If Not r = "Product" Then                                   ' if column x is not already above Product  then move page break
            ' find previous cell in column 1 which has Poduct sub heading
            Set fnd = s.Columns(3).Find("Product", r, , , , xlPrevious)    ' find previous  Product header
            ' set page break 1 row above it
            Set .Item(pb).Location = fnd
        End If
        pb = pb + 1
    Loop
    End With
    
    s.PrintOut ActivePrinter:="Microsoft Print to PDF"
    
    End Sub
    any changes to work book may need any code to be modified, though of course you could have used code to find which column the product sub heading is in, it seemed like a bit unnecessary
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  40. #40

    Thread Starter
    Member
    Join Date
    Dec 2018
    Posts
    49

    Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection

    Quote Originally Posted by Nish12 View Post
    Hi Westconn1,

    I tried to do as suggested by copying your code under the View Code on the Consolidated Invoice tab but nothing happened. Please let me know if I am supposed to do something differently.

    I essentially want the VBA code to consolidate the data from the different tabs into 1 based on the different combinations of selections the customer can make on the Intake Form.

    Thanks!

    Will the above code work if I have more than 5 buttons? Lets say I have 20 buttons. Do I just update "For ch = 1 To 4" to "For ch = 1 To 20"?

    When I add checkboxes they sometimes don't increment in order, could this cause an issue? I have an issue where the "With s.Shapes("Check Box " & ch)" line of code is causing a run time error '-2147024809 (80070057)': The item with the specified name wasn't found. Trying to rename the check box in the name field to the top left of excel but the update isn't sticking....it reverts back to the check box number it has.

    Open to suggestions.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width