SaveAs of multi sheet workbook with filters?

[V8burble]

I have a Workbook containing seven or eight Worksheets. The first Worksheet is a data entry and options selection sheet displaying a selection of Textboxes, Option buttons, Combo boxes etc, the second sheet contains reference data, following these are a number of Worksheets that are calculated and filtered as a result of those options and inputs. A 'print' button on the input Worksheet then runs a Procedure that applies the relevant filters, assigns print areas and prints out the 4 or 5 filtered worksheets. I now want to expand this Procedure to take a copy of just these filtered Worksheets and save them as another Workbook (values only) for subsequent free editting. I have started by selecting the relevant cells on the first filtered Worksheet, copying, opening a new Workbook, renaming the first Worksheet to match the one I'm copying, PasteSpecial formats, PasteSpecial the values, go back to the original Workbook, select the relevant cells on the next Worksheet, copying, opening the new Workbook, renaming the Worksheet etc etc. Now this seems to be a very labour intensive approach and I'm wondering if there is an easier way and how to do it. Ideally something like... taking a copy of the whole Workbook somehow changing all the cells to values only, rather than formulars then deleting the first two Worksheets (input and data ones).

Help!


Thanks

[Greg M]

Hi there,

See if the following code does what you require:

Please Login or Register  to view this content.

Obviously the worksheet names defined in the constants will need to be changed to match the names of the worksheets used in your workbook.

Similarly, the prefix defined above can be changed to whatever value you prefer.

The code works by saving a copy of your "Master" workbook in the same folder as the "Master" workbook itself, and with the same name as the "Master" workbook, but with the specified prefix added at the start of the name.

The code then opens the newly-created copy and scans all of its worksheets (except for the "Data Entry" and "Reference Data" worksheets).

During the scanning process the code deletes any rows which are hidden (e.g. by a filtering process), does a PasteSpecial operation of the values of all cells in the UsedRange of the worksheet, and turns any AutoFiltering off.

Finally, the code deletes the "Data Entry" and "Reference Data" worksheets, and saves the modified version of the "filtered" workbook.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[V8burble]

Thank you Greg for a very well explained and obviously time consuming answer, it all looks great

I'll give it a go, thank you.

[V8burble]

You are a STAR Greg, thank you.... works like a dream

[Greg M]

Hi again,

Many thanks for your feedback and for the compliment - I'm delighted that I was able to help.

Regards,

Greg M