Use VBA to save as a new workbook with a specific file name and file location

[woah]

Hi there,

I am looking for a VBA that can save the "Column A to E" of "sheet 1" to another new workbook with a specific file name (refer to cell G1) and a specific file folder.

Please kindly advise. Thank you very much.

 

[DanteAmor]

Hello @woah

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Specify on this line of the macro the folder you want.
sPath = "C:\trabajo\files\"

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
  
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
  
  sh.Range("A:E").Copy wb.Sheets(1).Range("A1")
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[woah]

Hello @woah

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

Specify on this line of the macro the folder you want.
sPath = "C:\trabajo\files\"

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
 
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
 
  sh.Range("A:E").Copy wb.Sheets(1).Range("A1")
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you so much!!! It works perfectly. I only have one more question. If I want to save the worksheet in the new worksheet with the name "Locker", what should I do? It now appears as "Sheet 1" only. Thank you!!!!

 

[DanteAmor]

If I want to save the worksheet in the new worksheet with the name "Locker"

Please try this:

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
  
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
  
  sh.Range("A:E").Copy wb.Sheets(1).Range("A1")
  wb.Sheets(1).Name = "Locker"
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False
End Sub

Have a nice day

 

[woah]

Please try this:

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
 
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
 
  sh.Range("A:E").Copy wb.Sheets(1).Range("A1")
  wb.Sheets(1).Name = "Locker"
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False
End Sub

Have a nice day

Sorry, I just came up with another question. If I only want to save the range content (A10:E), how can I do it? But the range sometimes contain more or less data.

 

[woah]

EDIT:


Sorry, I just came up with another question. If I only want to save the part of the content (A7:E), how can I do it? But the range sometimes contain more or less data, and some column not always contain data. Can I use Column C as reference to copy content? So that, I want it to copy up to the last row of the range (Column C) with content.

 

[DanteAmor]

If I only want to save the part of the content (A7:E),

Use this:

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
  
  sh.Range("A7:E" & sh.Range("C" & Rows.Count).End(3).Row).Copy wb.Sheets(1).Range("A1")
  wb.Sheets(1).Name = "Locker"
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False

  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

Have a nice day

 

[woah]

Use this:

Sub saveNewWorkbook()
  Dim sh As Worksheet
  Dim wb As Workbook
  Dim sPath As String
 
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
 
  Set sh = ThisWorkbook.Sheets("Sheet1")
  Set wb = Workbooks.Add(xlWBATWorksheet)
  sPath = "C:\trabajo\files\"     'specific file folder
 
  sh.Range("A7:E" & sh.Range("C" & Rows.Count).End(3).Row).Copy wb.Sheets(1).Range("A1")
  wb.Sheets(1).Name = "Locker"
  wb.SaveAs sPath & sh.Range("G1").Value & ".xlsx"
  wb.Close False

  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

Have a nice day

Sorry that once I changed the range into "A14:G" and used Column "D" as the reference to extract data in last row, the new workbook still copy the whole worksheet. Also, some cells become (#REF!) and format is totally mess up.

May I know the reason? Thank you

 

[DanteAmor]

I only have one more question. If I want to save the worksheet in the new worksheet with the name "Locker"

If I only want to save the range content (A10:E)

If I only want to save the part of the content (A7:E) ... copy up to the last row of the range (Column C)

once I changed the range into "A14:G" and used Column "D" ... Also, some cells become (#REF!) and format is totally mess up.
May I know the reason?

It's hard to give you a diagnosis of what's going on, if you're continually changing the requirement. And that's fine, but if you're modifying the macro, I really don't know what you changed if you don't put the modified macro here.

On the other hand, if you have formulas, we need to know if you want to paste the formulas or the values.

I suggest that you carefully analyze what you need and once you are clear about your objective, come back here and create a new thread with all your needs. That way we can deliver a macro that works for you.

This is very important: in the new thread you put how your sheet is and how you want the sheet in the new book.

Cordially
Dante Amor