Thread: Help with updating Master Worksheet Coding

Hi guys i'm a beginner at VBA coding so I was wondering if I could get some help to see what I did wrong.

I have a code that takes all the subsequent sheets and updates the master sheet.

Problems/things I'm trying to do are:

1. Once it updates then it creates a table. I created a separate macro for it and have it called by the MergeWorksheet macro but it's not working anymore. It worked in the beginning and works on its own.

2. Would it be better to convert it straight into a pivot table as that is what I want. I was planning to put the dashboard and pivot table on a separate workbook.

Sub MergeSheets()
Const sRANGE = "A1:S100000"
Dim iSheet, iTargetRow As Long, oCell As Object, bRowWasNotBlank As Boolean
Dim iTop, iLeft, iBottom, iRight As Long
'Sheets(1).Select: Sheets.Add
Sheets(1).Select
Cells.Select
Selection.Clear
bRowWasNotBlank = True

For iSheet = 2 To ThisWorkbook.Sheets.Count: DoEvents
For Each oCell In Sheets(iSheet).Range(sRANGE).Cells: DoEvents
If oCell.Column = 1 Then
If bRowWasNotBlank Then iTargetRow = iTargetRow + 1
bRowWasNotBlank = False
End If

If oCell.MergeCells Then
bRowWasNotBlank = True
If oCell.MergeArea.Cells(1).Row = oCell.Row Then
If oCell.MergeArea.Cells(1).Column = oCell.Column Then
Sheets(1).Cells(iTargetRow, oCell.Column) = oCell
iTop = iTargetRow
iLeft = oCell.Column
iBottom = iTop + oCell.MergeArea.Rows.Count - 1
iRight = iLeft + oCell.MergeArea.Columns.Count - 1
Sheets(1).Range(Cells(iTop, iLeft), Cells(iBottom, iRight)).MergeCells = True
    End If
    End If
        End If
        
If Len(oCell) Then bRowWasNotBlank = True
Sheets(1).Cells(iTargetRow, oCell.Column) = oCell
    Next oCell
        Next
        Sheets(1).Activate

    Call CreateTable

End Sub
-----------------------------------
Sub CreateTable()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$S$10000"), , xlYes).Name = _
        "Table1"
        'No go in 2003
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub