Macro to save as using path in worksheet

**Shahid_Excel** · 04-02-2012, 04:25 PM

Hi

I need some help with the below macro please.

The macro is working fine but what I want to change is the path for saving the file. The path that I would like it saved to is in the worksheet "NEW ORDER_" cell "N2"

Sub RENAME2()

   Dim strWbKill As String
   Dim myName As String
    
   myName = ThisWorkbook.Path & Application.PathSeparator 'copy to same folder that file is in
   myName = myName & Application.Cells(3, 7) & "_"  'cell G3 Style
   myName = myName & Application.Cells(3, 3) & "_"       'cell c3 Customer
   myName = myName & Application.Cells(10, 3) & "_"       'cell c10 PO Number
   myName = myName & Application.Cells(16, 4) & "_"        'cell d17 Colour
   myName = myName & Application.Cells(16, 3) & ".xlsM"    'cell c17 New/Repeat

    With ThisWorkbook
        strWbKill = .FullName
         ActiveWorkbook.SaveAs , Filename:=myName, FileFormat:=52
        
      End With
     
    Kill strWbKill

End Sub

I am new to the forum so this may not be filled in correctly, please let me know.

Thank you.

**Chippy** · 04-02-2012, 05:40 PM

Try changing:

myName = ThisWorkbook.Path & Application.PathSeparator

to:

myName = Sheets("NEW ORDER_").Range("N2").Value & Application.PathSeparator

I am new to the forum so this may not be filled in correctly, please let me know.

Good post, but please put code tags around VBA code.

**Shahid_Excel** · 04-02-2012, 06:35 PM

HI Chippy

Thank you for your reply.

After changing the code I get the following error when i run the macro.

Run Time Error 1004

Method 'SaveAs' of object '_Workbook' failed

Sorry I don't have much knowledge of VBA

**Chippy** · 04-03-2012, 07:24 AM

You said the existing code was working, but try removing the first comma in this line:

ActiveWorkbook.SaveAs , Filename:=myName, FileFormat:=52

If no joy, what is in cell N2 on sheet NEW ORDER_? It must be a valid folder path including drive, e.g. C:\folder1\folder2

Put:

MsgBox myName

before the SaveAs. It should give you a clue as to the reason for the error. myName must be a valid full file name, including drive letter, folder path and file name.

**Shahid_Excel** · 04-03-2012, 08:16 AM

Hi Chippy

I'm really sorry I had the wrong path in cell N2, sorry for wasting your time.

Thank you very much for your help on this it works a treat now.

Shahid_Excel