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.

[Mordred]

What cell contains the name of the new workbook and what cell contains the new worksheet?

[larzep]

The two cells that contain the value to rename the sheet and workbook are - G7 (Quote number) and B13 (Company name Pty Ltd)

[larzep]

can anyone help me with the issue of the macro bugging out when the combined characters of the two cell is more then 31 characters?

[larzep]

Just bumping the post to get more feedback and help.

[DGagnon]

could you attach a sample copy of the workbook as well?

[larzep]

Yes i have attached a copy of the spreadsheet.

[larzep]

Can anyone help?

[DGagnon]

what if you take only the first 20 characters of the company name?

Sub Save()

Sheets("Service Quote").Select
    Sheets("Service Quote").Copy
    Sheets("Service Quote").Select
    Sheets("Service Quote").Name = Range("G7").Value & " " & Left(Range("B13").Value, 20) '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 & " " & Left(Range("B13").Value, 20) & ".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

[larzep]

Thanks dgagnon for the reply.

I would love to make it simpler, but the thing is when i save the file, i will have another macro to PDF it then send an email to the client with their company name on the file. I would prefer it to keep the full company name along with the quote number.

Sorry to be a pain but is there anyway to bypass that 31 character save limit?

[DGagnon]

the limit is only for the Sheet name, not for the actual file name, so you can still save the filename as long as you want, but the sheet name needs to be less than 31 char in length.

[larzep]

thanks for that. if you put it that way, i can simply modify the names to allocate for the sheet. Will test out the macro again with the limit of 20 characters.