I am using the following code attached to a button to save the worksheet as a seperate workbook. The problem I have is if I try to use a range for the filename then the code crashes! Should be simple but cant get it to work?
Sub Save_ActiveSheet_NoCode()
'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim FilePath As String
Dim FileName As String
Dim I As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010, we exit the sub when your answer is
'NO in the security dialog that you only see when you copy
'an sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
' 'Change all cells in the worksheet to values if you want
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Dim shp As Shape
Dim myVar As Shapes
ActiveSheet.Activate
Count = ActiveSheet.Shapes.Count
'Set myVar = Sheets(ActiveSheet.Name).Shapes
For I = Count To 1 Step -1
ActiveSheet.Shapes(I).Delete 'myVar(i).Delete
Next I
'Save the new workbook
FilePath = "C:\Folder Name\Folder Name\"
FileName = "File Name"
With Destwb
.SaveAs FilePath & FileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
On Error GoTo 0
.Close SaveChanges:=True
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
If I replace this line:
with this line I thought it would be that simple?
FileName = Range ("B3").Value
But then it doesnt seem to like this:
.SaveAs FilePath & FileName & FileExtStr, _
FileFormat:=FileFormatNum
would be nice if I could also add the date and time to the file name so multiple copies can be saved without overwriting, is this possible?
Hopfully someone can help me
Thanks
Andrew
Bookmarks