Help Modifying Macro - Naming As Contents Of A Cell

[Helen842000]

Hi all,

I have the following working macro below.

Sub Button1_Click()
ActiveSheet.Unprotect "password"

Dim wbNew As Workbook

Application.DisplayAlerts = False

Worksheets("Page4").Copy

Set wbNew = ActiveWorkbook

With wbNew

With .Worksheets(1).UsedRange
.Value = .Value
End With

.SaveAs "C:\Folder\Folder\Folder\ SheetName.xls"

.Close True

End With

Application.DisplayAlerts = True

End Sub


Currently this macro does the following :-

1) Unprotects the workbook with a known password
2) Copies one worksheet out as current values into a new workbook
3) Saves the new workbook in a network location with the name SheetName.xls


Now for the modification, instead of saving the workbook a fixed name, I would like to name of the file to come from the contents of cells D4 and B2. So something like .SaveAs Filename:="C:\Folder\Folder\" & Range("D4").Value however that just references 1 cell not two.

I have tried several ways to reference the values held in those cells when the file is being saved. However each time I get a debug error.

Any thoughts?

 

[Trebor76]

Hi Helen842000,

Try this:

.SaveAs Filename:="C:\Folder\Folder\" & Range("D4").Value & Range("B2").Value

As long as there's no invalid characters in cells D4 or B2 all should be good.

Regards,

Robert

 

[Helen842000]

Thanks Trebor76, that worked great.

With that working properly I was able to modify that further to add a dash between the cell contents so it reads better. Looks super neat now!

.SaveAs Filename:="C:\Folder\Folder\" & Range("D4").Value & "-" & Range("B2").Value


Thank you!

 

[Trebor76]

Thank you!

You're welcome