adding a sub folder using activeworkbook.path syntax

Question

ok so this is a short peace in a big workbook... All i am trying to do is tell it a certain place to save.

ActiveWorkbook.SaveCopyAs _
    FileName:=ActiveWorkbook.Path "\OLD " & Range("D1").Value & ".XLSM"

This does exactly as it is supposed to however, i want to say basically "activeworkbook.path" plus give it one further step and designate a folder called "old" that it will go to.

in essence it would look like this

\documents\test\my-file.xlsm

to this

\documents\test\OLD\my-file.xlsm

any hints?

Answer 1

You have a space in "\OLD ", and you are not closing off \OLD to be a folder.

The line should look like

ActiveWorkbook.SaveCopyAs _
    FileName:=ActiveWorkbook.Path & "\OLD\" & Range("D1").Value & ".XLSM"

I would also strongly consider qualifying your Range("D1") with your worksheet.

Dim fileNameRng as range
Set fileNameRng = thisworkbook.worksheets("Sheet1").Range("D1")

ActiveWorkbook.SaveCopyAs _
    FileName:=ActiveWorkbook.Path & "\OLD\" & fileNameRng.Value & ".XLSM"