Hi all,

Currently I download data into an excel file weekly. I then cut and paste this data into an excel sheet to run sorts and formulas on the data. Does anyone know of any way I can automate this procedure to transfer the data to the second worksheet? Note: the sheet I paste to has formulas on so its important these aren’t copied over.

I appreciate any assistance on this.

Gar Toms

You can use Data | Import External data | New Database Query and select
Excel, select the Excel file that has the source data, create a query
by selecting fields you wish to bring across, apply any critera to
filter out data you don't want, then return the data back to Excel.
Select properties and check 'Fill down formulas in columns adjacent'.
You can select 'Refresh data on file open', this will then
automatically pull your data through from the source file whenever the
target file is opened.


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=519687

"GarToms" wrote in message
...

Hi all,

Currently I download data into an excel file weekly. I then cut and
paste this data into an excel sheet to run sorts and formulas on the
data. Does anyone know of any way I can automate this procedure to
transfer the data to the second worksheet? Note: the sheet I paste to
has formulas on so its important these aren't copied over.

I appreciate any assistance on this.

Gar Toms

Record a macro while going through your normal steps manually. Then, edit
the macro to make it generic. In other words, let's say 99% of the steps
always remain the same. But, each weekly file gets a name that's different
from last week's. At that point, you'd need to either end the macro and
apply the new name manually, or have the macro ask you for a name. I'd go
with the first option, since doing Save As only takes a moment.

As far as not overwriting the formulae, you can use Edit, Paste Special,
Values for that purpose.