VBA Code to Save a WorkSheet to a New File Using Cell Ranges to define Sheet and File

[Daveychops]

Hi there, I hope this request finds you well and in super fine spirits today .

I have searched through the archive and see that there are plenty of excellent solutions but not one that fits all my requirements (i'm just such a control freak!). This is my first request so apologies if anything is amiss.

I use Microsoft Office Excel 365 Pro Plus have a file that contains sorted data in a number of worksheets and would like a flexible macro to save an individual worksheet (with a defined name using a cell's content) to a separate file (with a defined location & name using two cell's contents)

Cell A1 will have the location to be saved "C:\..."
Cell A2 will have the file name to be saved "Monthend..."
Cell A3 will have the name of the worksheet (to be isolated and saved as a file) "Closed..."

That is the basic requirement and would be brilliant to have solved in itself; however, if there is an extra challenge you're game for...could you add in a pop up window that would ask for the worksheet name (that would be stored in cell A3).

Cheers again, have a nice one. Dave

 

[Fluff]

Hi & welcome to MrExcel
How about something like

Sub Daveychops()
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   Sheets(Ws.Range("A3").Value).Copy
   ActiveWorkbook.SaveAs Ws.Range("A1").Value & Ws.Range("A2").Value, 52
End Sub

 

[Daveychops]

Hi & welcome to MrExcel
How about something like

Sub Daveychops()
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   Sheets(Ws.Range("A3").Value).Copy
   ActiveWorkbook.SaveAs Ws.Range("A1").Value & Ws.Range("A2").Value, 52
End Sub

Ahhh awesome stuff, such a small thing that does a great job (as my girlfriend keeps telling me) thank you, have a nice day

 

[Fluff]

You're welcome & thanks for the feedback