Updating from external file & keeping previous data static

[Mildred]

I am currently updating a series of Excel sheets which change daily.
I set it up so that it has references to the source file and once I
replace the source file with the new data, then the sheets get
updated. I have been asked to keep the data from previous week "as
is" and just append the new data each and every day. I have to copy
and paste the information to the sheet, but because the cells are not
continous, I spend hours in this process. Do any of you have any idea
as to how I could possibly add some sort of automation to this
process?

Thank you,
Mildred

 

[Vasant Nanavati]

Not having the details, I can only offer some pseudo-code:

Workbooks.Open "c:\....\.sourcefile.xls"
Workbooks("sourcefile.xls").Worksheets("Sheet1") _
Range("A1:Z100").Copy Destination:= Workbooks _
("destinationfile.xls").Worksheets("Sheet1").Range("A" _
& LastRow + 1)

where LastRow is the last "occupied" row on the destination worksheet. Post
back if this works for you and if you need a way to figure out LastRow.

 

[Bob Phillips]

Mildred,

I would suggest that you turn the macro recorder on and then go through the
actions that you perform to add the new data, and stop the recorder.

Then look at the code, and remove any hard-coding (such as ranges) to
dynamic values. If you have problems with this, you can post back here with
details.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)