Results 1 to 6 of 6

Create a summary of different worksheets, leaving out empty cells, starting from row 12

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-05-2021
    Location
    Netherlands
    MS-Off Ver
    Microsoft Excel 365 (version 2022, Build 14931.20764)
    Posts
    182

    Create a summary of different worksheets, leaving out empty cells, starting from row 12

    Dear Excel-specialists,

    I have a short question.
    I have 1 master workbook that contains several worksheets, including a Summary worksheet.
    The header for each worksheet is the same (from row 1:11). This also counts for the Summary sheet.

    What I like to achieve is:
    - walk through all worksheets in the workbook (could have different names) except the worksheet with the name "Summary"
    - Copy all data that lives (per column A:Z) in all worksheets starting from row 12 until there is no data found
    - Make a summary of all collected data, leaving empty cells out and put this in the summary worksheet also starting from row 12

    Result: A summary of all the data from the worksheets starting from row 12.

    My problem: The script gives not the desired result in the summary worksheet.
    Column D stays empty (priority 3) and the word "Integration complexity" Cell H9 is also copied.

    Can somebody help me out please?
    Code an sheet is included.

    Thank you in advance!

    Best regards,

    Hans

    Sub CopyWorkSheetDataWithoutOverwritingHeader()
       Dim summarySheet As Worksheet
       Dim dataSheet As Worksheet
       Dim lastRow As Long
       Dim summaryRow As Long
       Dim columnRange As Range
       Dim cell As Range
       Dim startRow As Long
       
       Set summarySheet = ThisWorkbook.Sheets("Summary")
       startRow = 12
       
       summarySheet.Activate
       summarySheet.Visible = True
       
       summarySheet.Range("A" & startRow & ":Z3500").ClearContents ' Clear rows
       
       For Each dataSheet In ThisWorkbook.Sheets
          If dataSheet.Name <> "Summary" Then
             ' Loop through each column from B to Z
             For Each columnRange In dataSheet.Range("B:Z").Columns
                summaryRow = summarySheet.Cells(summarySheet.Rows.Count, columnRange.Column).End(xlUp).Row + 1
                lastRow = dataSheet.Cells(dataSheet.Rows.Count, columnRange.Column).End(xlUp).Row
                For Each cell In dataSheet.Range(columnRange.Cells(startRow), columnRange.Cells(lastRow))
                   If Not IsEmpty(cell) And Not IsError(cell.Value) Then
                      If InStr(1, cell.Value, "Priority") = 0 Then
                         summarySheet.Cells(summaryRow, columnRange.Column).Value = cell.Value
                         summaryRow = summaryRow + 1
                      End If
                   End If
                Next cell
             Next columnRange
          End If
       Next dataSheet
      
       summarySheet.Range("B:ZZ").EntireColumn.AutoFit
    End Sub
    Attached Files Attached Files
    Last edited by NewBee_HS; 10-20-2023 at 10:18 AM. Reason: Markup

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA Code to Add new worksheets & add a line to a summary tab with the correct data
    By stemcg93 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2020, 02:44 PM
  2. [SOLVED] Index/match giving correct results, wrong results & #N/A results...sometimes
    By mrteater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2019, 10:41 AM
  3. Replies: 4
    Last Post: 02-06-2016, 12:51 PM
  4. Formula suddenly displaying not correct results on summary sheet
    By canadianjameson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-17-2015, 08:11 PM
  5. [SOLVED] Not getting correct if(and results
    By Groovicles in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2014, 03:32 PM
  6. [SOLVED] added sum if correct lead to complete statement ?
    By Diandude in forum Excel General
    Replies: 2
    Last Post: 06-15-2012, 04:35 PM
  7. Execute Macro once Microsoft Query results complete
    By gwa3000 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-19-2005, 04:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1