Hi all,
I'm currently trying to use a user input to name a duplicate sheet based off a 'template sheet' within the same workbook, then populate cell B11 of the duplicate sheet with the user's input. This seemed to work fine, however, after doing 4 duplicate sheets, some of the new sheets get deleted/renamed and hidden? not sure where i'm going wrong here. Any help would be greatly appreciated! Let me know if you need more information. Thanks
Please Login or Register to view this content.
Hi there,
Without sight of your workbook it's difficult to know exactly what is/is not happening.
When you hide a worksheet, you don't have full control over which worksheet then becomes the active worksheet. In most cases the NEXT (i.e. the next tab on the right-hand side) worksheet becomes active, however if you hide the last (i.e. rightmost) worksheet, the PREVIOUS worksheet becomes active. Something like this might help to explain any inconsistent results you're experiencing.Please Login or Register to view this content.
Hope this helps.
Regards,
Greg M
Hi Greg,
Really appreciate the response! Thanks
I've attached an example copy of the spreadsheet I'm working on. There's two template versions, one I've hidden the template for and removed the visible = xlSheetHidden lines (Template 1) and the other I haven't (Template 2). I hope this helps clarify what is/is not happening. How would I be able to reference the newly created sheet within the code? Ideally I would like the template sheets to be hidden, but not sure if this is possible without using sheet indexing.
Last edited by dylanherri97; 03-06-2024 at 01:24 PM.
Possibly...Please Login or Register to view this content.
Hi again,
Unfortunately there are several basic errors in the code you posted originally, e.g.:
The compiler would have identified these errors if you had placed "Option Explicit" as the first line in the CodeModule - you should ALWAYS do this.Please Login or Register to view this content.
Also, you should not include open-ended "On Error Resume Next" statements - these can produce VERY strange/dangerous results! If you need to use them, you should always restore error handling as soon as possible by means of an "On Error GoTo 0" statement.
See if the attached version of your workbook does what you need - it uses the following code:
Worksheets CAN be copied (as hidden worksheets) when their Visible property is set to "Hidden" - it's only when that property is set to "VeryHidden" that they can't be copied.Please Login or Register to view this content.
Hope this helps - please let me know how you get on.
Regards,
Greg M
Last edited by Greg M; 03-06-2024 at 09:18 PM. Reason: Extra highlighting added
Hi Greg,
Thank you very much and thanks for explaining some of the more egregious errors in my original code, it makes a lot of sense now!
Your updated template seems to work perfectly and is incredibly easy to add new templates etc as well.
Thanks again
Hi again,
Many thanks for your feedback.
You're welcome - glad I was able to help.
Regards,
Greg M
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks