Transfering data across worksheets

[GarToms]

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


--
GarToms

[Gary Brown]

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.

[Doug Kanter]

"GarToms" <[email protected]> wrote in message
news:[email protected]...
>
> 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.