copy active sheet, create new workbook, name new workbook based on cell reference

I am trying to create a macro that will copy the active sheet and paste values, not formulas, to a new workbook (would need to create the workbook), name the workbook based on cell A4 in the worksheet and close. It would then go back to the original active sheet, select the next value in the data validation drop down list in cell A4 and repeat the entire procedure until it reaches the end of the data validation list. I've attached the worksheet here for a visual. Any help would be appreciated.

Hello there,

The below code should work for you:

PLEASE NOTE: I have highlighted the line of code that you will need to change to your Save as filepath red in the below code. If you do not know the file path, open a workbook in the folder/location and in an empty cell type =CELL("filepath") this will give you the location.

Please Login or Register  to view this content.

To insert this code

1. Press Alt+F8 on your keyboard
2. Clear the macro name field
3. Type the following in the macro name field SaveIBPQtrlyUpdate
4. Select the Create option
5. In between the Sub SaveIBPQtrlyUpdate() and End Sub copy and paste the above code, anything that appears in green is a comment meant to help you understand
6. Change the file path as instructed above
7. Close out of VBA and then press Alt+F8 again
8. Select the SaveIBPQtrlyUpdate macro and select Run.

Let me know if this works for you!

Thanks!

This works like a charm! Thank you so much!

If the hyperlinks are in the cells as a result of hard hyperlinks (CTRL-K to open the hyperlink window on a cell), those links should be retained when the "paste values" occurs. But if the cells have HYPERLINK() formulas in them, then those links would not survive the removal of the formulas themselves.