exporting

  • Thread starter Thread starter Ernst Guckel
  • Start date Start date
E

Ernst Guckel

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.
 
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
 
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
Click to expand...

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
Click to expand...

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

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.
 
Back
Top