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

access path error when save file

0

Hello

I'm  so  confuesd  . this  code  works  for  me , but  when  in  send  by  email  to  another  employee  to  work .   it  gives  error access path  in  this  line 

ws.Copy
Sub copysheet()
    Dim ws As Worksheet, StrPath As String
    Application.DisplayAlerts = False
    StrPath = ActiveWorkbook.Path & "\"
    Set ws = sheet2
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
            StrPath & " trm " & Format(Date, "MM-yyyy") & ".xlsx", FileFormat:=51
        ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

I  don't  think  because  of  version  almost  very  soon  in  my  PC  2019  but  the  others  employee 2020

can  anybody  guess  where  is  exactly  the  problem,please?

excel
MS Office Version: 2019
posted by: Mussa (rep: 48) on Mon Aug 1, 2022 at 9:59 am
Answer
Discuss

Answers

0
Selected Answer

Mussa

You're referring to a sheet by its codename (which is often Sheet2 anyway) but you might lose that if the sheet is deleted.

Your employee may have deleted that sheet then renamed another sheet to Sheet2 but that won't work. For example, if you create a new workbook with 2 sheets, VB Explorer will show them as 

Sheet1(Sheet1)
Sheet2(Sheet2)

where the bold name is the codename (set by VBA and read-only) and the second name is the worksheet name (which the user can change).

If I delete the original sheet 2, it will show just:

Sheet1(Sheet1)

and if I rename that sheet to "sheet2", it will show: 

Sheet1(sheet2)

but codename Sheet2 will no longer exist (and create an access error).

Suggest instead you try (/modify) the method I showed in a recently Selected Answer here: save specific sheet as xlsx

Hope this helps..

posted by: John_Ru (rep: 6142) on Mon Aug 1, 2022 at 10:43 am
Discuss

Discussion

Hi  John
actually  I  told  to  employee  to   capture the picture  in  his  PC  by  enter VB Explorer   and  it  shows  this Sheet2(SH)  name is inside  the worksheet name SH, but   the  question  is  wahy  doesn't  show  me  error?  anyway I  will rename to Sheet2(Sheet2)
and  i will inform  you  tommorw  what  happens  when  go  to  work
Mussa (rep: 48) Aug 1, '22 at 3:03 pm
Mussa

I don't know why that happens but suggest you try with your employee:
Set ws = Worksheets("SH")
John_Ru (rep: 6142) Aug 1, '22 at 3:17 pm
@John sorry for delaying .
strangely this works based on your last suggestion in discussion.
your suggestion is very useful .
many thanks for your solution .
Mussa (rep: 48) Aug 4, '22 at 7:52 pm
Add to Discussion


Answer the Question

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