Msgbox help

[RedOctoberKnight]

Good Morning.

I have the following code that pulls a schedule from another workbook. However, if the schedule in the other workbook hasn't been created yet, I obviously get an error. I'm looking for some help on having a msgbox pop up that states something like "schedule not created yet" if the schedule cant be found. Any help would be much appreciated.

Public Sub Copy_Range_From_Truck_Schedules()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim tsWorkbook As Workbook

Set tsWorkbook = Workbooks.Open(ThisWorkbook.Path & "\truckschedulesdualsides.xlsx")
With ThisWorkbook.ActiveSheet
.Range("A1:Q100").UnMerge
tsWorkbook.Worksheets(.Name).Range("A1:Q100").Copy
.Range("A1").PasteSpecial xlPasteValues
.Range("A1").PasteSpecial xlPasteFormats
.Range("A1").PasteSpecial xlPasteColumnWidths
.Range("C:D").EntireColumn.AutoFit
.Range("F:H").EntireColumn.AutoFit
.Range("K:M").EntireColumn.AutoFit
.Range("O:Q").EntireColumn.AutoFit
.Range("N:N").EntireColumn.Hidden = True
End With
tsWorkbook.Close False
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

 

[RedOctoberKnight]

yes.

 

[Fluff]

Ok how about

Public Sub Copy_Range_From_Truck_Schedules()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim tsWorkbook As Workbook

Set tsWorkbook = Workbooks.Open(ThisWorkbook.Path & "\truckschedulesdualsides.xlsx")
With ThisWorkbook.ActiveSheet
   If Not ShtExists(.Name, tsWorkbook) Then
      MsgBox "Sheet doesn't exist"
      Exit Sub
   End If
   .Range("A1:Q100").UnMerge
   tsWorkbook.Worksheets(.Name).Range("A1:Q100").Copy
   .Range("A1").PasteSpecial xlPasteValues
   .Range("A1").PasteSpecial xlPasteFormats
   .Range("A1").PasteSpecial xlPasteColumnWidths
   .Range("C:D").EntireColumn.AutoFit
   .Range("F:H").EntireColumn.AutoFit
   .Range("K:M").EntireColumn.AutoFit
   .Range("O:Q").EntireColumn.AutoFit
   .Range("N:N").EntireColumn.Hidden = True
End With
tsWorkbook.Close False
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

and add this to a normal module

Public Function ShtExists(ShtName As String, Optional wbk As Workbook) As Boolean
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function

 

[RedOctoberKnight]

perfect. Thank you so much!!

 

[Fluff]

You're welcome & thanks for the feedback.