Why does my SaveAs file save a file with the correct name, but has no contents?

Question

I have been able to hard code my file and then saveas in the same folder. However, the new file that is created with the correct name has no content at all. I found out how to save in the current directory with all of the help on SO but haven't been able to solve this issue. Anything helps since I am very new to VBA!

     Sheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Dim thisWb As Workbook, d As Integer

    Set thisWb = ActiveWorkbook
    Workbooks.Add
    d = InStrRev(thisWb.FullName, ".")
    'ActiveWorkbook.SaveAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & Mid(thisWb.FullName, d)
    ActiveWorkbook.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & Mid(thisWb.FullName, d)

    ActiveWorkbook.Close savechanges:=False

End Sub

`Workbooks.Add` - here you're creating a new (empty workbook) then saving it with the new name. Get rid of this line. — dwirony, Feb 27 '19 at 16:31
You may also want to look into [avoiding activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Feb 27 '19 at 16:37
Some things to consider: Try and avoid using things like `.Select` or `Activate` or `ActiveWorkbook`. You don't need any of these. Always qualify your workbooks or worksheets: `Set thisWb = ThisWorkbook` or `Set thisWs = ThisWorkbook.Worksheets("Sheet1")`. Now you can reference any range with `thisWs` (where `thisWs` is of type `Worksheet`). You don't need to `Select` or `Activate` — Zac, Feb 27 '19 at 16:40
Thank you all for the help and I will read up on the information you mentioned! — Michael Gallagher, Feb 27 '19 at 16:47

Tom · Accepted Answer · 2019-02-27T16:37:03.700

Your line Workbooks.Add makes a new workbook which is an empty file. This then becomes the ActiveWorkbook and as such your next line saves the new Workbook which is empty.

By looking at your code I'm guessing you're trying to save a copy of your source file. If so you don't need the Workbooks.Add line - Remove it and it should solve your issue. If you're trying to add a workbook though as well as saving a copy:- Use proper references instead of Active ones i.e.

ActiveWorkbook.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & Mid(thisWb.FullName, d)

Becomes

thisWb.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & Mid(thisWb.FullName, d)

You should try to avoid using Select or Active statements as much as possible (this would have avoided your issue entirely). For further reading take a look here: How to avoid using Select in Excel VBA

score 1 · Answer 2 · answered Feb 27 '19 at 16:33

Let's look at the last bit of your code:

Workbooks.Add 'Creates a new Blank Workbook, and makes it the ActiveWorkbook
d = InStrRev(thisWb.FullName, ".")
ActiveWorkbook.SaveCopyAs Filename:=Left(thisWb.FullName, d - 1) & "-Prelims" & Mid(thisWb.FullName, d)
'Saves your new Blank ActiveWorkbook

You need to either add something to your new workbook, or do the SaveAs on a different workbook (e.g. thisWb.SaveCopyAs?)

Why does my SaveAs file save a file with the correct name, but has no contents?

2 Answers2