I'm currently coming across an incredibly annoying error at the moment - hopefully someone can help me to find a solution, as I can't find anything online!
I have a workbook, and on the main worksheet ("Main"), I have a "Create Report" button. Clicking this button then runs a macro, which checks if a "Report" worksheet exists (if it does, it is deleted) and then creates a report (the "Report" worksheet).
When there isn't currently a "Report" worksheet, it generates the report perfectly fine. If I then manually delete the report, and run the macro again, it works perfectly fine.
The problem comes when the report already exists, so the macro has to delete the Report worksheet and then create the report. From putting stop points in the macro code, and stepping through each line with F8, I've discovered that when the Sheets("Report").Delete line is run, it deletes the worksheet as required, but then the code just stops, as though it's reached the end. It doesn't continue on with the next line. The macro effectively finishes running.
Following this, Excel either crashes ("Microsoft Office Excel has encountered a problem and needs to close..."), or doesn't properly operate (e.g. normal functions / formulas in cells don't work - for example, if I was to use the SUM() formula on some numerical cells, it would come up as zero regardless of what the 'true' sum was). And then when I go to close Excel, it gives the above error anyway.
I can't for the life of me work out why I am getting this issue. I have tried countless different methods to try and get round it, for example, a cell with either 1 or 0 in to indicate whether a report already exists, and then the macro checks this cell before attempting to delete the Report worksheet, rather than 'proper' coding to see whether the Reprot worksheet exists.
Below is the code:
The macro checks if "Report" exists - if it does, it is deleted. It then copies "Report template" (which is hidden), and renames the new worksheet from "Report template (2)" to "Report". The report is then generated (for the sake of convenience I have not pasted the rest of the macro, plus it's kinda confidential / work-related, so I would rather not!).
I've even tried using 2 separate buttons - one to check if Report exists and if so deletes it, and one to generate the report. Again, the generate report button works perfectly fine, but the delete report button macro stops running after the .Delete line:
(This macro uses the separate 0/1 cell for indicating whether a report exists or not - but the same issue arises if I do it the 'proper' way as in the previous code.)
Any help would be greatly appreciated.
Bookmarks