Each month I receive an excel file of transactions taken off a finance
system. It comprises a standard 20 columns but the number of rows can vary
from 600 to 800.

There is then a standard spreadsheet used to analyse the data.

Is there a formula or solution that I can use in the analysis spreadsheet
that, once the name of the transaction worksheet is given, would import or
link to the required data and bring in just the required number of rows.

TIA

Rob

Via formulas?

I don't think so.

Maybe it's time to think about using a macro to copy that sheet's .usedrange(!)
to the new location.

RFJ wrote:

Each month I receive an excel file of transactions taken off a finance
system. It comprises a standard 20 columns but the number of rows can vary
from 600 to 800.

There is then a standard spreadsheet used to analyse the data.

Is there a formula or solution that I can use in the analysis spreadsheet
that, once the name of the transaction worksheet is given, would import or
link to the required data and bring in just the required number of rows.

TIA

Rob

--

Dave Peterson

I think ( no - I know) you've just moved me out of my competence zone :(

Can sks help out on a .usedrange(!) macro or other solution.



"Dave Peterson" wrote in message
...
Via formulas?

I don't think so.

Maybe it's time to think about using a macro to copy that sheet's
.usedrange(!)
to the new location.

RFJ wrote:

Each month I receive an excel file of transactions taken off a finance
system. It comprises a standard 20 columns but the number of rows can
vary
from 600 to 800.

There is then a standard spreadsheet used to analyse the data.

Is there a formula or solution that I can use in the analysis spreadsheet
that, once the name of the transaction worksheet is given, would import
or
link to the required data and bring in just the required number of rows.

TIA

Rob

--

Dave Peterson

Open the workbook that contains the new data
Open the workbook that gets the new data

(And without any details...)

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Workbooks("newdatawkbknamehere.xls") _
.Worksheets("sheet9999").UsedRange

Set DestCell = Workbooks("otherwkbknamehere.xls") _
.Worksheets("sheet8888").Range("a1")

'wipe out existing data????
DestCell.parent.cells.clearcontents

RngToCopy.Copy _
Destination:=DestCell

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



RFJ wrote:

I think ( no - I know) you've just moved me out of my competence zone :(

Can sks help out on a .usedrange(!) macro or other solution.

"Dave Peterson" wrote in message
...
Via formulas?

I don't think so.

Maybe it's time to think about using a macro to copy that sheet's
.usedrange(!)
to the new location.

RFJ wrote:

Each month I receive an excel file of transactions taken off a finance
system. It comprises a standard 20 columns but the number of rows can
vary
from 600 to 800.

There is then a standard spreadsheet used to analyse the data.

Is there a formula or solution that I can use in the analysis spreadsheet
that, once the name of the transaction worksheet is given, would import
or
link to the required data and bring in just the required number of rows.

TIA

Rob

--

Dave Peterson

--

Dave Peterson

Neat <G

Much appreciated

"Dave Peterson" wrote in message
...
Open the workbook that contains the new data
Open the workbook that gets the new data

(And without any details...)

Option Explicit
Sub testme()

Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Workbooks("newdatawkbknamehere.xls") _
.Worksheets("sheet9999").UsedRange

Set DestCell = Workbooks("otherwkbknamehere.xls") _
.Worksheets("sheet8888").Range("a1")

'wipe out existing data????
DestCell.parent.cells.clearcontents

RngToCopy.Copy _
Destination:=DestCell

End Sub



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



RFJ wrote:

I think ( no - I know) you've just moved me out of my competence zone :(

Can sks help out on a .usedrange(!) macro or other solution.

"Dave Peterson" wrote in message
...
Via formulas?

I don't think so.

Maybe it's time to think about using a macro to copy that sheet's
.usedrange(!)
to the new location.

RFJ wrote:

Each month I receive an excel file of transactions taken off a finance
system. It comprises a standard 20 columns but the number of rows can
vary
from 600 to 800.

There is then a standard spreadsheet used to analyse the data.

Is there a formula or solution that I can use in the analysis
spreadsheet
that, once the name of the transaction worksheet is given, would
import
or
link to the required data and bring in just the required number of
rows.

TIA

Rob

--

Dave Peterson

--

Dave Peterson