Summary Page code help

[HH]

I found the following module during a a help search. I don't know "Maniac"
but the code works great.
I am having a problem remembering to exclude any worksheets that I do not
want to process. If anyone else adds a new worksheet to the workbook they
don't know how to exclude it. As you can see I already have 7 worksheets
excluded.

I am thinking of adding two new worksheets (named A & Z). Between these two
worksheets would be all worksheets that I do want processed.

I need help with the new code that would process only worksheets between
Worksheet A and Worksheet Z. Then I would only have to exclude the starting
and ending worksheets.

Does that make sense - and is it possible?.

If Maniac is out there - thanks for your help! If you or anyone else could
help writing new code, I would appreciate it.

Hank

Sub SignOutLog()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
Sheets("SignOutLog").Select
' Clear the existing values (if any)
Range("$A$2:$m$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
For I = 2 To Sheets.Count
a$ = Sheets(I).Name
' Don't process a sheet if its name Other than "SignOutLog"
' or if the name is blank.
If (a$ = "Birthday") Then GoTo 10
If (a$ = "DepositRecord") Then GoTo 10
If (a$ = "MailLabels") Then GoTo 10
If (a$ = "PmtSummary") Then GoTo 10
If (a$ = "Templat") Then GoTo 10
If (a$ = "ID") Then GoTo 10
If (a$ = "SignOutLog") Then GoTo 10
If (Sheets(a$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("c" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C3"
Range("d" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C4"
Range("e" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C14"
Range("a" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R9C9"
J = J + 1
10 Next I
End Sub

 

[Gary Keramidas]

maybe you can use something like this:

Sub SignOutLog()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'
Dim shtarr As Variant
'
Sheets("SignOutLog").Select
' Clear the existing values (if any)
Range("$A$2:$m$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
shtarr = Array("Sheet1", "Sheet2", "Sheet3")
j = 2
For i = LBound(shtarr) To UBound(shtarr)
a$ = Sheets(shtarr(i)).Name
'
If (Sheets(shtarr(i)).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
With Sheets(shtarr(i))
.Range("c" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C3"
.Range("d" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C4"
.Range("e" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C14"
.Range("a" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R9C9"
j = j + 1
End With
10 Next i
End Sub

 

[HH]

I have inserted worksheets A & Z. Between them are all the worksheets to be
processed. When I run the code I get ERROR- "Runtime error '9': Subscript
out of range
highlighet in yellow is: A$ = Sheets(shtarr(i)).Name

Did I do something wrong?

 

[Gary Keramidas]

replace these sheet names with the names of the sheets you want to run your
code on

shtarr = Array("Sheet1", "Sheet2", "Sheet3")

then try it

 

[HH]

Thanks Gary, I understand now.
But that's what I am trying to avoid. If someone adds another sheet we need
to add the sheet name to the array IF we want it processed.
The other way we had to add the sheet name IF we did NOT want it
processed -- about the same thing.

My point is to add sheets I want processed between Sheet A and Sheet B. If
I do not want them processed, I would add them outside this area.

That way the code would not have to be altered.
I just can't come up with the code to get it done.

 

[HH]

Anyone else got an idea how this can be done?

Thanks Gary, I understand now.
But that's what I am trying to avoid. If someone adds another sheet we
need to add the sheet name to the array IF we want it processed.
The other way we had to add the sheet name IF we did NOT want it
processed -- about the same thing.

My point is to add sheets I want processed between Sheet A and Sheet B.
If I do not want them processed, I would add them outside this area.

That way the code would not have to be altered.
I just can't come up with the code to get it done.

 

[Gary Keramidas]

use the index number. if sheet A is the 3rd sheet and Sheet B is the 10th sheet:

for i 3 to 10
worksheets(i).select
next

or to allow for adding sheets in between sheet A and sheet B
for i = 3 to worksheets("Sheet B").index
'do this
next

 

[HH]

Thanks for trying Gary,

I have no way of knowing how many sheets will be in front of A. Today is is
the 8th sheet. I also don't know how many sheets might be between A and Z.
Today there are 20 One sheet is added for each person enrolled.. Likely
there will be 60 to 80 sheets within a year.
Maybe there is no answer except what I said in the begining - a database
would be better. Just do no have that option right now.

 

[Gary Keramidas]

so then try this

Sub test()
Dim i As Long
For i = Worksheets("Sheet A").Index To Worksheets("Sheet b").Index
'do something
Worksheets(i).Activate
Next
End Sub

 

[HH]

Sounds good,
See where I went wrong. This is the new code and it won't process.

Sub SignOutLog()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
Sheets("SignOutLog").Select
' Clear the existing values (if any)
Range("$A$2:$m$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
Dim i As Long
For i = Worksheets("A").Index To Worksheets("b").Index
' Don't process a sheet if its name Other than "SignOutLog"
' or if the name is blank.
Worksheets(i).Activate
Range("c" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C3"
Range("d" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C4"
Range("e" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R6C14"
Range("a" + Format(J)).FormulaR1C1 = "='" + a$ + "'!R9C9"
J = J + 1
10 Next i
End Sub

 

[Gary Keramidas]

what are you trying to do here?
Range("c" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C3"

what is the format(j))?
what is a$

an explanation would help.

 

[HH]

That is the location of the information we are pulling form the processed
worksheets. R6C3 ( Row6Column3) means C6.

What you are asking about is part of the origional code by "Maniac" I don't
have a big knowledge of code.
I don't pretend to understand how it works - but it does.