Thank you for replying.

Details of what I want to achieve are explained in the following example:
(Apologizes if I give too much detail)

Sheet10 in Book1 is where the data is contained.
I will firstly need to explain in detail what Sheet10 contains.

Sheet10 contains a formula in the range D8:J1000 and is as follows:
D8 =Sheet6!L30 this is filled down to D1000
E8 =Sheet6!R30 this is filled down to E1000
F8 =Sheet6!C30 this is filled down to F1000
G8 =Sheet6!H30 this is filled down to G1000
H8 =Sheet6!T30 this is filled down to H1000
I8 =Sheet6!U30 this is filled down to I1000
J8 =Sheet6!D30 this is filled down to J1000

Not all rows down to r1000 will contain data as this is determined what data
there is in Sheet6.
One exception will be J8:J1000 as this will always contain data.

The most important cut off point is column D Where the data ends in column
D is where the cut off should be.
Data in any other column E:J below where data ends in column D can be
regarded as not important and can be cut off.

Now to saving the data as a .CSV file;
I currently use the following macro:

Sub Mac1()
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\My Documents\Excel doc\Excel
books\Prepare.xls"
Calculate
ActiveWorkbook.SaveAs Filename:="C:\HLSwin\Ready.CSV" _
, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
Calculate
Windows("Book1.xls").Activate
Range("I640").Select
Calculate
End Sub

In the workbook Prepare.xls sheet1 there contains a formula in the range
A1:G1000 and is as follows:
A1 ='[Book1.xls]Sheet10'!$D8 this is filled down to A1000
B1 ='[Book1.xls]Sheet10'!$E8 this is filled down to B1000
C1 ='[Book1.xls]Sheet10'!$F8 this is filled down to C1000
D1 ='[Book1.xls]Sheet10'!$G8 this is filled down to D1000
E1 ='[Book1.xls]Sheet10'!$H8 this is filled down to E1000
F1 ='[Book1.xls]Sheet10'!$I8 this is filled down to F1000
G1 ='[Book1.xls]Sheet10'!$J8 this is filled down to G1000

Like in Book1 Sheet10 there is data below in the rows where data ends in
columnA

So there you have it, I hope you can see where I am coming.

Regards
Pat


"Tom Ogilvy" wrote in message
...
I just did. If you can't modify that, then you need to specifically
explain
where your data is, where you want the formula and what you want the
formula
to be.

for the example, the description is that my data starts in A1 with no
header
row. I want to put a formula that sums columns B to G and that formula
would be in column K

so the code

Dim rng as Range
set rng = Range("A1").CurrentRegion
' rng now refers to the area with data surrounding
' cell A1. This would be the same as if you clicked in
' A1 and did Ctrl+Shift+8
set rng = rng.resize(,1)
' now the variable rng is adjusted just to refer to
' column A for as many rows as were in the
' region around A1.
rng.offset(0,10).Formula = "=Sum(B1:G1)"
'Now the formula =Sum(B1:G1) (typo corrected)
' is placed in K1 down to the number of rows
' around cell A1. Since the addresses are relative
' excel automatically adjusts the references.

--
Regards,
Tom Ogilvy