I'm looking for some macro help. I have created a series of sheets in a workbook that will be populated from a question and answer sheet in the book. The sheets are broken down into "packages" for bidding purposes. The packages appear on separate sheets. Not all packages will be used on a particular project, and not all rows within a package will be populated.
I have been able to create a macro attached to a sheet that will merge the multiple sheets together into a newly created sheet named "Finalized List". After that, I get a little lost and am unsure how to write the remainder of what I would like to do.
I would like to compact the "Finalized List" sheet by eliminating/hiding any unused rows within that package (where the quantity for that item = 0), then also not include any packages that have quantities = 0 for all items within that package. I would like the contents from the first sheet (the first package) to have a package number that is dynamically created based on the total number of packages in the Finalized List sheet. As an example, let's say that my workbook has sheets associated with 10 packages. However the project I am workiing on only requires 6 of those packages. I would like the merged packages to become sequentially numbered-something like: 01-01, 01-02, 01-03 through 01-06 rather than just copying the package number from its respective sheet.
Next I would like to allow the user to set the print area for the Finalized List sheet (since this would be a dynamic # of rows, depending on packages) and then send to any printer to which they are connected. I would like to accomplish the compaction, print area and print functions via buttons that would be visible at the bottom of the newly created Finalized List sheet. This workbook(s) will be used by several users that are not very excel savvy and would like to make as much of it as foolproof as possible.
I hope this is as clear as possible. Please let me know if this is possible at all and what the code would look like.
Thanks for the help!
I have been able to create a macro attached to a sheet that will merge the multiple sheets together into a newly created sheet named "Finalized List". After that, I get a little lost and am unsure how to write the remainder of what I would like to do.
I would like to compact the "Finalized List" sheet by eliminating/hiding any unused rows within that package (where the quantity for that item = 0), then also not include any packages that have quantities = 0 for all items within that package. I would like the contents from the first sheet (the first package) to have a package number that is dynamically created based on the total number of packages in the Finalized List sheet. As an example, let's say that my workbook has sheets associated with 10 packages. However the project I am workiing on only requires 6 of those packages. I would like the merged packages to become sequentially numbered-something like: 01-01, 01-02, 01-03 through 01-06 rather than just copying the package number from its respective sheet.
Next I would like to allow the user to set the print area for the Finalized List sheet (since this would be a dynamic # of rows, depending on packages) and then send to any printer to which they are connected. I would like to accomplish the compaction, print area and print functions via buttons that would be visible at the bottom of the newly created Finalized List sheet. This workbook(s) will be used by several users that are not very excel savvy and would like to make as much of it as foolproof as possible.
I hope this is as clear as possible. Please let me know if this is possible at all and what the code would look like.
Thanks for the help!
