exporting
 

Hello,

I have a spreadsheet that I need to export selected data to a file. It
can be a spreadsheet. My file is over 1mb and I just need to export the
actual values of a particular range to a backup file. Any ideas?

Ernst.

exporting
 

Dim sh as Worksheet
Dim rng as Range
Dim varr as Variant
On Error Resume Next
Kill "C:\Backup\MyData.xls"
On Error goto 0
ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls"
workbooks.Open "C:\Backup\MyData.xls"
for each sh in ActiveWorkbook.Worksheets
if sh.Name < "Data" then
application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
end if
Next
with worksheets("Data")
set rng = .Range("A1").CurrentRegion
vArr = rng.value
.Usedrange.Clearcontents
rng.Value = varr
End With
Activeworkbook.Close Savechanges:=True

--
Regards,
Tom Ogilvy

"Ernst Guckel" wrote in message
...
Hello,

I have a spreadsheet that I need to export selected data to a file. It
can be a spreadsheet. My file is over 1mb and I just need to export the
actual values of a particular range to a backup file. Any ideas?

Ernst.

exporting
 

Dim sh as Worksheet
Dim rng as Range
Dim varr as Variant
On Error Resume Next
Kill "C:\Backup\MyData.xls"
On Error goto 0
ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls"
workbooks.Open "C:\Backup\MyData.xls"
for each sh in ActiveWorkbook.Worksheets
if sh.Name < "Data" then
application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
end if
Next

This works great.

with worksheets("Data")
set rng = .Range("A1").CurrentRegion
vArr = rng.value
.Usedrange.Clearcontents
rng.Value = varr
End With
Activeworkbook.Close Savechanges:=True

but I don't understand what the last bit of code is to accomplish...

Ernst.

exporting
 

How about saving the values of a 'Selected Range' within a worksheet?

Ernst.

You said you needed to: "export the
actual values of a particular range to a backup file. "

So I assumed the particular range is on a sheet that contains more data
than
you want to save. I gave this as an example - the code assumes the
particular range starts in A1 and is a contiguous rectangle offset from
the
other data at least by one completely blank row and column. If that
isn't
the case, then you don't need that part. If you want to save a single
sheet

On Error Resume Next
Kill "C:\Backup\MyData.xls"
On Error goto 0
ActiveSheet.Copy
Activeworkbook.Save Filename:="C:\Backup\Mydata.xls"
Acitveworkbook.Close SaveChanges:=False

would be all you need.

--
Regards,
Tom Ogilvy




"Ernst Guckel" wrote in message
...
Dim sh as Worksheet
Dim rng as Range
Dim varr as Variant
On Error Resume Next
Kill "C:\Backup\MyData.xls"
On Error goto 0
ActiveWorkbook.SaveCopyAs Filename:="C:\Backup\Mydata.xls"
workbooks.Open "C:\Backup\MyData.xls"
for each sh in ActiveWorkbook.Worksheets
if sh.Name < "Data" then
application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
end if
Next

This works great.

with worksheets("Data")
set rng = .Range("A1").CurrentRegion
vArr = rng.value
.Usedrange.Clearcontents
rng.Value = varr
End With
Activeworkbook.Close Savechanges:=True

but I don't understand what the last bit of code is to accomplish...

Ernst.