HELP - Code to Copy/Format/Save/Print Worksheet Based on Cell Values

[larzep]

Hi,

I currently have a code that copies a worksheet in WB1, creates a new WB and renames the newly created file then saves it based on the value of two cells within the new WB.

The issue I have been facing is that once the values from the two cells combined become longer then 31 characters, the VB coding bugs out and returns an error message saying Run-time Error '1004'.

The coding I am using atm is:

Sub Save()

Sheets("Service Quote").Select
    Sheets("Service Quote").Copy
    Sheets("Service Quote").Select
    Sheets("Service Quote").Name = Range("G7").Value & " " & Range("B13").Value 'create new workbook with file name based on Quote No then Company name
    
    Columns("H:V").Select
    Selection.Delete Shift:=xlToLeft
    Range("M11").Select
    
    Range("g23:g54").Select     'selects line total column and formats colour to grey
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -4.99893185216834E-02
        .PatternTintAndShade = 0
    End With
    
    Range("A19:G19").Select 'selects line larzep quotation row and formats colour to larzep green
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 3657131
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Range("A22:G22").Select     'select second line title bar and formats colour to light green
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12775653
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With



Const PATH As String = "C:\My Documents\Phat\LARZEP\Current Projects\Quote Spreadsheet\Quotes\"
With ActiveWorkbook
.SaveAs FileName:=PATH & Range("G7").Value & " " & Range("B13").Value & ".xls" 'saves the workbook with the file name based on Quote No then Company name
End With
 
 
Application.DisplayAlerts = True
    ActiveWorkbook.Close SaveChanges:=False

With ActiveSheet

Application.Dialogs(xlDialogPrint).Show

End With


End Sub

Can anyone please advise how i can get around this bug and allow me to rename and save the new WB based on cells having values over 31 characters?

The two key cells i am trying to reference is G7 & B13.

Thank you.