UNMERGE RANGE VBA ERROR

[RedOctoberKnight]

Good Morning,

I have a worksheet that copies and pastes from another workbook. It works perfectly except for when I need to "pull" the information again. I get a merged cell error. So i'm trying to figure out how to unmerge range (A1:S100) and then have it copy the info.

I get an error with my following code. I'm not sure where to put the unmerge action.

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
tsWorkbook.Worksheets(.Name).Range("A1:S100").Copy
.Range("A1:S100").UnMerge
.Range("A1").PasteSpecial xlPasteValues
.Range("A1").PasteSpecial xlPasteFormats
.Range("H:J").EntireColumn.AutoFit
.Range("M:O").EntireColumn.AutoFit
      .Range("R:R").EntireColumn.ColumnWidth = 25

   End With
tsWorkbook.Close False
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Any help would be much appreciated and as always, thank you.

 

[Fluff]

Which sheet are the merged cells on, the copy sheet or the paste sheet?

 

[RedOctoberKnight]

Well the copy sheet has merged cells and my original code (just take out the .Range("A1:S100").UnMerge) worked fine. My problem is when I run the code again, I get a merged cell error which I assume is on my paste sheet. So I want to unmerge the cells on the paste sheet and then paste the info.

 

[Fluff]

What error number & message do you get & what line is highlighted when you click debug?

 

[RedOctoberKnight]

1004
pastespecial method of range class failed.

.Range("A1").PasteSpecial xlPasteValues is giving me the error now.

 

[Fluff]

Try moving the unmerge line before the copy line.

 

[RedOctoberKnight]

Thanks Fluff

 

[Fluff]

You're welcome.