Hello,
I've been trying to combine some examples of the SaveAs techniques, but haven't been successful, and would greatly appreciate some help.
I'm trying to save a copy of the a worksheet only (specific Sheet9 "Report" if ActiveSheet is not possible)
- as a formatted file without macros in it (.xlsx)
- giving a default filename
- defaulting to the path of the currently open workbook
- allowing the user to select a folder
- suppressing any warnings/alerts about removing VBA
- leaving the new workbook open (and keeping the old one open in the backgroud.
This does exactly that for PDF:
But for saving as .xlsx what I have so far is below, but it seems to be stuck with my attempt to block alerts, and never gets round to saving the file.
Could somebody point me in the right direct please?
I've been trying to combine some examples of the SaveAs techniques, but haven't been successful, and would greatly appreciate some help.
I'm trying to save a copy of the a worksheet only (specific Sheet9 "Report" if ActiveSheet is not possible)
- as a formatted file without macros in it (.xlsx)
- giving a default filename
- defaulting to the path of the currently open workbook
- allowing the user to select a folder
- suppressing any warnings/alerts about removing VBA
- leaving the new workbook open (and keeping the old one open in the backgroud.
This does exactly that for PDF:
VBA Code:
Private Sub SaveReportAsPDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("B1"), Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
But for saving as .xlsx what I have so far is below, but it seems to be stuck with my attempt to block alerts, and never gets round to saving the file.
VBA Code:
Sub SaveActiveSheetasXLSXWithoutVBA()
Dim NewWb As Workbook
Dim fname As Variant
fname = Application.GetSaveAsFilename(InitialFileName:=Range("B1"), filefilter:= _
" Excel Macro Free Workbook (*.xlsx), *.xlsx,")
'Set FileFormat to .xlsx
FileFormatValue = 51
'Copies the ActiveSheet to new workbook
ActiveSheet.Copy
Set NewWb = ActiveWorkbook
Application.DisplayAlerts = False
'Save the file
NewWb.SaveAs fname, FileFormat:= _
FileFormatValue, CreateBackup:=False
NewWb.Close False
Set NewWb = Nothing
Application.DisplayAlerts = True
End Sub
Could somebody point me in the right direct please?