Macro, SaveAs in New Instance of Excel, PasteValues, Close, return to Original Workbook

[jacobrcotton]

Hey Everyone,

I doubt this is a new/unique ask, but I'm having trouble searching for it. I have a workbook/macro that does a series of calls to an SQL server, returning recordsets, and pasting that data into a Workbook. The Workbook then uses that data/recordsets for a bunch of formulas and charts and exhibits.

The ask (to me) is to run this in a batch for multiple clients. Specifically, run the macro, PasteValues, SaveAs (with a specific file name), then run it again for the next client.

The problem I have is that once I Paste Values, the formulas don't exist, and it won't work for the next client.

My proposed solution:
(1) run the macro - Original Workbook
(2) ALT + Save As, which will Save As & open the new save as new instance of Excel - New Workbook
(3) Paste Values - New Workbook
(4) Save the New Workbook
(5) Close the New Workbook
(6) Refocus on the Original Workbook, run next client

Problems I'm having:
(1) I don't know how to ALT+SaveAs in VBA. I can .SaveAs FileName easily, but I can't find out how to ".SaveAsNewWorkbook FileName" (not sure if I'm communicating that correctly or very well)
(2) Is there an easier solution to this problem that my proposed solution above? Am I overthinking this?

Thanks!

 

[StephenCrump]

I'd do the copy/paste values/formatting into a copy worksheet(s) within ThisWorkbook, and copy that sheet(s) to a new workbook, to save for the client.