Stumped on my Summary Sheet

[jthomas3029]

I copied a Mr. Excel VBA formula, Copy Wrksheet range in VBA consolidation. It has worked well until I added more data into the rows on my worksheets. The individual worksheets are displaying the correct calculations but the summary sheet does not. The summary sheet draws upon a formula from a previous row within the summary sheet even though it reflects the correct worksheet formula. To add to it the first worksheet is always correct on the summary sheet no matter how many new rows of data I add.

 

[MARK858]

Welcome to the board, try changing

Set rngCopy = wrkSheet.Range("A2:U30")

to

Set rngCopy = wrkSheet.Range("A2:U" & wrkSheet.Range("A" & Rows.Count).End(xlUp).Row)

In future please can you post VBA code directly in the thread (wrapped in code tags) as we can't copy and paste from an image.

 

[jthomas3029]

Welcome to the board, try changing

Set rngCopy = wrkSheet.Range("A2:U30")

to

Set rngCopy = wrkSheet.Range("A2:U" & wrkSheet.Range("A" & Rows.Count).End(xlUp).Row)

In future please can you post VBA code directly in the thread (wrapped in code tags) as we can't copy and paste from an image.

Thank you for the timely suggestion; however, the issue is still present even with the modification.

 

[MARK858]

First of all with your XL2BB image you need to unzip the file.
Second what do you have in column A of each sheet i.e. do you have data and does the data go down to the last row in each sheet.

 

[jthomas3029]

Private Sub Worksheet_Activate()

    'Consolidates data from the range A2:N35 for every tab except the one it's part of.

    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
    
    strConsTab = ActiveSheet.Name 'Consolidation sheet tab name based on active tab.
        
    If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
        If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
            Sheets(strConsTab).Range("A2:S" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        End If
    End If
        
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
    
    If wrkSheet.Name <> strConsTab And _
            wrkSheet.Name <> "Tables" Then
        
                
            Set rngCopy = wrkSheet.Range("A2:U" & wrkSheet.Range("A" & Rows.Count).End(xlUp).Row)
            lngPasteRow = Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row + 1
            rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
            Application.CutCopyMode = False
            
        End If
    
    Next wrkSheet
    
    Application.ScreenUpdating = True
    
    MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"

End Sub

 

[jthomas3029]

Every worksheet has a column A with the data beginning on A7 and ending on P7. Each worksheet does contain formulated rows without data in preparation of the data being entered.

 

[MARK858]

Each worksheet does contain formulated rows without data in preparation of the data being entered

That does make a difference but in the opposite way (i.e. it should be copying more rows than needed).
To adjust for that try the code below and then we will see where we are.

Private Sub Worksheet_Activate()

    'Consolidates data from the range A2:N35 for every tab except the one it's part of.

    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
    
    strConsTab = ActiveSheet.Name                'Consolidation sheet tab name based on active tab.
        
    If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
        If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
            Sheets(strConsTab).Range("A2:S" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        End If
    End If
        
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
    
        If wrkSheet.Name <> strConsTab And _
           wrkSheet.Name <> "Tables" Then
        
                
            Set rngCopy = wrkSheet.Range("A2:U" & wrkSheet.Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row)
            lngPasteRow = Sheets(strConsTab).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
            rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
            
        End If
    
    Next wrkSheet
    
    Application.ScreenUpdating = True
    
    MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"

End Sub

 

[jthomas3029]

Thank you.

Private Sub Worksheet_Activate()

    'Consolidates data from the range A2:N35 for every tab except the one it's part of.

    Dim wrkSheet As Worksheet
    Dim rngCopy As Range
    Dim lngPasteRow As Long
    Dim strConsTab As String
    
    strConsTab = ActiveSheet.Name                'Consolidation sheet tab name based on active tab.
        
    If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
        If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
            Sheets(strConsTab).Range("A2:S" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        End If
    End If
        
    Application.ScreenUpdating = False
            
    For Each wrkSheet In ActiveWorkbook.Worksheets
    
        If wrkSheet.Name <> strConsTab And _
           wrkSheet.Name <> "Tables" Then
        
                
            Set rngCopy = wrkSheet.Range("A2:U" & wrkSheet.Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row)
            lngPasteRow = Sheets(strConsTab).Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).Row + 1
            rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
            
        End If
    
    Next wrkSheet
    
    Application.ScreenUpdating = True
    
    MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"

End Sub

I appreciate your work on my behalf. We are not quite there yet.

 

[jthomas3029]

What is confusing to me that it works as designed on the first worksheet and the first few rows of next worksheets. I just switched the order of the worksheets and voila the first one is correct. I need to replicate the success with the first worksheet to the others. js...

 

[MARK858]

Are you 100% sure that every sheet has data in Column A?