VBA to copy current sheet to new workbook and save

1
votes

Although I am very rusty on my VBA, I have saved sheets to new workbooks many times before. This code is failing with the error code "Method 'SaveAs' of object '_Workbook' failed"

wbSource.Sheets(newFileName).Copy

On Error GoTo NewWorkbookError:
Set wbCleaned = ActiveWorkbook
    
ChDir ProcessedFileStoragePath        
wbCleaned.SaveAs filename:=newFilePath, _
                 FileFormat:=FileStoredAsFormat, CreateBackup:=False

It is failing with the same error with out the ChDir line, which I added since when recording a macro for saving the new workbook, that code line was in the recording.

The other thing I noticed was the "filename" casing in the .SaveAs method, which should be Filename: but the editor is lowercasing it when I type it in that way. I do NOT have any other variables in this module named filename.

 Const ProcessedFileStoragePath As String = "C:\Users\TD\OneDrive\DataLoaders\FilesForAzureStorage\"
Const FileStoredAsFormat As String = "xlCSVUTF8"

Any help appreciated, I've spent too much time on this already.

1
excelvba
is the variable newfilepath containing a valid path ? – Francesco Giossi
Why you copy but never paste the value to new wb? – Kin Siang
When you step throug the code, after the Set line, does wbCleaned give you a name if you try ?wbCleaned.Name in the immediate window? – rohrl77
Sample values of newFilePath and FileStoredAsFormat would be helpful. – BigBen
@BigBen..yep, realized that when I went to make coffee, added now – dinotom

1 Answers

0
votes

I had the exact same issue this morning in my own code. ActiveWorkbook for some reason did not yield an object and stayed empty. I got arround the problem by specificing the workbook manually.

Try this:

wkb.Worksheets(newFileName).Copy
Excel.Workbooks(2).SaveAs newFilePath

SOLUTION: The solution to the OPs Problem were the file format enum. It had to be a variant variable type (per Comment from OP).

I'll leave my suggestion for others in case it is helpful.