Check if worksheet is before another

[christopher.yust]

Hey everyone,

I'm trying to write a macro that will generate a report of data entered
on various worksheets. I only want to generate the report, however, on
worksheets within a range of worksheets defined by the user. For
instance, if they say worksheet "Week 4" and "Week 8", it would
generate the report by pulling numbers from Week 4, 5, 6, 7, and 8.

I'm assuming that the only way this would even be possible is if the
worksheets are in sequential order. However, given the excel program I
have, it always adds new worksheets to the end so it should be a safe
assumption.

The point of the program is it is essentially a weekly timecard to keep
track of how much I work so I can bill my time appropriately. At the
end of each week, the user can hit a button to generate a new template
for the next week so users can save their time for their records. The
problem, however, it that the file size gets massive by just copying
and adding new spreadsheets each week. Ideally, I would be able to
generate this report would would display the time worked Sun- Saturday
each week (based on the different spreadsheets in the range) and then
I'll delete the original worksheets and give users the option to email
the report to themselves or save it as a seperate file.

Thanks!

 

[Don Guillett]

This could be done with a macro using an inputbox asking for start and
asking for stop or even a formula using indirect. More detail would be
helpful

 

[chris]

Don,

I used an input box to ask the users for the start and stop

First = InputBox("What is the first spreadsheet you want on the
report?", "Report Generator")
First = SheetName(UCase(First))
Last = InputBox("What is the last spreadsheet you want on the report?",
"Report Generator")
Last = SheetName(UCase(Last))

"SheetName" is a function I made that makes sure those are valid sheets
within the workbook and if not, enters a loop that won't end until the
user enters a valid sheet name

Here's where I would use that range though. Currently, I set it up
just to do it for all spreadsheets:
Dim Report as String 'this is the report name entered by the user

For Each ws In Worksheets
If ws.Name <> Report Then
'Sheets(Report).Select
ActiveCell.Offset(r, 0) = ws.Name

For i = 1 To 7
ActiveCell.Offset(r, i) = FindDay(i, ws)
Next
ActiveCell.Offset(r, i) = "=SUM(RC[-7]:RC[-1])"
r = r + 1
End If
Next ws

FindDay is a function I made that pulls the hours worked Sun-Saturday,
and the sum function sums up the total hours worked for the week.

I want it to just go through the range of worksheets specified by the
user, but I couldn't figure out how ot do that in the for loop

Thanks!
Chris

 

[Don Guillett]

try incorporating this idea. Assumes that all sheets are between desired
sheets.

Sub indexsheets()
fs = Sheets("first").Index
'MsgBox fs
ls = Sheets("last").Index
'MsgBox ls
For i = fs To ls
'MsgBox Sheets(i).Name
Next i
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Don,

I used an input box to ask the users for the start and stop

First = InputBox("What is the first spreadsheet you want on the
report?", "Report Generator")
First = SheetName(UCase(First))
Last = InputBox("What is the last spreadsheet you want on the report?",
"Report Generator")
Last = SheetName(UCase(Last))

"SheetName" is a function I made that makes sure those are valid sheets
within the workbook and if not, enters a loop that won't end until the
user enters a valid sheet name

Here's where I would use that range though. Currently, I set it up
just to do it for all spreadsheets:
Dim Report as String 'this is the report name entered by the user

For Each ws In Worksheets
If ws.Name <> Report Then
'Sheets(Report).Select
ActiveCell.Offset(r, 0) = ws.Name

For i = 1 To 7
ActiveCell.Offset(r, i) = FindDay(i, ws)
Next
ActiveCell.Offset(r, i) = "=SUM(RC[-7]:RC[-1])"
r = r + 1
End If
Next ws

FindDay is a function I made that pulls the hours worked Sun-Saturday,
and the sum function sums up the total hours worked for the week.

I want it to just go through the range of worksheets specified by the
user, but I couldn't figure out how ot do that in the for loop

Thanks!
Chris

This could be done with a macro using an inputbox asking for start and
asking for stop or even a formula using indirect. More detail would be
helpful

 

[chris]

Thanks, that works great!

I can't believe I didn't think of using the index feature there. I was
able to just modify some of my code to implement it and it seems to
work great. Thanks again!!

Chris

try incorporating this idea. Assumes that all sheets are between desired
sheets.

Sub indexsheets()
fs = Sheets("first").Index
'MsgBox fs
ls = Sheets("last").Index
'MsgBox ls
For i = fs To ls
'MsgBox Sheets(i).Name
Next i
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

Don,

I used an input box to ask the users for the start and stop

First = InputBox("What is the first spreadsheet you want on the
report?", "Report Generator")
First = SheetName(UCase(First))
Last = InputBox("What is the last spreadsheet you want on the report?",
"Report Generator")
Last = SheetName(UCase(Last))

"SheetName" is a function I made that makes sure those are valid sheets
within the workbook and if not, enters a loop that won't end until the
user enters a valid sheet name

Here's where I would use that range though. Currently, I set it up
just to do it for all spreadsheets:
Dim Report as String 'this is the report name entered by the user

For Each ws In Worksheets
If ws.Name <> Report Then
'Sheets(Report).Select
ActiveCell.Offset(r, 0) = ws.Name

For i = 1 To 7
ActiveCell.Offset(r, i) = FindDay(i, ws)
Next
ActiveCell.Offset(r, i) = "=SUM(RC[-7]:RC[-1])"
r = r + 1
End If
Next ws

FindDay is a function I made that pulls the hours worked Sun-Saturday,
and the sum function sums up the total hours worked for the week.

I want it to just go through the range of worksheets specified by the
user, but I couldn't figure out how ot do that in the for loop

Thanks!
Chris

This could be done with a macro using an inputbox asking for start and
asking for stop or even a formula using indirect. More detail would be
helpful

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hey everyone,

I'm trying to write a macro that will generate a report of data entered
on various worksheets. I only want to generate the report, however, on
worksheets within a range of worksheets defined by the user. For
instance, if they say worksheet "Week 4" and "Week 8", it would
generate the report by pulling numbers from Week 4, 5, 6, 7, and 8.

I'm assuming that the only way this would even be possible is if the
worksheets are in sequential order. However, given the excel program I
have, it always adds new worksheets to the end so it should be a safe
assumption.

The point of the program is it is essentially a weekly timecard to keep
track of how much I work so I can bill my time appropriately. At the
end of each week, the user can hit a button to generate a new template
for the next week so users can save their time for their records. The
problem, however, it that the file size gets massive by just copying
and adding new spreadsheets each week. Ideally, I would be able to
generate this report would would display the time worked Sun- Saturday
each week (based on the different spreadsheets in the range) and then
I'll delete the original worksheets and give users the option to email
the report to themselves or save it as a seperate file.

Thanks!