Good day to all!
It is my first time posting here.
I would really appreciate it if you can help me out guys.
This is my situation...
I have this code in my workbook that Copies the whole workbook and paste it as values or text in a new workbook.
And additionally it Save it as a xlExcel8 (xls) file format (The file is originally an XLM file).
I also have this worksheet where I can choose to display a certain report by clicking a checkbox.
(a checkbox to choose a criteria for the formulas in the worksheet)
The problem is...
The code runs smoothly if No checkbox was chosen but if a checkbox is clicked, I get the error
"Method 'SaveAs' of object '_Workbook' failed" if I run the code.
This is the code...
Sub save_as_values()
Dim Output As Workbook
Dim Current As String
Dim FileName As String
On Error GoTo Errorcatch
Set Output = ThisWorkbook
Current = ThisWorkbook.FullName
Application.DisplayAlerts = False
Dim SH As Worksheet
For Each SH In Output.Worksheets
SH.UsedRange.Copy
SH.UsedRange.PasteSpecial xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Next
FileName = ThisWorkbook.Path & "\" & "RR Report(text only)" & Sheet2.Range("L6").Value & ".xls"
Output.SaveAs FileName, XlFileFormat.xlExcel8
Workbooks.Open Current
Output.Close
Application.DisplayAlerts = True
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Thanks in Advance!
It is my first time posting here.
I would really appreciate it if you can help me out guys.
This is my situation...
I have this code in my workbook that Copies the whole workbook and paste it as values or text in a new workbook.
And additionally it Save it as a xlExcel8 (xls) file format (The file is originally an XLM file).
I also have this worksheet where I can choose to display a certain report by clicking a checkbox.
(a checkbox to choose a criteria for the formulas in the worksheet)
The problem is...
The code runs smoothly if No checkbox was chosen but if a checkbox is clicked, I get the error
"Method 'SaveAs' of object '_Workbook' failed" if I run the code.
This is the code...
Sub save_as_values()
Dim Output As Workbook
Dim Current As String
Dim FileName As String
On Error GoTo Errorcatch
Set Output = ThisWorkbook
Current = ThisWorkbook.FullName
Application.DisplayAlerts = False
Dim SH As Worksheet
For Each SH In Output.Worksheets
SH.UsedRange.Copy
SH.UsedRange.PasteSpecial xlPasteValues, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Next
FileName = ThisWorkbook.Path & "\" & "RR Report(text only)" & Sheet2.Range("L6").Value & ".xls"
Output.SaveAs FileName, XlFileFormat.xlExcel8
Workbooks.Open Current
Output.Close
Application.DisplayAlerts = True
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub
Thanks in Advance!