TheRedCardinal
Active Member
- Joined
- Jul 11, 2019
- Messages
- 252
- Office Version
- 365
- 2021
- Platform
- Windows
Hi all,
I am at a loss about how to handle a common occurrence in my latest macro development.
The macro workbook (Wbk1) selects a file from a subfolder, called data, opens it, allocates it to a workbook variable WBk2, and then directly copies a sheet that is either called ICA or ICD from Wbk2 into Wbk1, a simple copy and paste job.
My user is required to prepare Wbk2 in advance by loading in the new data, refreshing the pivot table, then extracting the data from the pivot table for product lines ICA and ICD into separate worksheets, called ICA and ICD. This is the normal double click on the value method of extracting from the pivot table.
Problem is, they often forget, and up until now, this leads to a crash of the macro when it can't find the sheet.
Can I ask for any ideas on how to handle, and code this?
What I would like is for the macro to identify that the sheet it is looking for isn't present; then ideally, could it go on and extract the relevant sheet from the pivot table, rename it, and proceed without any input from the user? But for this I am not sure how to perform the extraction itself, or how to identify the sheet that has just been created so as to rename it.
Or secondly, to effectively pause, and allow the user to create the sheets (with a helpful message saying what is missing) - but how do I allow this? How could I create a button for the user to press once he has fixed the mistake?
Or is there a better way that I've not thought of yet?
I am at a loss about how to handle a common occurrence in my latest macro development.
The macro workbook (Wbk1) selects a file from a subfolder, called data, opens it, allocates it to a workbook variable WBk2, and then directly copies a sheet that is either called ICA or ICD from Wbk2 into Wbk1, a simple copy and paste job.
My user is required to prepare Wbk2 in advance by loading in the new data, refreshing the pivot table, then extracting the data from the pivot table for product lines ICA and ICD into separate worksheets, called ICA and ICD. This is the normal double click on the value method of extracting from the pivot table.
Problem is, they often forget, and up until now, this leads to a crash of the macro when it can't find the sheet.
Can I ask for any ideas on how to handle, and code this?
What I would like is for the macro to identify that the sheet it is looking for isn't present; then ideally, could it go on and extract the relevant sheet from the pivot table, rename it, and proceed without any input from the user? But for this I am not sure how to perform the extraction itself, or how to identify the sheet that has just been created so as to rename it.
Or secondly, to effectively pause, and allow the user to create the sheets (with a helpful message saying what is missing) - but how do I allow this? How could I create a button for the user to press once he has fixed the mistake?
Or is there a better way that I've not thought of yet?