Results 1 to 8 of 8

Sub Summarize macro code help needed.....

Threaded View

  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.

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