Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

save specific sheet as xlsx



I  try  to save  specific sheet as  xlsx   .so  far  I  don't  succeed . gives "method data of  number are not  found "   in word  path in this  line 

.SaveAs .Path & "\BRANDS" & Format(Date, "DD/MM/YYYY"), 51
Sub s()
Dim ws As Worksheet

    Application.ScreenUpdating = False
   With ThisWorkbook
    With sheet2
Application.DisplayAlerts = False
        .SaveAs .Path & "\BRANDS" & Format(Date, "DD/MM/YYYY"), 51
        Application.DisplayAlerts = True
    End With
   Application.ScreenUpdating = true
    End With
End Sub

any  help guys?



Selected Answer


One problem in your code is that the bold bit 

.SaveAs .Path & "\BRANDS" & Format(Date, "DD/MM/YYYY"), 51

produces a string like "..BRANDS25/07/2022.xlsx" for the filename but "/" is an illegal character in Windows filenames.

In the attached file (with 2 worksheets), I've used this modified line to avoid that:

.SaveAs .Path & "\BRANDS " & Format(Date, "DD-MM-YYYY"), 51

The full code below will save a given sheet to a new file. Note the revised With statement means I need to use .Parent.Path to save the new file to your existing workbook's location. I've made some changes (in bold) and added a couple of comments to help:

Sub saveSheet()
Dim ws As Worksheet, NewWb As Workbook

Set ws = ThisWorkbook.Sheets("sheet2")
'create new file
Set NewWb = Workbooks.Add

Application.ScreenUpdating = False
   With ws
   'copy desired sheet to new file
    .Copy Before:=NewWb.Sheets(1)
    ' save and close new file
    Application.DisplayAlerts = False
    NewWb.SaveAs .Parent.Path & "\BRANDS " & Format(Date, "DD-MM-YYYY"), 51
' restore then close this file
        Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End With
End Sub

You might decide to move .Parent.Close before the line Application.DisplayAlerts = True (if you want to close the first file without saving or the option to do so).

Your line Application.Close will not run (since you closed the file / macro before). You could move it to the Workbook_BeforeClose event macro.

Hope this fixes your problem.



thanks  for your  assistance !
Hasson (rep: 22) Jul 25, '22 at 9:05 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login