PDA

View Full Version : [SOLVED] Sheet names in an array



hilton
01-23-2007, 04:28 AM
Hello, all.

This looks like a great place to be, and I can see me spending many a pleasant hour learning what the bloomin questions are, never mind the answers!!.

Maybe someone can point me in the right direction in regard to my current prob.

I have a workbook Master.xls, with one worksheet, named EXAMPLE.

Every month (or whatever) I will receive a bunch of workbooks, Data1, Data2, etc,each with the same number of sheets, but the number of sheets and the sheet names will vary each month.

What I want to be able to do is toopen any one of these monthly wkbooks, collect the names of the sheets, then create in MAster.xls as many copies of sheet EXAMPLE as there are in Data1, and named withe names of the sheets in Data1.

I don't know much about arays, but it seems that passing the sheet names into an array is the way forward, but I've no Idea where to start. Any help would be grately appreciated.

Rgds

OBP
01-23-2007, 05:11 AM
You could either put them in to an array or directly in to the worksheet's cells.
Do you know how to find the Sheet's names using the Sheets collection?

Bob Phillips
01-23-2007, 05:14 AM
Hi hilton,

Welcome to VBAX, you should enjoy it here.

BTW, I didn't know BC was still part of the UK, I thought we had gotten rid of those darn Canucks :rotlaugh:.

To your question. This code will open the other workbook and create every sheet in you current workbook, then close it. It doesn't copy the data over, as that is how I read your requirement.


Dim sFile
Dim oWB As Workbook
Dim oWS As Worksheet
With ActiveWorkbook
sFile = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If sFile <> False Then
Set oWB = Workbooks.Open(sFile)
For Each oWS In oWB.Worksheets
.Worksheets.Add(after:=.Worksheets(.Worksheets.Count)).Name = oWS.Name
Next oWS
oWB.Close savechanges:=False
End If
End With

hilton
01-24-2007, 01:19 AM
Thanks for the quick reply.

It's the array bit that's holding me up. I don't know how to loop through the sheets and assign their names to an array.

XLD solution is alomost what I want, but I only need the sheet names. I have some code that will then gnerate copies of the EXAMPLE sheets an give them the names of the sehhets in the DATA file.

Bob Phillips
01-24-2007, 02:43 AM
Where do you wnat them, in a VBA array, in a worksheet?

hilton
01-24-2007, 03:04 AM
Well, I'm actually trying to do two things here:
1) provide a solution for my client (fairly important, but short term, and I can do that by a long-winded process) and
2) more importantly, learn how to work with arrays. If i dim an array with a list of sheetnames I know how to extract them from the array and deal with them. It's creating a dynamic array to populate with an unknown number of elements that I can't get to grips with.

BTW I'm not actually in BC, don't know how tah happened, but we have got a couple of inches of snow over here in Chingford.

Thanks

moa
01-24-2007, 03:23 AM
Dim loopInt As Integer
Dim anArray() As Variant
ReDim anArray(Worksheets.Count)
For loopInt = 1 To Worksheets.Count
anArray(loopInt) = Worksheets(loopInt).Name
Next

This will put the sheet names into an array. You have to redimension the array with the count of worksheets. Don't have time to slot it into your code but I hope that helps.

Bob Phillips
01-24-2007, 05:24 AM
Dim oWB As Workbook
Dim oWS As Worksheet
Dim arySheets
Dim sFile
Dim iSheet As Long
With ActiveWorkbook
sFile = Application.GetOpenFilename("Excel files (*.xls), *.xls")
If sFile <> False Then
Set oWB = Workbooks.Open(sFile)
ReDim arySheets(oWB.Worksheets.Count)
For Each oWS In oWB.Worksheets
arySheets(iSheet) = oWS.Name
iSheet = iSheet + 1
Next oWS
oWB.Close savechanges:=False
End If
End With


To get an array item, just index into it, starting at 0,

MsgBox arySheets(5)

gets the 6th item.

hilton
01-26-2007, 12:21 AM
Thanks very much, everyone.

All the code examples you have provided are going to be put to good use.

This a really fantastic place!