Excel Select all visable sheets and cells, unmerge cells and fill down

0
votes

I have multiple workbooks with a varying number and names for sheets. The sheets have a number of merged cells that i need to unmerge and then copy the cell data. I've been unsuccessfully trying to blend a few different scripts together. Any help is greatly appreciated.

I'm trying to write a VB script that will do the following

  1. Select all visible sheets in the workbook
  2. Select all cells with data
  3. Unmerge any merged cells
  4. copy values down
         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count
               
Dim cell As Range, joinedCells As Range

For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

         Next I
excelvba