VBA Error for saving a newbook in a specific path

Question

Are there any errors for my vba coding? I have entered the file location on cells B4 and file name on cell B5 in the workbook. I would like to create a new workbook with multiple named sheets. Could someone help me to check? Thank you!

Sub AddNewWorkbook1()

Dim FiLe As String
Dim Filepath As String
Dim Period As String

Worksheets("Sheet1").Range("B4") = FiLe
Worksheets("Sheet1").Range("B5") = Filepath
Worksheets("Sheet1").Range("E2") = Period

'Adding New Workbook
Workbooks.Add

'Saving the Workbook
ActiveWorkbook.SaveAs FiLe & Filepath

'Add new sheets with colored tabs
Worksheets.Add().Name = Period & "DTH&TPD"
Worksheets(Period & "DTH&TPD").Tab.ColorIndex = 39
Worksheets.Add().Name = "DTH&TPD" & "Claims List"
Worksheets("DTH&TPD" & "Claims List").Tab.ColorIndex = 39
Worksheets.Add().Name = Period & "Accidental Claims"
Worksheets(Period & "Accidental Claims").Tab.ColorIndex = 33
Worksheets.Add().Name = "Accidental Claims List"
Worksheets("Accidental Claims List").Tab.ColorIndex = 33
Worksheets("Sheet1").Delete

End Sub

I have entered the file location on cells B4 where the file has to be saved and save it as the file name on cell B5. Thank you! — yiehan tay

Tim Williams Tim Williams · Accepted Answer · 2019-07-17T04:07:10

When posting your code, it's good practise here to always provide a description of what the actual problem is. Are you getting an error? If Yes what is it, and on which line?

That said, try this:

Sub AddNewWorkbook1()

    Dim FileName As String
    Dim FilePath As String
    Dim Period As String
    Dim wb As Workbook

    FilePath = Worksheets("Sheet1").Range("B4")
    FileName = Worksheets("Sheet1").Range("B5")
    Period = Worksheets("Sheet1").Range("E2")

    Set wb = Workbooks.Add() 'Adding New Workbook
    wb.SaveAs FilePath & FileName 'Save

    'Add new sheets with colored tabs
    AddSheetWithNameAndColor wb, Period & "DTH&TPD", 39
    AddSheetWithNameAndColor wb, "DTHTPD" & "Claims List", 39
    AddSheetWithNameAndColor wb, Period & "Accidental Claims", 33
    AddSheetWithNameAndColor wb, "Accidental Claims List", 33

    Application.DisplayAlerts = False 'turn off warning about delete
    wb.Worksheets("Sheet1").Delete
    Application.DisplayAlerts = True  'turn warnings back on
End Sub

'sub to add a sheet, name it, and change the tab colorindex
Sub AddSheetWithNameAndColor(wb As Workbook, sName As String, clr As Long)
    With wb.Worksheets.Add()
        .Name = sName
        .Tab.ColorIndex = clr
    End With
End Sub

VBA Error for saving a newbook in a specific path

1 Answers