Help with Copying sheets to new workbook and naming file based on cell value

[Ultim8]

Hello all,

Firstly sorry but i couldn't find the solution via search. Its been a while since i used excel to a VBA level and I seem to have forgotten everything (nearly 10 years since i used it properly).

Basically i want to have a button on my worksheet "Cost".

The button needs to:

1) Move worksheets "Cost", "Quote" and "Invoice" to a new workbook. without the button and VBA on it.
2) It also needs to remove formula's from some cells as there linked to the remaining tabs and just put them in as values these are Cost!A16:A22
3) Set the save directory as C:\Water Worx\Customer Quotes And Invoices
4) save the file as Cost!F13's value and as an .xlsx file

Thanks in advance.

 

[Tom Urtis]

Almost everything you have listed here is achievable by using the macro recorder. You can create a new workbook, copy the 3 sheets in that new workbook (which if you copy and paste special for values, then for formats will not bring forward any code in the sheet module), delete the buttons and link formulas, and save and name the file. You can polish the recorded code to make it a bit more efficient but there is nothing dynamic in your request, except maybe for the pre-existence of a file with the same name as the name you wantto save the new workbook as. In that case, you can sandwich the save line between

Application.DisplayAlerts = False
'save line of code goes here
Application.DisplayAlerts = True

If the buttons carry over and they are Forms buttons you can include the line
ActiveSheet.Buttons.Delete
as you copy each sheet.

Have you tried that, if not maybe give it a shot, and if you get stuck post back.

 

[Ultim8]

ok thanks Tom i'll give that a shot tonight.