Unknown Number of Sheets in Excel

[DBAMJA]

Not sure if this can be done programatically or not but here goes.

I have an excel file that changes from month to month with the number of sheets within the file. Some sheets are added where others are deleted. The people that use this file are looking for a way to summarize a certain cell on each sheet by adding the numbers together. With the changing numbers of sheets, the formula changes every month. Is there a way to accomplish this using VBA and not knowing the total number of sheets or the names of the sheets?

Don't know if that makes sense but your help will be greatly appreciated.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[DBAMJA]

Sorry, forgot to add that we are using Excel 2003.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[SkipVought]

hi,

Is there a way to accomplish this using VBA and not knowing the total number of sheets or the names of the sheets?

YES!

msgbox ThisWorkbook.Sheets.Count

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, post your code where you need help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DBAMJA]

Thanks, I will see what I can do with that. I haven't started writing any code yet because I don't even know where to begin with this.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[xlbo]

Where to begin would probably be to stop adding / removing sheets and to collate the data into 1 sheet that has appropriate tags for things like months / years or whatever else the data is split up by on its seperate sheets.

Individual sheets could be kept for reference but the most common mistake everyone makes when using excel is to think of the report and the data as the same entity. I can pretty much guarentee that it will be easier and more future proof to spend time on a routine to collate your data into a single sheet then report off that than it will be to put the effort into building a reporting solution over the top of an ever changing feast...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hear, hear, Geoff!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DBAMJA]

Geoff:

I would completely agree with you. The problem is, this is to track information on patients and each patient has their own tab. I was able to work it out. It may not be pretty but it works. Here is what I came up with:


Private Sub CountDays()
Dim counter As Integer, temp As String
Dim capdt As Single
Dim capdc As Single
Dim ccpdt As Single
Dim ccpdc As Single
Dim homet As Single
Dim homehemo As Single
Dim incenter As Single
Dim inpatient As Single


counter = 1
capdt = 0
capdc = 0
ccpdt = 0
ccpdc = 0
homet = 0
homehemo = 0
incenter = 0
inpatient = 0
Worksheets("Day Summary").Range("D2") = 0
Worksheets("Day Summary").Range("D3") = 0
Worksheets("Day Summary").Range("D4") = 0
Worksheets("Day Summary").Range("D5") = 0
Worksheets("Day Summary").Range("D6") = 0
Worksheets("Day Summary").Range("D7") = 0
Worksheets("Day Summary").Range("D8") = 0
Worksheets("Day Summary").Range("D9") = 0


Do While counter <= ThisWorkbook.Sheets.Count
Debug.Print counter
temp = Sheets(counter).Name
Debug.Print temp
If temp = "cover" Then
Debug.Print "Skipping cover"
ElseIf temp = "epo rec" Then
Debug.Print "Skipping epo rec"
ElseIf temp = "aranesp rec" Then
Debug.Print "Skipping aranesp rec"
ElseIf temp = "Day Summary" Then
Debug.Print "Skipping Day Summary"
ElseIf temp = "home log established patient" Then
Debug.Print "Skipping home log established patient"
ElseIf temp = "Home log new patient (detail)" Then
Debug.Print "Skipping Home log new patient (detail)"
ElseIf temp = "Home log established pt(detail)" Then
Debug.Print "Skipping Home log established pt(detail)"
Else
capdt = capdt + Worksheets(temp).Range("D43")
capdc = capdc + Worksheets(temp).Range("D44")
ccpdt = ccpdt + Worksheets(temp).Range("D45")
ccpdc = ccpdc + Worksheets(temp).Range("D46")
homet = homet + Worksheets(temp).Range("D47")
homehemo = homehemo + Worksheets(temp).Range("D48")
incenter = incenter + Worksheets(temp).Range("D49")
inpatient = inpatient + Worksheets(temp).Range("D50")

End If
counter = counter + 1
Loop
Worksheets("Day Summary").Range("D2") = capdt
Worksheets("Day Summary").Range("D3") = capdc
Worksheets("Day Summary").Range("D4") = ccpdt
Worksheets("Day Summary").Range("D5") = ccpdc
Worksheets("Day Summary").Range("D6") = homet
Worksheets("Day Summary").Range("D7") = homehemo
Worksheets("Day Summary").Range("D8") = incenter
Worksheets("Day Summary").Range("D9") = inpatient






End Sub


Like I said, not pretty but it works.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[xlbo]

Each patient may have their own tab but that shouldn;t mean you can;t take the information from that tab and consolidate it onto one sheet with a reference for the patient number / name - you could also have an inclusion flag against each patient record to determine whether the data forms part of the summay report

That would then make reporting a breeze!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Good stuff for figuring out how to make it work for you though

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DBAMJA]

Thanks Geoff. This excel file was developed by a user in the department. I am, by trade, a database programmer and this excel file is a perfect example of why a database should be used to track this information but no one wants to allow me to convert it to a database. They would rather try to patch what they have than develop a proper application. Which is fine. I have plenty of other projects to keep myself busy.

Sorry about the rant. This place just really pisses me off at times.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[xlbo]

aaaaah - preaching to the converted then!!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question