Run macro on multiple sheets

[dwehner]

I have three individual macros that work separately. I would like to combine the three into one and have them work across multiple worksheets.
I'm not sure how to do this.
I will attach a word document with the 3 macros and could someone show me how to accomplish this task?

The word document is named "Need to loop multiple worksheets"

Thank you.

[Bob Phillips]

Where should it insert missing dates?

What is the minutes column?
An Excel workbook would be far more help than the Word doc!

[dwehner]

I will attach workbook.
There is a date column that is involved and a
min column.

I removed several worksheets just to make it simpler for you to view.
There are approximately 16 names in all.

Thanks for looking at it.

[JBeaucaire]

This one macro does what the other 3 did and will work on all sheets in a workbook except "stats":

Please Login or Register  to view this content.

[dwehner]

Thank you very much. I tried it and it works perfectly, like you already knew it would.

I'm thankful there are persons like yourself willing to help an old broad.

Now I will try and follow instructions to mark this as solved. It is perfect.

Dee

[dwehner]

A new question on same workbook.

I have a column with a list of dates. A second column with numbers.
I need to total the numbers for 7 days in a row, and continue down
the dates overlapping different 7 days. Example.

1/1/10 through 1/7/10 would be the date range then
1/2/10 through 1/8/10 would be the next date range, then
1/3/10 through 1/9/10 and so on. We are trying to find
the date range that has the greatest total, but needs to cover 7 days.

I can create the macro to do this but can I just insert it into the existing code that solved my initial question to this forum. I'm not sure exactly where I would insert it.

Thanks for any help.

[JBeaucaire]

1/1/2010 is a Friday. You want to look for 7 day ranges starting on FRIDAYs or do we really want to look for 7-day ranges starting on Mondays?

I can add a column to insert a formula in your patient sheets that is effectively this formula...in L2:

=IF(WEEKDAY(B2)=2,SUM(D2:D8), "")

Copied down, this gives you 7 day totals.

[dwehner]

It will never be the same. I need it to start on the first seven days listed, and continued from there. Ex. sheet 1 may start on 5/2/10, sheet 2 could start on 5/8/10, sheet 3 could start on 5/4/10 etc.
the macro I did is below, but I don't know the next step to get it to do all sheets.
I'm trying to learn, but I'm not doing such a good job.

Please Login or Register  to view this content.

[JBeaucaire]

Please edit your post above to put code tags around the posted code, as per forum rules. Thanks.

[dwehner]

I've been having trouble logging in, so sorry for not responding sooner.
I'll place tags around code. But I also forgot to mention, that when the 7 days are totaled, it means 7 different days. If there are 3 records dated 5/1/10, they count as one day. So the code needs to cover 7 different consecutive days. The previous excel worksheet uploaded should be self-explanatory. Now for the code:

Please Login or Register  to view this content.

The above was my recording the macro on one worksheet. I don't know how to make it work on all worksheets. Also, can it be inserted in the code you originally sent?

Thank you.

[dwehner]

The code I included in last post, really doesn't work that well.
I must be in a specific cell for it to work.
I'm not sure how to correct it.

[JBeaucaire]

This will add the totals in an empty column to the right of the data as it goes. It properly adds 7-day totals on each line going forward 7 days, even if there are multiple days rows within the range.

Please Login or Register  to view this content.

[dwehner]

J: The code works well except for one little thing!!!!
If the date has more than one record , ex

5/19/10 15
5/19/10 15
5/20/10 30
5/21/10 45
5/21/10 15
5/22/10 15
5/23/10 15
5/24/10 30
5/25/10 15

Then the code should cover from 5/19/10 through 5/25/10 with the total being 195. Then skip down to 5/20/10 and cover the next 7 days, not 7 records. Am I clarifying this clearly?

Otherwise, it's exactly what I need.

[JBeaucaire]

The code I put in actually DID what you said, except it did it on every row...totalled 7 days going forward, so you had a 7-day total starting on every row...I thought you were looking for the highest 7-day period starting on any given day.

If that would work for you, then use the code given above.

If not, if you want 7-day ranges only, then use this:

Please Login or Register  to view this content.

[dwehner]

JB: I'm hesitant to be so picky when you have given me exactly what I asked for and in such a quick manner, however, I know my boss and he is really the picky one.
He doesn't want every row to have a total, but every 7-day span, and I'm thinking the result should be on the same row as the end of each 7 day span. Is that possible?
Again, I'm thankful for your responses.

