Hello all,
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 SubAs 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!