+ Reply to Thread
Results 1 to 8 of 8

Sub Summarize macro code help needed.....

Hybrid View

ThaGonz Sub Summarize macro code help... 10-20-2012, 11:01 PM
dwint Re: Sub Summarize macro code... 10-21-2012, 12:29 AM
ThaGonz Re: Sub Summarize macro code... 10-21-2012, 08:49 AM
event21 Re: Sub Summarize macro code... 10-21-2012, 09:08 AM
ThaGonz Re: Sub Summarize macro code... 10-21-2012, 09:31 AM
ThaGonz Re: Sub Summarize macro code... 10-22-2012, 03:20 PM
ThaGonz Re: Sub Summarize macro code... 10-22-2012, 05:46 PM
event21 Re: Sub Summarize macro code... 10-22-2012, 07:30 PM
  1. #1
    Registered User
    Join Date
    09-02-2011
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2007
    Posts
    21

    Question Sub Summarize macro code help needed.....

    I have received great advice here in the past and I greatly appreciate it! I am hoping someone can help me with this issue as well...

    I have a workbook with multiple sheets that are being updated regularly by different users. I am using a Sub Summarize macro to copy all data from the individual sheets and paste it all on one summary sheet ("Data Summary") in the workbook so that management doesn't need to look at 20 different sheets to get the data.

    The code I am using works great and data from all the sheets populates correctly on the summary sheet but I need some help fine tuning 2 issues. I know it is probably a simple fix but for some reason i just can't figure it out.

    Issue 1: I need to copy data from all sheets starting at Row 3 instead of row 1 because the first 2 rows on all sheets are header rows. Right now when I run the Macro I end up with 20 identical header rows on the "Data Summary" sheet

    Issue 2: I need one sheets data to be excluded from the summary as it is an instructions page. The sheet name is "Storm" and I do not want its data included in the Summary sheet.

    Here is the code I am using any help would be greatly appreciated!

    Sub Summarize()
        Dim ws As Worksheet
        Dim lastRng As Range
        Application.ScreenUpdating = False 'speed up code
         
        ThisWorkbook.Sheets("Data Summary").Rows("2:65536").ClearContents 'clear
         
        For Each ws In ThisWorkbook.Worksheets
        
          Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(1, 0)
             
            Select Case ws.Name
            Case "Data Summary" 'exlude
                 'do nothing
            Case Else
                ws.Activate
                 
                 'copy data from individual sheets
                Range("A6556", Range("X65536").End(xlUp)).Copy lastRng
                 
            End Select
        Next
        Application.CutCopyMode = False 'clear clipboard
        Application.ScreenUpdating = True
        Sheets("Data Summary").Activate
    End Sub
    Last edited by jeffreybrown; 10-20-2012 at 11:04 PM. Reason: Please use code tags...Thanks.

  2. #2
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Sub Summarize macro code help needed.....

    try this code
    Option Explicit
    
    Sub Summarize()
        Dim ws As Worksheet
        Dim lastRng As Range
        
        Application.ScreenUpdating = False 'speed up code
        ThisWorkbook.Sheets("Data Summary").Rows("2:65536").ClearContents 'clear
        For Each ws In Worksheets
            If ws.Name <> "Data Summary" Then
                ws.Range("a3").Select
                Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy
                Sheets("Data Summary").Select
                Range("a3").Select
                If ActiveCell.Offset(1, 0) = "" Then
                    ActiveCell.Offset(1, 0).Select
                    Else
                    ActiveCell.End(xlDown).Offset(1, 0).Select
                End If
                ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
                Application.CutCopyMode = False
            End If
        Next ws
        Application.ScreenUpdating = True
        Sheets("Data Summary").Activate
    End Sub
    if its not working for you, please post your sample
    click the star if it solves your problem

  3. #3
    Registered User
    Join Date
    09-02-2011
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Sub Summarize macro code help needed.....

    dwint thank you for your response. I tried the code you supplied and I keep getting a 400 error. I have attached a sample sheet so you can see what I am trying to do. The sample sheet attached is currently using the original code I attached in the original post.

    The buttons at the top of the "Data Summary" sheet run the macros to populate and also clear that sheet.

    As you will see when you run the Summarize Macro it works great but as stated in the original post I don't want the "Storm" sheet data or the header row data (rows 2 and 3) from all the other included in the summary on the data summary sheet. So I have formatted all the data I don't want in the summary in BOLD RED with an X next to it. So basically when the summarize macro is run I should see no BOLD RED "X" data on in the data summary

    Let me know if there are any additional questions.

    Thanks again!!!
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Sub Summarize macro code help needed.....

    Hi -

    try this one.

    Regards,
    event
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-02-2011
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Sub Summarize macro code help needed.....

    Thanks Event, Exactly what I am looking for! Works great

    Thanks for everything guys! This one is solved

  6. #6
    Registered User
    Join Date
    09-02-2011
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Sub Summarize macro code help needed.....

    OK so I guess I was wrong...The code worked great in the sample workbook attached but for some reason when I add it to the actual workbook I need it for it still includes row 2 from all sheets in the summary but I need rows 1 and 2 excluded as those are header rows. I have attached the actual workbook I need it for if someone could take a look and let me know what I am doing wrong I would greatly appreciate it!

    When you open the work book a userform will appear. Check the "fiber data summary" box and that sheet will load. At the top righ of the sheet is the "summary button" that runs the summarize macro. Once you run it you will see all the header rows populate.

    The code is under ThisWorkbook in VBA.
    Sub Summarize()
        Dim ws As Worksheet
        Dim lastRng As Range, lr As Long
        Application.ScreenUpdating = False 'speed up code
         
        ThisWorkbook.Sheets("Data Summary").Rows("3:65536").ClearContents 'clear
         
        For Each ws In ThisWorkbook.Worksheets
            If InStr(ws.Name, "Instructions") = 0 And InStr(ws.Name, "Summary") = 0 Then
                Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(2, 0)
                      lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                      ws.Range("a3:x" & lr).Copy lastRng
            End If
        Next
        Application.CutCopyMode = False 'clear clipboard
        Application.ScreenUpdating = True
        Sheets("Data Summary").Activate
    End Sub
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-02-2011
    Location
    Boston, Ma
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Sub Summarize macro code help needed.....

    Never mind....I figured it out. Thanks all! Solved

  8. #8
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: Sub Summarize macro code help needed.....

    Hi -

    In the codes, you can find this line;

    Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(2, 0)
    change it to;

    Set lastRng = ThisWorkbook.Sheets("Data Summary").Range("A65536").End(xlUp).Offset(1, 0)
    to exclude the headers.

    Regards,
    event

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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