Save sheet as a new workbook with date from specific cell

[Aviles]

Hi all,

I have a sheet named "Temp" that I would like to save as a new seperate workbook (xlsx), with the name "Trade Date" together with the specific date from cell C4 of the "Home" sheet (eg: "Trade Date dd/mm/yyyy")

I would like to name the sheet of this new workbook "Dealt" and then for the workbook to be saved in the following directory: S:\Folder1\Folder2\Folder3

I would then like the new workbook to be closed and then return to the original source workbook.

I found this code which I believe is a good start, but I'm not sure how to add the date from cell C4 of Home sheet.

Sub Copy()

Sheets("Temp").Copy
ActiveWorkbook.SaveAs "Trade Date.xlsx"
ActiveWorkbook.Close

End Sub

Any help would be appreciated.

Thanks,
Ed.

 

[DanteAmor]

eg: "Trade Date dd/mm/yyyy"

You cannot put the "/" in a file name, I recommend using the "-"

How about:

Sub Copy()
  Dim sDate As String, sPath As String
  sPath = "S:\Folder1\Folder2\Folder3\"

  sDate = Format(Sheets("Home").Range("C4").Value, "dd-mm-yyyy")
  Sheets("Temp").Copy
  ActiveSheet.Name = "Dealt"
  ActiveWorkbook.SaveAs sPath & "Trade Date " & sDate & ".xlsx"
  ActiveWorkbook.Close False
End Sub

 

[Aviles]

You cannot put the "/" in a file name, I recommend using the "-"

How about:

Sub Copy()
  Dim sDate As String, sPath As String
  sPath = "S:\Folder1\Folder2\Folder3\"

  sDate = Format(Sheets("Home").Range("C4").Value, "dd-mm-yyyy")
  Sheets("Temp").Copy
  ActiveSheet.Name = "Dealt"
  ActiveWorkbook.SaveAs sPath & "Trade Date " & sDate & ".xlsx"
  ActiveWorkbook.Close False
End Sub

Hi @DanteAmor

Thanks for the reply. I tried your code and it works well, thank you. I would like to make a couple of amendments if I could please.

How can the code be changed to paste values only to the new workbook?

Also, after I finish with this funtion, I would like to continue with additional code after the 'ActiveWorkbook.Close False' line and before 'End Sub', but it seems like this code ends the Sub before it can get to my other code. Is there a way to amend the code so it does not end the current Sub?

Thanks,
Ed.

 

[DanteAmor]

How can the code be changed to paste

Sub Copy()
  Dim sDate As String, sPath As String
  sPath = "S:\Folder1\Folder2\Folder3\"
  sPath = "C:\trabajo\varios\"

  Application.ScreenUpdating = False
  sDate = Format(Sheets("Home").Range("C4").Value, "dd-mm-yyyy")
  Sheets("Temp").Copy
  ActiveSheet.Name = "Dealt"
  ActiveSheet.Cells.Copy
  ActiveSheet.Range("A1").PasteSpecial xlPasteValues
  ActiveWorkbook.SaveAs sPath & "Trade Date " & sDate & ".xlsx"
  ActiveWorkbook.Close False
  Application.ScreenUpdating = True
  '
  'here your code
  '
End Sub

Or put your code here and I adapt it.

 

[Aviles]

Sub Copy()
  Dim sDate As String, sPath As String
  sPath = "S:\Folder1\Folder2\Folder3\"
  sPath = "C:\trabajo\varios\"

  Application.ScreenUpdating = False
  sDate = Format(Sheets("Home").Range("C4").Value, "dd-mm-yyyy")
  Sheets("Temp").Copy
  ActiveSheet.Name = "Dealt"
  ActiveSheet.Cells.Copy
  ActiveSheet.Range("A1").PasteSpecial xlPasteValues
  ActiveWorkbook.SaveAs sPath & "Trade Date " & sDate & ".xlsx"
  ActiveWorkbook.Close False
  Application.ScreenUpdating = True
  '
  'here your code
  '
End Sub

Or put your code here and I adapt it.

Thank you! That worked great.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.