How to save a worksheet as a workbook in Excel VBA
Sub WorksheetAsWorkbook(WS As Worksheet)
Dim WB As Workbook
Dim strPath
WS.Range("A1:AZ5000").Copy
Set WB = Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
strPath = ThisWorkbook.Path & "\SavedSheetAsWorkbookFiles\" & WS.Name & "_" & Format(Date, "YYYYMMDD") & Hour(Now()) & Minute(Now()) & Second(Now) & ".xlsx"
WB.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
WB.Close True
End Sub
Sub WorksheetAsWorkbook(WS As Worksheet)
Dim WB As Workbook
Dim strPath
WS.Range("A1:AZ5000").Copy
Set WB = Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
strPath = ThisWorkbook.Path & "\SavedSheetAsWorkbookFiles\" & WS.Name & "_" & Format(Date, "YYYYMMDD") & Hour(Now()) & Minute(Now()) & Second(Now) & ".xlsx"
WB.SaveAs Filename:=strPath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
WB.Close True
End Sub
No comments:
Post a Comment