VBA Code - pick file name from cell value

[hrayani]

Hello Friends,

This is part of my macro which I am using.

Sub makecopy()

    Workbooks.Add
    ChDir "C:\BACKUP"
    ActiveWorkbook.SaveAs Filename:="C:\Backup\[B][COLOR=#ff0000]Factory Target[/COLOR][/B].xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False  
    
End Sub

Can I tell the code to pick file name from cell value & some free defined text in the code.

For Example

Cell A1 = "Honda"
Cell B2 = "Civic"

I would like the file name to be "MAKE Honda MODEL Civic"

something like this
"MAKE" & Range A1.value & "MODEL" & Range B2.Value



Any help would be appreciated

Regards,

Humayun

 

[Fluff]

Whilst this shouldn't make any difference given the error you're getting, but it's worth trying

   Dim fName As String
   fName = "C:\BACKUP\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & Format(Now, "yyyymmddhhmmss") & ".xlsx"
   MsgBox fName
   
   
   Workbooks.Add
   ActiveWorkbook.SaveAs fName, 51

 

[hrayani]

Thanks Fluff,

I don’t have access to my computer right now as I am off from my work.
But surely tomorrow I will try the solution you provided.

I will keep you posted

Take care

 

[Fluff]

Ok, that's fine.

 

[hrayani]

Hi Fluff,

I tried the solution you provided... Its working fine

Now its debugging (Run time error 9 - Subscript out of range)

 Windows("Factory Target.xlsx").Activate

Obviously the name has changed that is why it is giving error

It should be like activate fName

 

[Fluff]

Try it like

Application.DisplayAlerts = False
Dim Wbk As Workbook
Dim fName As String
   fName = "C:\BACKUP\Customer" & Range("A1").Value & "Supplier" & Range("F1").Value & Format(Now, "yyyymmddhhmmss") & ".xlsx"
   MsgBox fName
   
   
   Workbooks.Add
   ActiveWorkbook.SaveAs fName, 51
   setwbk = ActiveWorkbook
    
    Windows("REPORTS").Activate
    Sheets("FACTORY TARGETS").Select
    
     Range("A1:AF1000").Select
    Selection.Copy
    
   Wbk.Activate

 

[hrayani]

run time error 438
Object does not support this property or method

 setwbk = ActiveWorkbook

 

[Fluff]

Oops, there should be a space after the word "Set"

 

[hrayani]

Thanks Fluff

All working fine now..... I also figured out why it was giving error on saving the file

The range references given in the code are cell A1 & F1

In cell A1 there is value like this Humayun / Fluff

Excel does not allow "/" in the file name

This was causing the problem

 

[Fluff]

Glad it's sorted & thanks for the feedback