Method 'SaveAs' of object '_Workbook' failed when A Checkbox is clicked

sin2nado

New Member
Joined
Jun 21, 2014
Messages
1
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top