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

Daveychops

New Member
Joined
Mar 25, 2019
Messages
3
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 ;)
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,457
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel
How about something like
Code:
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
 
Last edited:

Daveychops

New Member
Joined
Mar 25, 2019
Messages
3
Hi & welcome to MrExcel
How about something like
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,457
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,407
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top