Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro to Copy Worksheet

0

Hello Variatus (it's probably one for you as you've helped so much thus far and have got to know my workbook!)

Following the success of my previous 2 questions (with your help), i have progressed my workbook further and its almost there...but I've got stuck again.

I've now turned the last dummy sheet into a template and have got a macro that will create & insert a copy of this sheet just before it. There's a button on each sheet from where this macro can be run.

However, it trips up during the copy process with the sheet name and I can't work out why (I can't even work out where it gets the suggested sheet name from that comes up in the message box!). I've tried telling my CopySheet code to rename the sheet to a name that won't already be present, but to no avail. I'm sure that its something to do with the sheet not being named as a date and therefore not agreeing with the rest of the code that's in there but i don't want it to have a date as this will confuse things with it appearing in the sheet name list on the summary sheet.

I know that the summary sheet is showing errors in the data but that's just because there are no entries on the day sheet.

Please help me with this problem and I think I'm complete :)

Many thanks in advance

excelmacros-vba
MS Office Version: Windows 11 - Office 365
posted by: JonP (rep: 37) on Wed Aug 1, 2018 at 7:48 pm
Answer
Discuss

Answers

1
Selected Answer

Hi Jon,

The new sheet you insert inherits the code from the Template. For some reason this code was triggered, interfering with your intentions. The command to use is Application.EnableEvents = False which does what it says. Enable events again when you need them. When inserting a new sheet, this moment has come when the new sheet's name was determined. The default is Template(2) but I added code to try the date after the last existing date first. The code inserts this at D1 which triggers the change of tab name.

I found that a lot of invalid range names were added during testing and added code to remove them on each activation of the summary sheet. I earlier lamented the constant resetting of hyperlinks to GoTo Data and Monthly Summary and now replaced those links with buttons. Observe that they have been named. You can access the names in the Name Bar when you select the shapes in Edit Mode.

Exciting things were happening on my other screen while I made these changes, and the program has become quite complex now. Should I have overlooked a test (like, if there will be a crash if no new name is found for Template(2)) as a result thereof please point it out to me for fixing.

BTW, all procedures in the attached VBA project with today's date (02 Aug) were either created today or modified.

Edit (02 Aug 2018) ===========================

On second thought I made the Template tab invisible (Hidden). You can unhide it to modify. This is the code in proc InsertNewDaySheet which manages the visibility.

    With Worksheets("Template")
        .Visible = xlSheetVisible
        .Copy Before:=Sheets("Template")
        .Visible = xlSheetHidden
    End With

As you see, the tab is temporarily made visible for copying and then hidden again. You can disable the line  .Visible = xlSheetHidden to keep it visible all the time or change it to  .Visible = xlSheetVeryHidden to make it impossible to unhide it from the worksheet's interface. The Visible property can be set in the Property window of the VBA Editor.

Regards!

posted by: Variatus (rep: 4889) on Wed Aug 1, 2018 at 10:25 pm
Discuss

Discussion

Thanks Variatus, all works perfect :)
JonP (rep: 37) Aug 2, '18 at 1:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login