Formats & Macros

[Guest]

There is one set of data I process monthly in Excel that becomes very
tedious. First, the source is from a Crystal report accessible to me through
a web link. I can export the report data to either Excel or as a pdf. The
latter is pretty useless for processing, but the Excel export always requires
me to unmerge and unwrap the cell formatting; then I have to delete rows, cut
and paste headings, and change one row to text formatting to retain leading
zeros in apparently numeric data. I always wind up having to do what we call
the "monkey maneuver" - copying and pasting row data into Notepad and
re-pasting it into the cells. Is there a way to "automate" the tedious and
repetitive tasks? I thought of using macros, but the amount of data always
changes. I'd appreciate any help on this. I'm pretty good with VBA coding in
Access, but almost never code in Excel.

 

[Guest]

Is it possible to try pasting the original data onto a worksheet and then
having a second worksheet that picks up the relevant data through various
formulae. Then each month just overwrite the original data sheet and the
formulawe should still work?

 

[Guest]

That sounds like a great idea. I'll try it and let you know.

 

[Guest]

OK, I'm able to do some of what I need. The formula I use to bring over a
column of text results in a zero where there is no data in the source
worksheet. That's great if I can then use some method to find each such
instance of zero in the column and automatically delete the row in which the
zero occurs. Do you know how I could do that? Also, the column of text
includes data collected for two months. I'd like to find the second instance
of the first entry in the column (that begins the second month of data) and
copy the second month heading (for example, March) from the source worksheet
to the second one in the column immediately to the left of the second
instance of the item. The rest of the processing includes the preparation of
several pivot tables, adding a column that will give each month's data a date
for each row item in the yyyymm format, and then appending of the two months
of data to the end of an archive.