I'm currently getting very frustrated with what I expect will be a very simple issue to resolve. Would someone please look at the code below and offer any advice?
I have a group of macros set up to run through a source workbook, split in to 27 seperate sheets, format each one, then export to a master copy. Everything works fine and it does what I want, however, I'm having difficulty in exiting the code when a specific condition is met. Each created worksheet will have a specific name taken from a cell in the sheet, however, the macro will simply not end when it gets to the final worksheet, it comes up with a run time error. It's not a huge issue as everything runs fine, I just want the error message to not show up. The code for the "broken" section is below.
The issue I have is towards the end with ActiveSheet.Next.Activate. It runs perfectly well, however, when it gets to the final sheet, it won't stop. I've tried naming the final sheet to get it to stop then, no joy. I found and modified the sub Test1() and still no joy, tried naming the last sheet again and exiting, ending, killing, I can't work this out..
All I want it to do is run through each worksheet, export it to the template file, save close and move on to the next one until there are no more sheets. I suspect this is incredibly easy to do, I can't figure it out.
Sub Export() Dim wbTarget As Workbook 'Template File Dim wbThis As Workbook 'Source file Set wbThis = ActiveWorkbook strName = ActiveSheet.Name Set wbTarget = Workbooks.Open("C:\users\desktop\2019 Template.xlsx") wbThis.Activate Application.CutCopyMode = False Range("A:AY").Copy 'paste to master wbTarget.Activate Range("A1").PasteSpecial Application.CutCopyMode = False wbTarget.SaveAs Filename:="c:\users\desktop\Final\2019 Master" & Range("D2") & ".xlsx" wbTarget.Close wbThis.Activate ActiveSheet.Next.Activate 'Call Test1 Set wbTarget = Nothing Set wbThis = Nothing Call Export End Sub Sub Test1() On Error Resume Next Sheets(ActiveSheet.Index + 1).Activate If Err.Number <> 28 Then Exit Sub End Sub
As I said, it's not a vital issue, the code works and does what I want, I just don't want it to have to runtime error when it gets to the last sheet. Thank you in advance!