[Al Mackay]

Tom

Thanks so much for what you've done here. It worked brilliantly.

However, the requirement has now slightly changed (I should have
foreseen that one!)....

Is it possible instead of using a named range to basically say
everytime a row contains any data from row 4 onwards on a sheet to
export these rows (but only using column a:h's data?).

e.g.
A B C D E F G H
R1
R2
R3
R4 Ref Doc Type Testing Completed 1? 2? 3? 4?
R5 CN1 FS FS-TEST NO 2 5 5
R6 CN2 SATS SATS YES 3 5 3 3
R7
R8
R9

So in the above example the following would be created:
R1 Ref Doc Type Testing Completed 1? 2? 3? 4?
R2 CN1 FS FS-TEST NO 2 5 5
R3 CN2 SATS SATS YES 3 5 3 3

Obviously now the row numbers will have changed to R1, R2, R3 etc. as
this has been copied in to cell A1.

Once again thanks for your help - if you could help on the above this
would be really appreciated.

Thanks, Al Mackay.


"Tom Ogilvy" wrote in message ...
Couple of typos - I should have had rngName in the saveas vice rng - thus
your confusion. Here is a revision. I sent you a tested file.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' rng holds range defined by name "data"
Dim rng As Range
' rngName holds the name for the file
Dim rngName As Range
Dim wkbk As Workbook
Set rng = ThisWorkbook.Names("Data").RefersToRange
Set rngName = rng.Parent.Range("B2")
Set wkbk = Workbooks.Add
rng.Copy wkbk.Worksheets(1).Range("A1")
' if file exists, overwrite without prompt
Application.DisplayAlerts = False
wkbk.SaveAs FileName:=ThisWorkbook.Path _
& "\" & rngName.Value & ".csv", FileFormat:=xlCSV
Application.DisplayAlerts = True
wkbk.Close SaveChanges:=False
End Sub

--
Regards,
Tom Ogilvy


Al Mackay wrote in message
om...
Tom

Really appreciate your help on this. However, I'm having problems
with getting this to work.

Wondered if you could advise on the following:
1) Where does the macro need to be stored? - I have put this in the
worksheet code area?

2) Is the rng variable used for the named range or used for the cell
where the filename is to be derived from?

- By any chance would you be able to send me a copy of the example
sheet that you used when you created the code?

Thanks again for your time and effort on this. If you are able to
send me a working copy @ all I would appreciate it if you could send
this to my aol account ( ) if not if you could
provide some guidance on the above this would be extremely
appreciated.

Many Thanks, Al Mackay.

"Tom Ogilvy" wrote in message
...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim rng as Range
Dim rngName as Range
set rng = Thisworkbook.Names("Data").ReferstoRange
set rngName = rng.Parent.Range("B2")
workbooks.add
rng.copy ActiveWorksheet.Range("A1")
ActiveWorkbook.SaveAs Filename:=thisworkbook.Path _
& "\" & rng.value & ".csv", FileFormat:= xlCSV
Activeworkbook.Close SaveChanges:=False
End Sub

I wouldn't try to do this on a save.

If you do, you could either add additional code to insure the same data
wasn't saved twice, or just save it twice and add code to overwrite the
existing file without prompt.

--
Regards,
Tom Ogilvy




MrAlMackay wrote in message
...
Is it possible to do the following:

Export certain data within a spreadsheet (perhaps using named range
functionality in order to define where the data will be held?) to a
text
file?

This would mean that only the named range information would be
exported to
a
text file.

I would want the text file to be named based on the content of Cell B2
and
for
the macro to be ran whenever the spreadsheet was saved or closed.

Really appreaciate help on this.

Many Thanks, Al Mackay