Marco Not Working Properly

[wec43wec]

Note: I had previosuly requested a response in the Micrsoft:Office Forum which I was advised to enter my request in this forum. One person had provided a response as indicated on the last part of this thread.

Now - My issue is as follows:

I have a spreadsheet which I use to monitor data that is entered by users in many different offices each day of the week with a rollup / consolidated sheet for end of week totals.

I have created macros which are used to erase the data at the end of the week so that the file can be used to enter data for the next week, etc. Note: I have create this option to be used on each sheet which has a button to erase data for that day or on the last sheet which has a button to erase all sheets at one time.

Each of these sheets are protected so that that the user can only enter data in a specific cell.

My issue is that I have created a macro to erase data on each sheet as well as I have created a macro that erase each sheet (6 work week days and the consolidated sheet) at one time. When I use the button to erase each sheet at one time - I do not have a problem, however when I click the macro button to erase all sheets at one time I get an error message which I will display below and the section that is higlighted when I click the debug button. I can only guess that my problem is related to how and when I protected the cells (on each individual sheet) before or after I created the last macro that erase "all sheets at one time"

Could this be the issue?

Must I create a macro when the sheets are NOT protected or does it matter?

Lastly - it seems that at times when I test the macro, I do not have a problem, however when I email the macro to another user they have a problem with the macro used to erase all sheets do not work. Could it be that when the file name is changed, the macro does not relate to the new file?


Thanks for any help.


ERROR MESSAGE - READS

Run-time error '1004'

"Master PS Form 3922_3930 (April 2007).xls' could not be found.
Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the file menu, make sure that the file has not been renamed, moved, or deleted.

Debug Display (NOTE - THE FIRST LINE BELOW THAT BEGINS WITH THE WORD "APPLICATION.RUN" IS THE LINE THAT IS HIGHLIGHTED IN YELLOW - WHICH TO ME INDICATES THAT THIS IS THE BEGINNING OF THE ERROR IN THE MACRO".

Sub Clear_ALL_Sheets()
'
' Clear_ALL_Sheets Macro
' Clear ALL SHEETS in WORKBOOK
'
' Keyboard Shortcut: Ctrl+a
'
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_Plan_Data"
Sheets("Input_ Daily_ Actual_Data").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_3930"
Sheets("Fri").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_F"
Sheets("Thu").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_thu"
Sheets("Wed").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_W"
Sheets("Tue").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_T"
Sheets("Mon").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!Clear_M"
Sheets("Sat").Select
Application.Run "'Master PS Form 3922 _ 3930 (April 2007).xls'!clear_sat"
Range("C8").Select
Sheets("Mon").Select
Range("C8").Select
Sheets("Tue").Select
Range("C8").Select
Sheets("Wed").Select
Range("C8").Select
Sheets("Thu").Select
Range("C8").Select
Sheets("Fri").Select
Range("C8").Select
Sheets("Input_ Daily_ Actual_Data").Select
Range("O10").Select
Sheets("Input_ Weekly_Plan").Select
Range("B3").Select
End Sub




Inappropriate post?
If so, Red Flag it!


Check out the FAQ
area for this forum!




Tek-Tips Forums is Member Supported. Click Here to donate.
Gavona (TechnicalUser) 29 Apr 07 17:39
Quote:
Could it be that when the file name is changed, the macro does not relate to the new file?
The file name is indeed hard coded "Master PS Form 3922 _ 3930 (April 2007).xls".

I would have thought simply

CODE
Sub Clear_ALL_Sheets()
Call Clear_Plan_Data
Call Clear_3930
etc
End Sub
would work. I don't think that any of the other rows are doing anything useful.



Gavin


Thank Gavona
for this valuable post!

 

[SkipVought]

Hi,

Are you deleting data in cells that are LOCKED on the protected Sheets? If so, you must first UNPROTECT the sheet and then delect the data in the locked cells.



Skip,

_[red][/red]

 

[SkipVought]

Just a question to satisfy my curiosity.

After putting the time and effort into capturing this data, why delete it? Does it not have some historic value to your concern? Could it not be analyzed in the context of other weeks' data?

Inquiring minds want to know. ;-)

Skip,

_[red][/red]

 

[wec43wec]

Skip - thanks for the reply.

In repsonding to your inquiry

Skip >> Are you deleting data in cells that are LOCKED on the protected Sheets

WEC >> No, not to my knowledge, however I have a number of cells that I possibly could have selected a protected cell - but I do not think so.

Skip >> After putting the time and effort into capturing this data, why delete it?


WEC >> The data is deleted so that the user can rename the file and use it for a second week. The original data is saved under a different name prior to clicking the erase/delete button.

Question - in my original meesage I got some responses that I need to be clear with as follows:

1. When I current a macro it is my understanding that the file name is "hard coded" in excel and will not allow a change to the name of the file. Is this true.

2. Must I unprotect the sheet before or after I generate the macro.? Is there are concern as to when the marco is protected?

3. Can I create a macro that is designed to select other macros within the same wookbook.

 

[wec43wec]

Skip - were you able to look into my issue?

 

[SkipVought]

"1. When I current a macro it is my understanding that the file name is "hard coded" in excel and will not allow a change to the name of the file. Is this true."

The file name is the name of the workbook. If someone changes the file name, that's what it is. What YOU have in your code is HARD CODED.

"2. Must I unprotect the sheet before or after I generate the macro.? Is there are concern as to when the marco is protected?"

Locked cells in the sheet are PROTECTED from changes: user or macro. The Macro is not protected by a protected sheet. You can, however, put password portection on your VBA project.

"3. Can I create a macro that is designed to select other macros within the same wookbook."

Macros can call other macros. Just be sure that your object references are correct and you understand the scope of variables.


Skip,

_[red][/red]