Company I'm working with keeps daily operational data in individual Excel files. I've created coding in another Excel file (I'll call it the log file) which does the following:
- Runs code to extract important data from daily file based on date in file name. Data is in multiple tabs.
- Pastes data into two different tabs in log file.
- Creates charts and additional metrics
All log file tabs are protected (no password) to prevent accidental changes and code unprotects and protects as needed. Similar code executes multiple times without problem but errors when pasting on one particular sheet. Code where problem is:
' gets most recent data for dashboard
Windows(filename3).Activate
Sheets("Production - Mill A & Planer").Select
Range("A1:C29").Select
Selection.Copy
Windows("Consolidated Morning Reports r26.xlsm").Activate
Sheets("dashboard").Select
Call m24_unprotect_sheet
Range("A1").Select
ActiveSheet.Paste '<- PROBLEM IS HERE
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I get an error (Run time error 1004: Paste method of worksheet class failed) and debugger shows it happens at the command ActiveSheet.Paste. If the sheet is unprotected, the code works as intended. The error happens only if the sheet is protected and not if it is unprotected, despite the unprotect sheet code unprotecting the sheet. I can confirm that after the code stops, the sheet is unprotected. If I re-run the code, it will work since I assume the sheet is now unprotected. I have nearly identical code executing on other sheets without this problem.
I've tried protecting and unprotecting at different locations in the code. I also tried changing the property of the cells where the paste should occur to unlocked but did not fix the problem.
