Results 1 to 13 of 13

Combine Data from Multiple Sheets into one Sheet

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-26-2006
    Posts
    141

    Combine Data from Multiple Sheets into one Sheet

    I have a workbook with a sheet for each month of the year. I want to combine column A from the all the monthly Sheets but not the other three sheets (Event Totals, Monthly Totals, and Vendors 2017) into one sheet on the Event Totals sheet. I have included my code below. I am currently getting an error on If sh.Name <> "Event Totals" Or "Monthly Totals" Or "Vendors 2017" Then as well as Last = LastRow(sumSht). I would also like to add some code to the CopyRng to only copy the cells in column A from the monthly sheets which have data.

    Sub CopyRangeFromMultiWorksheets()
        Dim sh As Worksheet
        Dim sumSht As Worksheet
        Dim i As Long
        Dim CopyRng As Range
        
        Set sumSht = Sheets("Event Totals")
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
                For Each sh In ActiveWorkbook.Worksheets
                If sh.Name <> "Event Totals" Or "Monthly Totals" Or "Vendors 2017" Then
    
                Last = LastRow(sumSht)
    
                Set CopyRng = sh.Range("A1:A25")
                
                If Last + CopyRng.Rows.Count > sumSht.Rows.Count Then
                    MsgBox "There are not enough rows in the sumSht"
                    GoTo ExitTheSub
                End If
    
                CopyRng.Copy
                With sumSht.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With
                
                
        End If
        Next
    
    ExitTheSub:
    
        Application.Goto sumSht.Cells(1)
    
        sumSht.Columns.AutoFit
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Last edited by billykiller05; 12-30-2016 at 05:15 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Combine data in multiple sheets to one sheet
    By RDevi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2016, 10:44 AM
  2. Replies: 0
    Last Post: 10-12-2015, 10:02 AM
  3. Combine columns data from multiple sheets to existing master sheet via cell value
    By 253.Asmo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2015, 12:13 PM
  4. Combine multiple sheets into one sheet
    By cherryab888 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2015, 05:57 PM
  5. [SOLVED] Formula to combine data from multiple sheets to a summary sheet
    By laurann in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-31-2013, 02:38 PM
  6. Replies: 7
    Last Post: 10-19-2011, 08:33 AM
  7. Combine Data from Multiple Sheets to Single Sheet
    By mpquin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2009, 06:06 PM

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