0

Hi i have encountered an error when i try to execute this vba. Basically i am trying to save the active worksheet with the code below.

Sub Button1_Click()

Dim varResult As Variant
Dim dirPath, fileName As String


dirPath = Application.ActiveWorkbook.Path
fileName = ActiveSheet.Range("J5").Value 'ActiveSheet.Name


Application.ScreenUpdating = False
Application.DisplayAlerts = False


varResult = Application.GetSaveAsFilename(FileFilter:= _
                 "Microsoft Excel 97-2003 Worksheet (.xls) (*.xls), *.xls", Title:="Save As", _
                InitialFileName:=dirPath & "\" & fileName)

ActiveWorkbook.Worksheets("CBC").SaveCopyAs fileName:=varResult

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub
Howard Foo
  • 11
  • 3
  • What's the error you are encountering? – Variatus May 27 '20 at 03:18
  • if i cancel the save as, i will receive a debug or run time error from vba and would like to close the workbook also if i canceled the save as option – Howard Foo May 27 '20 at 03:23
  • 2
    Look at this [solution](https://stackoverflow.com/questions/38386511/how-to-handle-no-or-cancel-on-workbook-saveas-overwrite-confirmation). It takes into account error handling that you may not have thought of – jblack May 27 '20 at 03:48
  • 1
    Some thoughts. (1) If ActiveSheet.Range("J5") really holds the ActiveShet.Name, then why don't you say `fileName = ActiveSheet.Name`? (2) If the workbooks was never saved before it doesn't have any `Application.ActiveWorkbook.Path`. (3) What's the value of `varResult` if the user presses Cancel? Step through your code using F8 and look at the values as they develop in the Locals window. Then add IF conditions to your code to deal with the eventualities. You still didn't say on which line your code crashes, or what's the message. – Variatus May 27 '20 at 07:12

1 Answers1

0

I cannot write comment (too low reputation) so as an answer. I think that you are trying to save a worksheet, but SaveCopyAs can be used only with Workbook object. Try this: Macro to save only the active worksheet

Viktor West
  • 544
  • 6
  • 9