Consolidate Spreadsheets into 1

[Edoloto]

Greetings!

I have some questions related to some information I need to know to create a VBA script ...

1. How can I know the number of worksheets in a workbook using VBA?
2. IS it possible that in addition to know the number of worksheets, I can also know the name of this worksheets using VBA?
3. 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

[MaximS]

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]

[Edoloto]

Thanks a lot for the information. Is there a way that I can give you points or mark this question as answered?

Thanks,

Eduardo

[MaximS]

Hi you can do it using Thread Tools or Rate Thread in menu located just above first post.

[RonMcK]

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]