VBA to save file in same folder

Tanyaann1995

Board Regular
Joined
Mar 24, 2021
Messages
62
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have below code which saves an .xlsm file in same folder and edits the name. But, I'm getting run time 1004 error after the below highlighted line. Please can anyone explain why.

VBA Code:
Sub editable(control As IRibbonControl)

Dim xWs As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook
ActiveWorkbook.save
wb.Worksheets(2).Range("A:K").Copy
wb.Worksheets(2).Range("A1").PasteSpecial Paste:=xlPasteValues
wb.Worksheets(2).Columns("L:AH").EntireColumn.Delete
Application.DisplayAlerts = False
For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "Emerson COMMERCIAL OFFER" Then
            xWs.Delete
        End If
Next
Application.DisplayAlerts = True
[COLOR=rgb(184, 49, 47)]ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & wb.Worksheets(1).Range("F11")[/COLOR]
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
What does the error message say?
What data do you have in cell F11?
 
Upvote 0
What does the error message say?
What data do you have in cell F11?
Hi,

The error message mentions " Method 'Save As' of Object Failed. F11 has a string value in it which i want to incorporate in the new Excel filename when it gets saved.
 
Upvote 0
F11 has a string value in it which i want to incorporate in the new Excel filename when it gets saved.
But what is the EXACT value in cell F11? Note that there are some characters that are illegal to use in file names.

I often find it helpful to see exactly where it is trying to save it and what it is trying to name it.
So replace this line:
VBA Code:
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & wb.Worksheets(1).Range("F11")
with this section:
VBA Code:
Dim fname as String
fname = ActiveWorkbook.path & "\" & wb.Worksheets(1).Range("F11")
MsgBox fname
ActiveWorkbook.SaveAs fname
Then, when you run the code, it will pop out a Message Box with the path and name where it is trying to save the file.
So you can confirm the path is valid, as is the file name.
What exactly is that Message Box returning?
 
Upvote 0
Solution
But what is the EXACT value in cell F11? Note that there are some characters that are illegal to use in file names.

I often find it helpful to see exactly where it is trying to save it and what it is trying to name it.
So replace this line:
VBA Code:
ActiveWorkbook.SaveAs ActiveWorkbook.path & "\" & wb.Worksheets(1).Range("F11")
with this section:
VBA Code:
Dim fname as String
fname = ActiveWorkbook.path & "\" & wb.Worksheets(1).Range("F11")
MsgBox fname
ActiveWorkbook.SaveAs fname
Then, when you run the code, it will pop out a Message Box with the path and name where it is trying to save the file.
So you can confirm the path is valid, as is the file name.
What exactly is that Message Box returning?
Thanks :) This worked.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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