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

 

[Fluff]

How about

Dim tsWorkbook As Workbook
Dim FullNme As String
FullNme = ThisWorkbook.Path & "\truckschedulesdualsides.xlsx"
If Dir(FullNme) = "" Then
   MsgBox "Not created"
   Exit Sub
End If
Set tsWorkbook = Workbooks.Open(FullNme)
With ThisWorkbook.ActiveSheet

 

[RedOctoberKnight]

Sorry, self taught user and I'm still new to VBA. Do I just copy and paste that over my old code? or how do I go about integrating that into my code?

 

[Fluff]

Just add the part in blue to your code as shown and change your code as shown by the part in red

Dim tsWorkbook As Workbook
Dim FullNme As String
FullNme = ThisWorkbook.Path & "\truckschedulesdualsides.xlsx"
If Dir(FullNme) = "" Then
   MsgBox "Not created"
   Exit Sub
End If
Set tsWorkbook = Workbooks.Open(FullNme)
With ThisWorkbook.ActiveSheet

 

[RedOctoberKnight]

So this is how I put it together but I don't receive a msgbox and I get an error with "tsWorkbook.Worksheets(.Name).Range("A1:Q100").Copy"

Public Sub Copy_Range_From_Truck_Schedules()

 Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim tsWorkbook As Workbook
Dim FullNme As String
FullNme = ThisWorkbook.Path & "\truckschedulesdualsides.xlsx"
If Dir(FullNme) = "" Then
MsgBox "Not created"
Exit Sub
End If

Set tsWorkbook = Workbooks.Open(FullNme)
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

 

[Fluff]

Does the workbook open?

 

[RedOctoberKnight]

yes, I get an error 9 msg.

 

[Fluff]

In that case you wont get the message box as the file exists.

 

[RedOctoberKnight]

sorry, I should be more specific and tell you that the "truckschedules" has multiple sheets that have dates for sheet names. Currently, my code opens up the truckschedules, looks for the same sheet name as the current sheet and copies the data to that sheet. I want it to open up the truckschedules and if the truckschedules don't have a sheet with the same name as the current sheet, then give the "not created" msg box. If the truckschedules do have a sheet with the same name as the current sheet, then copy the data over to the current sheet.

 

[Fluff]

So you want to see if a sheet exists, rather then the workbook?