CODEWITHSUNDEEP
vbaexcel
Irene
Irene

Reputation: 53

Check the name of the last sheet in my workbook and if it has a certain name then break the macro

I want to have a code for error handling that checks the name of the last sheet and if the sheet has a certain name then it should break the rest of my code.

Set ws = ThisWorkbook.Sheets(Sheets.Count)
MsgBox ws.Index '& "-" & ws.Name & "-" & ws.CodeName

Here it copies a sheet from another workbook into ThisWorkbook in the end. My last sheet at the moment is called "Legend".

Sheets(Sheet1).COPY After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Here I want it to say that if the last sheet is "Legend" it means that sheet is wrong and the sheet was not copied so break the code. 

If ws.Name Like "*Legend*" Then Exit Sub

But it is not working properly as it break the code anyway regardless if the copy pasting of the sheet was successful. Any suggestions??

Thanks a lot!

Upvotes: 0

Views: 1627

Answers (2)

Variatus
Variatus

Reputation: 14383

This is your code as I understand it:-

Set ws = ThisWorkbook.Sheets(Sheets.Count)
MsgBox ws.Index '& "-" & ws.Name & "-" & ws.CodeName
Sheets(Sheet1).COPY After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
If ws.Name Like "*Legend*" Then Exit Sub

Obviously, you copy the sheet from the other workbook before you ask whether you should copy it. Therefore the copy process can't be stopped by examining the name of the last sheet. The copy has already been made. If you want to stop the copying, ask the question before.

However, the above code will not do anything to the newly copied sheet if the sheet which was last before the new sheet was added is named 'Legend'. The name of the new sheet is not being examined.

Upvotes: 0

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

You are most likely setting ws before the extra sheet is copied, so it doesn't matter how many sheets you add, ws will always be Legend.

Try either:

Set ws = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
If ws.Name Like "*Legend*" Then Exit Sub

Which will recount the number of sheets inclusive of any you have copied.

Or if you need ws to stay as Legend for later use:

If ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name Like "*Legend*" Then Exit Sub

Upvotes: 4

Related Questions