I need help in writing/modifying an EXCEL macro

D

dkv0942

I'm running EXCEL 2002.
I need help in writing/modifying an EXCEL macro. If anyone can point me to a
resource for sample code or examples to help with the following I would
appreciate it.

Every month I download a report from a state agency that I need to reformat,
create charts from and send reminder emails based on some of the data. The
report is downloaded as an EXCEL workbook containing a single worksheet. The
worksheet is preformatted as a printed report. I have no control of this
formatting and I can not get the data as a .csv file. The report contains 3
sections. See detailed report description below.

What I want to do:
1. Is copy the 3rd section of the report to a new worksheet.
2. Sort the new worksheet by name, and date.
3. Filter some names based on resource type.
4. Create a new file, which will be attached to an email to my boss for
follow-up.

Originally I used the built-in EXCEL macro recording function and this
worked for a couple of months. Then the number of rows in the 2nd section
varied and my generated worksheet failed to sort correctly, and I lost the
column headings so my filter no longer worked. The results were not something
I could forward. I do not know how to modify the macro to search for the
literals which end section 2 or start section 3 so I can copy the correct
data to the new worksheet. I am not a VBA programmer, just an old-fart trying
to do some administrative volunteer support for an important program, and
really do not want to become one.

Detail Report Description:
Section 1: Headings –contain fixed headings in merged & centered cells.
Row 1 – Report heading 1 in merged cells A1–I1
Row 2 – Report heading 2 in merged cells A2-I2
Row 3 – Report heading 3 in merged cells A3–I3
Row 4 – Blank row
Row 5 – Organization information in cell A5 and merged cells B5–E5. Run by
information in F5 and merged cells G5-H5.
Row 6 – Resource information in cell A6 and merged cells B6–D6.
Row 7 – Start of reporting period information in cell A7 and merged cells
B7–D7.
Row 8 – End of reporting period information in cell A8 and merged cells
B8–D8.
Row 9 – Blank row.
Section 2: People who have submitted timesheets during the reporting period.
Row 10 - Blank row.
Row 11 – Section Headings in merged cells A11-B11, C11-E11, F11-G11, single
cell H11, & merged cells I11-J11
Row 12 – Start of data for this section. The data fields map to the column
heading above.
The number of rows in this section varies from 1 to over 300.
Last row of section contains literal “Resource Totals:†merged into cells
A?-B?.
Section 3: People who are missing timesheets for the reporting period.
The section starts with a blank row.
Next row contains section heading, starting with the literal “Resources
Missing Period Timesheets†merged into cells A?-C?.
Next row contains column headings for the section; starting with the
literal “Contact Name†merged into cells A?-C?.
Next row = Start of data for this section. The data fields map to the
column heading above.
The number of rows in this section varies from 1 to the end of data. There
is not a terminating literal.
 
E

etradeguru

dkv0942,
I am not sure how old you are to qualify as an old fart, but take
comfort from the fact that I will shortly be a whole half century
young and I only really got hooked on VBA about a year ago because
circumstances dictated the need.
Its just a matter of confidence.
There will be others more well versed than I who will undoubtedly
reply to your call for help, but in the meantime, take courage in both
hands and go have a look at some of the mvp sites. A good start is
Chip Pearson at www.cpearson.com/excel/mainpage.aspx

Good luck
 
P

Pete_UK

It would help if you post your recorded macro here - then we can see
how it needs to be modified.

Pete
 
D

dkv0942

dkv0942,
I am not sure how old you are to qualify as an old fart, but take
comfort from the fact that I will shortly be a whole half century
young and I only really got hooked on VBA about a year ago because
circumstances dictated the need.
Its just a matter of confidence.
There will be others more well versed than I who will undoubtedly
reply to your call for help, but in the meantime, take courage in both
hands and go have a look at some of the mvp sites. A good start is
Chip Pearson at www.cpearson.com/excel/mainpage.aspx

Good luck
 
D

dkv0942

dkv0942,
I am not sure how old you are to qualify as an old fart, but take
comfort from the fact that I will shortly be a whole half century
young and I only really got hooked on VBA about a year ago because
circumstances dictated the need.
Its just a matter of confidence.
There will be others more well versed than I who will undoubtedly
reply to your call for help, but in the meantime, take courage in both
hands and go have a look at some of the mvp sites. A good start is
Chip Pearson at www.cpearson.com/excel/mainpage.aspx

Good luck

Thanks for the kind words of encouragement. Glad to here you are approaching the 1/2 century mark. It will be just a couple of years when I can attend my 50th HS reunion. The first couple of technical replies as summed a knowledge & skill level with Colo's HTML Maker utility which didn't exist. So it has been an interesting and very challenging 3 days. Now that I have gotten a screen-shot uploaded, I can take your advice and visit the MVP sites again. I enjoy my volunteer work, but would much prefer to run my reports & statistics out of an ACCESS database, but tha is no possible with this organization. Again thanks for the encouragement.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top