CODEWITHSUNDEEP
excelvba
Shanmugam Arumugam
Shanmugam Arumugam

Reputation: 25

Runtime error 1004 on Save (Thisworkbook.save) Works on one machine and not on another

I have a data entry userform and when user saves data to sheet. I want to save the Excel file - to achieve this I use ThisWorkbook.Save and this works like a charm on my machine.

The file is created and saved as a .XLSM file in Excel 2016 (windows 64 bit).

Now when I distribute the same to my peers at office - some machines work fine.

Some machines throw a runtime 1004 error save method of worksheet class failed.

Application.DisplayAlerts = False
Dim xWs As Worksheet

Dim xName As String
xName = "data"
For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> xName Then
            xWs.Visible = False
        End If
Next
ThisWorkbook.Save        '<<<< Runtime 1004 Error on this line
Unload Me
Application.Quit

On checking the file, the data is saved to the sheet but the error throws up.

What could be the potential reason for this?

Just something that came to my mind based on "https://www.rondebruin.nl/win/s5/win001.htm" Users can open my file (type .xlsm) in any version of excel excel 2007 / excel 2003 ? Is that the root cause? If yes what is my way around?

Upvotes: 1

Views: 942

Answers (2)

Andreas
Andreas

Reputation: 23968

I believe the problem is how you distribute the file.
My guess is that you email it to them and they open it from the email?
What happens is the file is in a position where you can't save it.

Ask your colleagues to save as the file on the desktop or documents folder where you know for sure they have full read and write capabilities.
Then open the file.

This has bugged me also a few times. It's easy to forget.

Upvotes: 3

Aiyuni
Aiyuni

Reputation: 810

You want to avoid using ActiveWorkbook. Create a variable to store your workbook(s) and use that variable. Your problem is probably due to the fact that your ActiveWorkbookis pointing to a different workbook than the one you wanted.

Upvotes: 1

Related Questions