Export Current Worksheet - not the entire workbook

[scotthannaford1973]

hi

I have some simple VB that will back up an entire workbook to a specific location, with a file name that includes the time and date of saving... that's all good... but I'd like to add a button to a number of worksheets that will allow for that single sheet to be exported to a specific location (say H:/ ) and include the worksheet name, date and time in the file name.

As I need this button to be on multiple sheets, so any of them can be exported individually, I assume it's possible to create the VBA with ActiveSheet built in.

Guidance, hugely appreciated! So - to summarise:

1. export current, active sheet
2. to a specific folder H:/
3. with worksheet name, date and time

and in the spirit of sharing, this is the code i use for backing up the entire workbook:

Sub SaveToArchive() Dim savedate savedate = Date Dim savetime savetime = Time Dim formattime As String formattime = Format(savetime, "hh.MM") Dim formatdate As String formatdate = Format(savedate, "YYYY.MM.DD") Application.DisplayAlerts = False Dim backupfolder As String backupfolder = "M:\Acute\ICT\Digital Programme and Projects\00 - Projects and Workstreams\003 - Roadmap Resourcing and Scheduling\000 - Archive\" ActiveWorkbook.SaveCopyAs Filename:=backupfolder & formatdate & " " & formattime & " " & ActiveWorkbook.Name ActiveWorkbook.Save Application.DisplayAlerts = True MsgBox "The file has been backed up to " & backupfolder & " - check folder if needed!" End Sub

 

[BSALV]

    backupfolder = "M:\Acute\ICT\Digital Programme and Projects\00 - Projects and Workstreams\003 - Roadmap Resourcing and Scheduling\000 - Archive\"
     ActiveSheet.Copy     'or .move
     ActiveWorkbook.SaveAs Filename:=backupfoler & Format(Now, "yyyymmdd hhmmss ") & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[scotthannaford1973]

Hi

I have used your code to amend the backup folder / file name etc as below - but it still saves with the workbook name, rather than the sheet... getting closer though!




Sub ExportCurrentSheet()

Dim savedate

savedate = Date

Dim savetime
savetime = Time
Dim formattime As String
formattime = Format(savetime, "hh.MM")
Dim formatdate As String
formatdate = Format(savedate, "YYYY.MM.DD")

Application.DisplayAlerts = False

Dim backupfolder As String
backupfolder = "H:\"
ActiveSheet.Copy 'or .move
ActiveWorkbook.SaveAs Filename:=backupfolder & Format(Now, "yyyymmdd hhmmss ") & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
MsgBox "The file has been backed up to " & backupfolder & " - check folder if needed!"

End Sub

 

[BSALV]

ActiveSheet.Copy 'or .move
ActiveWorkbook.SaveAs Filename:=backupfolder & Format(Now, "yyyymmdd hhmmss ") & ActiveSheet.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[scotthannaford1973]

ActiveSheet.Copy 'or .move
ActiveWorkbook.SaveAs Filename:=backupfolder & Format(Now, "yyyymmdd hhmmss ") & ActiveSheet.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

perfect - thanks!