Macro with a memory

[Jonathan]

Hi. I am currently downloading a report every day and updating a
spreadsheet with some numbers from that report. My spreadsheet keeps
a history of those numbers for a months time to produce graphs and
such. My report comes in an Excel format and has a date/timestamp so
identification of what date the report corresponds is possible. Can I
make a macro that looks a report dated 10/23/2009 and not delete/
replace the data from 10/01/2009 to 10/22/2009? Hope that is clear
enough.

Furthermore, if possible, I would like to use this macro to look at a
months worth of data at a time. Again, my downloaded report would
have a date stamp (something like "09/01/2009 - 09/30/2009"). I
believe I could write the code for this task on my own, but creating
the logic to do this and the task above is stumping me.

Lastly... best VBA book for beginners? I have some programing
experience (MATLAB), but I still need to learn the VBA language.

Thanks!

 

[joel]

the first question I have to ask is the number of rows of data the sam
every time you download? I suspect you are having problems deletin
data because formulas and charts are ending up with wrong references.

I think your data would be better organized if the data was i
chronological order. If the rows are the same, then moving data rathe
than copying dat might work

OldRow = 10
for NewRow = 1 to Lastrow
Range("A" & NewRow) = Range("A" & OldRow)
OldRow = OldRow + 1
next Newrow

Then add the new data into the last rows.


Other solutions are

1) Use Named Ranges in the formulas and charts in you workbook whic
can be accessed both by the worksheet functions and VBA. Have you macr
updated the named ranges so the formulas and charts will get the correc
data.

2) Using clearcontents method in VBA will not corrupt formulas o
charts if new data is put into the same locations.

3) Have the VBA code create the formulas and charts so yo don't have t
preserve the same range of data each time you run the macro

 

[JLatham]

Johathan,
The macro you're looking for can almost without doubt be created. You just
haven't given enough detail at this point to really begin doing that. If you
want, you can contact me via email at (remove spaces)
Help From @ JLathamsite .com
and I'll see what help I can give you. Eventually I'd need copies of some
of the report files and the spreadsheet that gets updated from it.

As for VBA book(s) for beginners, take a look at this page over at
Contextures:
http://www.contextures.com/xlbooks.html
one that I've looked at that's listed on that page is
Microsoft Excel VBA Programming for the Absolute Beginner, 3E; Duane
Birnbaum; 544 pages; ISBN: 1598633945; $29.99
and I would recommend it as one that does as it says: teaches from the
ground up.

In the meantime, while selecting your book(s), you might get a leg up on it
all with a booklet I put together as an introduction to VBA programming in
Excel:
http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf
There's a link to it and some almost trivial associated "project" workbooks