Unmerge Cells and Get sheet name & workbook name in Columns

[nkms143]

Hi,
I'm fighting with a workbook which have many sheets with merged cells. Now What i want is to unmerge all cells in each sheet and sort as per Column "A" and Sheet name should appear in Column "M" and Workbook Name should appear in Column "N". I tried to write a macro for unmerging and Sorting, but i'm failing to select the range. Its giving run time error.

Any Suggestions will be helpful. I'm herewith uploading the test workbook with macro.

 

[Hui]

Try the following code:

Sub MergeToUnmerge()
'
Dim wksht As Worksheet

For Each wksht In ActiveWorkbook.Worksheets
  
  wksht.Select

  With wksht.Range("A4:R20").Select
  Selection.UnMerge
  Rows("3:24").EntireRow.AutoFit
  
  wksht.Sort.SortFields.Clear
  
  Range("A3:N24").Select
  ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Clear
  ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add _
  Key:=Range("A3:A21"), _
  SortOn:=xlSortOnValues, _
  Order:=xlAscending, _
  DataOption:=xlSortNormal
  
  With ActiveWorkbook.Worksheets(wksht.Name).Sort
  .SetRange Range("A3:N21")
  .Header = xlGuess
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
  End With
  
  Range("M4").FormulaR1C1 = wksht.Name
  Range("N4").FormulaR1C1 = ActiveWorkbook.Name
  Range("M4:N4").Copy Range("M5:N21")

  End With
  
  Next wksht
  
End Sub

 

[nkms143]

Thanks Hui Sir. It Worked.