-
Consolidate Spreadsheets into 1
Greetings!
I have some questions related to some information I need to know to create a VBA script ...
- How can I know the number of worksheets in a workbook using VBA?
- IS it possible that in addition to know the number of worksheets, I can also know the name of this worksheets using VBA?
- If I have 30 worksheets, should I create an array (for the accounts number listed in the worksheets) for each worksheet?
Thank you very much!
Eduardo
-
Try this code:
[VBA]Sub a()
Dim shtSheet As Worksheet
Dim strSheetNames As String
i = 1
For Each shtSheet In ActiveWorkbook.Worksheets
' this line will write sheet name into cell
Cells(i, 1).Value = shtSheet.Name
' this will make a string listing all sheet names
strSheetNames = strSheetNames & shtSheet.Name & Chr$(13)
' this will count your sheets
i = i + 1
Next shtSheet
strSheetNames = "Yo have " & i - 1 & " worksheets named as follow:" & Chr$(13) & strSheetNames
MsgBox strSheetNames
End Sub[/VBA]
-
Thanks
Thanks a lot for the information. Is there a way that I can give you points or mark this question as answered?
Thanks,
Eduardo
-
Hi you can do it using Thread Tools or Rate Thread in menu located just above first post.
-
Edoloto,
You can get the number of worksheets in your workbook without iterating by using this code:[vba]numSheets = Worksheets.Count[/vba]
Here is a variation on the solution to your query:[vba]Sub b()
Dim shtSheet As Worksheet
Dim strSheetNames As String
For Each shtSheet In ActiveWorkbook.Worksheets
strSheetNames = strSheetNames & shtSheet.Name & Chr$(13)
Next shtSheet
MsgBox "You have " & Worksheets.Count & " worksheets named as follow:" & Chr$(13) & strSheetNames
End Sub
[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules