Forum Discussion
Solved
Excel VBA - If Sheet Name IsNumeric Loop - False error message
Morning All,
Need some help on a loop process to take action only on sheets in a workbook where the sheet name is numeric. The number of numerically named sheets will vary month to month based on how many employees a team has, and each sheet is named with the employee id.
The current loping to update all employee sheets works until it finds worksheets with non-numeric names, then just throws a message box of "False". I want to avoid the false message box as it provides no value, and will confuse the users.
Current VBA screenshot below :
Aargh - I should have seen it - my apologies. The line
MsgBox sTemp = "Refreshed Scoring Template and Formulas for the following worksheets"
should be
MsgBox "Refreshed Scoring Template and Formulas"
Or perhaps you wanted something like this:
Sub ClearUserSheets() Dim WBK As Workbook Dim WKS As Worksheet Dim i As Integer Dim sFolder As String Dim sTemp As String 'Close and save other workbooks For Each WBK In Application.Workbooks If Not (WBK Is Application.ThisWorkbook) Then WBK.Close SaveChanges:=True End If Next WBK 'Select Template area to copy to user sheets ThisWorkbook.Worksheets("Template").Activate Range("A9:H19").Copy 'Loop Through User Sheets, hide non user sheets and Paste Refreshed Template For Each WKS In ThisWorkbook.Worksheets If IsNumeric(WKS.Name) Then With WKS .Range("A9:H19").PasteSpecial xlPasteAll sTemp = sTemp & vbCrLf & .Name End With End If Next WKS 'End and notify user of completed tasks/tabs MsgBox "Refreshed Scoring Template and Formulas for the following worksheets:" & sTemp End Sub
I don't see where this code would produce a message box that says "False".
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
Attaching a dummy copy here. As this copy lays out there will always be several non-numerically named worksheet tabs. In the real world, there will be 3-5 tabs with text names, and upwards of 15-20 numerically named tabs. Any assistance is appreciated, as always!Aargh - I should have seen it - my apologies. The line
MsgBox sTemp = "Refreshed Scoring Template and Formulas for the following worksheets"
should be
MsgBox "Refreshed Scoring Template and Formulas"
Or perhaps you wanted something like this:
Sub ClearUserSheets() Dim WBK As Workbook Dim WKS As Worksheet Dim i As Integer Dim sFolder As String Dim sTemp As String 'Close and save other workbooks For Each WBK In Application.Workbooks If Not (WBK Is Application.ThisWorkbook) Then WBK.Close SaveChanges:=True End If Next WBK 'Select Template area to copy to user sheets ThisWorkbook.Worksheets("Template").Activate Range("A9:H19").Copy 'Loop Through User Sheets, hide non user sheets and Paste Refreshed Template For Each WKS In ThisWorkbook.Worksheets If IsNumeric(WKS.Name) Then With WKS .Range("A9:H19").PasteSpecial xlPasteAll sTemp = sTemp & vbCrLf & .Name End With End If Next WKS 'End and notify user of completed tasks/tabs MsgBox "Refreshed Scoring Template and Formulas for the following worksheets:" & sTemp End Sub
- Hey Hans! I'll try to dummy one up, but it would be a bit of work. From my troubleshooting it looks like "False" message box pops up when the "If IsNumeric" encounters the worksheets which are actually named by text, and not number. It currently loops through all of the numeric tabs, making the needed updates perfectly, but then after updates throws the "False" pop up.
-Joe
