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.)
hello
I try making simple code to shows the sheets names in messagebox at once ,
but gives mismatch in this line .
MsgBox ws.Name & vbLf & vbLf & Join(ws, vbLf)
Sub ms()
Dim ws As Variant
Set ws = Sheets(Array("sheet1", "sheet2"))
For Each ws In Sheets
MsgBox ws.Name & vbLf & vbLf & Join(ws, vbLf)
Next
End Sub
any idea to solve it please?
Leopard
The problem is that you're trying to use the Join function on an array and a control character.
Try this instead to get all sheet names...
Sub AllSheetNames()
Dim ShNms As String, Sh As Worksheet
For Each Sh In ThisWorkbook.Sheets 'loop through sheets in this workbook
ShNms = ShNms & Sh.Name & vbLf & vbLf 'gather names
Next Sh
MsgBox ShNms ' advise user
End Subor this to report a few sheets (using the ordinal references rather than specifying the names you want!):
Sub SomeSheetNames()
Dim ShNms As String, Sh As Worksheet
For Each Sh In Sheets(Array(1, 2)) 'loop through specified sheets in this workbook
ShNms = ShNms & Sh.Name & vbLf & vbLf 'gather names
Next Sh
MsgBox ShNms ' advise user
End SubHope this helps.