-
1 Attachment(s)
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!
-
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
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)
-
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?
-
1 Attachment(s)
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
-
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?
-
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.
-
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?
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
Quote:
Is it pretty easy to do a print to PDF of the end result, the consolidated invoice tab?
yes quite easy
Quote:
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
-
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)
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
-
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?
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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...
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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...
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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.
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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?
-
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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.
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!!
-
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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.
-
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
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
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.
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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.
-
1 Attachment(s)
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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.
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
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
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
westconn1
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!
-
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
Quote:
I'm just googling as I go.
that is what i do too, plus trial and error with debugging
-
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!
-
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
Quote:
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
-
Re: Combing Multiple Excel Worksheets onto One Worksheet Based on Check Box Selection
Quote:
Originally Posted by
Nish12
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.