exporting a worksheet for backup

S

sp00nix

I would like to export the last worksheet in my workbook into a backu
dir. How can I do this without doing "Copy Worksheet" where it opens u
in the new work book? I would just like it to export while keeping onl
the current workbook open.
Thanks in advance,
Mik
 
S

steve

this dose not do exactly what your asking, it saves the
whole file to a bu directory, adjust it to fit your needs

Sub CreateBackUp()
'need to creat the folder Bu if not their
Dim Num As Integer
Dim OrgPathSt As String
Const BUPath As String = "C:\BU\"
Dim BUPathName As String
Dim Wb As Workbook

On Error GoTo CreateDir
Application.ScreenUpdating = False

Set Wb = ActiveWorkbook

OrgPathSt = Wb.Path & "\" & Wb.Name
If Left(OrgPathSt, 1) = "\" Then
MsgBox "You must name & save your file first,
Via 'File' then 'Save As'"
Exit Sub
End If
BUPathName = "C:\BU\" & Left(Wb.Name, Len(Wb.Name) -
4) & _
" (" & Year(Date) & "-" & Month(Date) & "-" & Day
(Date) & " " & Hour(Time) & "." & Minute(Time) & "." &
Second(Time) & ")" & ".xls"

Num = MsgBox("Do you wish to save the Current file
as:" & vbNewLine & "[ " & OrgPathSt & " ]" _
& vbNewLine & vbNewLine & "And Create the Backup
File:" & vbNewLine & "[ " & BUPathName & " ]",
vbOKCancel, "Back Up")
If Num = vbCancel Then Exit Sub

Wb.Save
SaveAsBu:
Wb.SaveAs FileName:=BUPathName
Wb.Close

Workbooks.Open FileName:=OrgPathSt

Application.ScreenUpdating = True
Exit Sub
CreateDir:
MkDir "BU"
GoTo SaveAsBu
End Sub
 
T

Tom Ogilvy

I doubt you can, but you can make the action not visible

Application.ScreenUpdating = False
worksheets(worksheets.count).Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\myfolder\myfile.xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
 
S

sp00nix

Thanks, I have made similar code but my original question kind of
stumped me. Anyone have any other ideas?
Mike
 
S

sp00nix

One reason I didn't want to copy the ws to a new wb is because it won'
copy the complete cell if it has more than 255 chars. Is there any wa
around this issue? Thank
 
T

Tom Ogilvy

Dim rng as Range
Application.ScreenUpdating = False
set rng = worksheets(worksheet.count).Cells
worksheets(worksheets.count).Copy
rng.copy Destination:=Activesheet.Cells
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\myfolder\myfile.xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True

--
Regards,
Tom Ogilvy


Tom Ogilvy said:
I doubt you can, but you can make the action not visible

Application.ScreenUpdating = False
worksheets(worksheets.count).Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\myfolder\myfile.xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True
Click to expand...
 
Top