VBA Copy Worksheet Then Save As

[andersen_yunan]

Hi All,

I'm currently trying to copy certain worksheet in my workbook and then save it as new excel file in download folder.

Sub RectangleRoundedCorners1_Click()
Dim FName As String
Dim FPath As String

FPath = "C:"
FName = "ResultsLessThan" & Format(Date, "ddmmyy") & ".xlsx"

Sheets("Less Than").Copy
ThisWorkbook.Sheets("Less Than").SaveAs Filename:=FPath & "" & FName
End Sub

I'm using this as the code but it does not excecute as per I wanted. Is there something I did wrong or is there any other option to do this?

Thanks in advance!

 

[dmt32]

Hi,
try this update to your code

Sub RectangleRoundedCorners1_Click()
    Dim FName As String
    Dim FPath As String
    
    FPath = "C:"
    FName = "ResultsLessThan" & Format(Date, "ddmmyy") & ".xlsx"
    
    Sheets("Less Than").Copy
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=51
End Sub

Dave

 

[andersen_yunan]

Hi,
try this update to your code

Sub RectangleRoundedCorners1_Click()
    Dim FName As String
    Dim FPath As String
    
    FPath = "C:"
    FName = "ResultsLessThan" & Format(Date, "ddmmyy") & ".xlsx"
    
    Sheets("Less Than").Copy
    ActiveWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=51
End Sub

Dave

Hi Dave,
Thanks for the response! This works perfectly, question though why would the FileFormat:=51 needed?

 

[dmt32]

Hi Dave,
Thanks for the response! This works perfectly, question though why would the FileFormat:=51 needed?

Hi,
glad resolved your issue.

When using SaveAs you should specify the FileFormat required in this case, you are saving file in xlsx format (no macros) fileformat name [FONT=&quot]xlOpenXMLWorkbook[/FONT] or value 51

You can read guide here: http://codevba.com/excel/workbook_saveas.htm#.XSwhWOtKjIV

Choices of File Format enumeration: https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat


Dave

 

[andersen_yunan]

Hi,
glad resolved your issue.

When using SaveAs you should specify the FileFormat required in this case, you are saving file in xlsx format (no macros) fileformat name xlOpenXMLWorkbook or value 51

You can read guide here: http://codevba.com/excel/workbook_saveas.htm#.XSwhWOtKjIV

Choices of File Format enumeration: https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat


Dave

Got it! Thanks for the help!

 

[andersen_yunan]

Hi,
glad resolved your issue.

When using SaveAs you should specify the FileFormat required in this case, you are saving file in xlsx format (no macros) fileformat name xlOpenXMLWorkbook or value 51

You can read guide here: http://codevba.com/excel/workbook_saveas.htm#.XSwhWOtKjIV

Choices of File Format enumeration: https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat


Dave

Hi Dave,

One more question though, I am copying a worksheet in which I protected it. Can I save it as non-protected when copying it into a new workbook? In addition to that I also realize that the worksheet cannot be hide when running the VBA, in which I want to hide it. Is there any way to solve this?

 

[dmt32]

Hi Dave,

One more question though, I am copying a worksheet in which I protected it. Can I save it as non-protected when copying it into a new workbook? In addition to that I also realize that the worksheet cannot be hide when running the VBA, in which I want to hide it. Is there any way to solve this?

I counted two questions

Untested but maybe following update will do what you want

Sub RectangleRoundedCorners1_Click()
    Dim FName As String, FPath As String
    Dim ws As Worksheet
    
    Const MyPassword As String = "YOUR PASSWORD HERE"
    
    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Less Than")
    
    FName = "ResultsLessThan" & Format(Date, "ddmmyy") & ".xlsx"
    
    With ws
        .Visible = xlSheetVisible
        .Unprotect Password:=MyPassword
        .Copy
        .Visible = xlSheetHidden
        .Protect Password:=MyPassword
    End With
    
    With ActiveWorkbook
        .SaveAs Filename:=FPath & "\" & FName, FileFormat:=51
        .Close False
    End With
    
    Application.ScreenUpdating = True
End Sub

Enter you worksheet password where shown in red as required

Dave