Extracting Data from another spreadsheet
 

Hi,

I'm trying to write a macro that will take data from one
spreadsheet and put it into another then hard code it.
The formulas are hard coded because the spreadsheet that
the data is coming from changes. Each time it changes I
want to run a macro to put the data into another sheet.
How do I tell it, when the data is extracted to put it in
the first available/empty row? The macro that I did, just
writes over the previous data that was extracted. By the
way, I'm using Excel 97 on a Windows2000 workstation.

Assuming that you have a process that works (bar overwriting old data), when
selecting the sheet to paste the data to, you could select a named range on
the sheet and then paste.

This named range would be dynamic looking at the first blank row.

Say you have data from row 1 to row 30, I assume you would want to paste to
cell A1.

NamedRange: =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A),,)

In your VBA code
<your code.Range("NamedRange").Paste........

This would always paste to the highest available blank cell.

Hope it helps,

Basil

"GMCAR3" wrote:

Hi,

I'm trying to write a macro that will take data from one
spreadsheet and put it into another then hard code it.
The formulas are hard coded because the spreadsheet that
the data is coming from changes. Each time it changes I
want to run a macro to put the data into another sheet.
How do I tell it, when the data is extracted to put it in
the first available/empty row? The macro that I did, just
writes over the previous data that was extracted. By the
way, I'm using Excel 97 on a Windows2000 workstation.