[JBeaucaire]

Well, let's race on down the road with stuff bosses typically like to see, then.

This will create an obvious table of the data, color the 7-day ranges alternately so they are obvious to the eye, put the totals at the bottom of each range, and bold the totals and the titles. Gotta keep the boss happy.

Please Login or Register  to view this content.

[dwehner]

JB: Please look at new attachment. Select Doe worksheet for
comments.
Otherwise, bells and whistles are superb.
Thanks as always
Dee

[JBeaucaire]

Ah, so we did have it right the first time...totals on every row? This sheet has both macros in it.

Please Login or Register  to view this content.

[dwehner]

JB: did you look at the xls. that has 17.? kb? The one with Doe worksheet?

I don't want total on every row. especially if there are duplicate dates.
Please look at attachment that has 17 kbs.

Thanks

[dwehner]

How do I delete previous attachments so as not to confuse myself or others?

[JBeaucaire]

EDIT...GO ADVANCED...paperclip...manage attachments....delete.

So, you DO want it on every DAY, but not every ROW, just once on the last ROW per day? That's a small tweak:

Please Login or Register  to view this content.

[dwehner]

Jerry, I'm uploading another spreadsheet with comments in the Doe worksheet.
I really do appreciate your expertise, but I'm not sure I'm making myself clear.
The file is called, CMI for Jerry.xls
Thanks

[JBeaucaire]

I see the SUM rows formula I was using was off by one row, fixed now. This also skips the first 6 days and puts the first total in the 7th day, then every day after that.

Please Login or Register  to view this content.

[dwehner]

Jerry: You're brilliant. That's exactly what I wanted.

Thank you so much, and I thought I read someplace to give you kudos but don't see how.

So, if anyone else reads this response, Job well done by JB:

[JBeaucaire]

Thanks. People are talking about the blue scales icon in the upper right corner of each of these posts, by the post #.

[dwehner]

Found the scales. Thanks

[dwehner]

JB:

For some reason, unless I'm missing something, the sum figure isn't always on the correct line. Also, some of the days are not being included. Example. the total is only capturing 6 days, not 7.
On other worksheets, it is totally correct.

So, are you willing to take another look at this, next week?

Question: If we add worksheets after running the macro, can we re-run for new worksheets, or should we always start fresh?

[JBeaucaire]

The macro is designed to run on all sheets. We could add a little "flag" to each sheet once it's completed, or have the macro look for the "7 days" in row1 and if it's there, skip that sheet since it's been run already.

Sure we can look, always easiest if you show examples.

[dwehner]

JB: I'm attaching a zip file. I will let you run the macro to see if it does as expected.
I renamed the worksheets A-N. Some of the sheets are correct.
However, check out "A". When I ran macro, the first 7day was off two rows. Also, check out "J". It was off 4 rows. "K" was off two rows. "M" was also off 4 rows.
Eventually, we will be adding worksheets. {just to let you know, my boss told me I, [you], just saved $$$ for facility.
I appreciate your help.
Dee

[JBeaucaire]

Try this...I know you don't mind I put the author comments back in, yes?

[dwehner]

JB: thank you, it is accurate now. I'll leave comments alone.

There is so much to learn re: what's available in Excel syntax.
How does one know all that is available short of printing off every method for every object?
It's overwhelming!!!

Again, thank you.

Dee

[JBeaucaire]

I think the best way to learn stuff is when you have actual needs/applications, it makes the learning very real.

[dwehner]

JB:
As a continuation of this macro, would it be possible to have the
macro also lookup the highest figure in the column and tell which
cell or 7-day range it is affiliated with?

If you don't have the spreadsheet, I can upload it again. Let me know
And thanks in advance.
dee

[JBeaucaire]

I have the sheet, but I don't have the clear example of what you're after as a result. Post up your sheet again showing what you want to happen.

[dwehner]

JB: Take a look at tab "Stats" for more info.
I am uploading again CMI Charges2.

[JBeaucaire]

Highlighted on the stats page how? Please mockup the desired results. That's what I suggested earlier.

What about multiple rows with the same max value?

[dwehner]

Did you open the recent file I uploaded? Stats page was just for instructions.
As far as multiple rows with same max value, would it be possible to highlight
them? More than likely, multiple rows of max value would be the exception as opposed to the rule. Check out Tab A, row 68
Again, thanks.