Creating a macro to save files based on text in cells

[kw127533]

Hi All,

I have the below macro that I've been using to select data from a sheet, copy the data, open a new workbook, paste special values and format columns C & D to date format. This works perfectly as is, however, instead of the filename for the new workbook being based off of cell H1 in worksheet "Upload to NBI" I would like it to be based on cell B10 from the "Contacts" sheet. I tried using Sheets ("Contacts").Range ("B10").text instead of Range("H1").text but it errors. Here is the code I currently have:

Sub Upload_NBI()
'
' Upload_NBI Macro

Sheets("Upload to NBI").Select
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Columns("C:D").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Select
Application.CutCopyMode = False
ChDir "\\Yydafs-data1\department\CIT\CIT_Admin\NBI\Project Plan Upload Files"
ActiveWorkbook.SaveAs Filename:= _
"\\Yydafs-data1\department\CIT\CIT_Admin\NBI\Project Plan Upload Files\" & Range("H1").Text & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Sheets("Contacts").Select

I would also like this code to loop and save separate files based on the values in cells B11-B19 (if they are not blank). For example, if B11 is blank stop, else save file and name based on text in B11, if B12 is blank stop, etc.

I'm new to VBA so any help is appreciated!!