Results 1 to 4 of 4

Copy data from all worksheets (except 2) paste in Summary and Delete sheets

Threaded View

  1. #1
    Registered User
    Join Date
    12-15-2014
    Location
    Wales, UK
    MS-Off Ver
    365
    Posts
    66

    Copy data from all worksheets (except 2) paste in Summary and Delete sheets

    Good day,

    The following code needs to Copy data from all Worksheets, except 2 specified, and paste underneath each other in the "Summary" tab, then delete the worksheets that the data was copied from.

    It works except it still copies the 2 specified not to and delete them as well. Please help.

    'Add all tabs to Summary and Delete sheets and hidden sheets
    
        Dim lRow As Long
        Dim ws As Worksheet
        Dim pasteSheet As Worksheet
        Set pasteSheet = Worksheets("Summary")
        
        Application.DisplayAlerts = False
        For Each ws In Worksheets
            If ws.Visible = xlSheetHidden Then
                ws.Delete
            End If
        Next ws
        Application.DisplayAlerts = True
    
        For Each ws In Sheets
            lRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            If ws.Name <> pasteSheet.Name AND ws.Name <> "KAM" AND ws.Name <> "OPS" Then
                ws.Range("A15:AD" & Range("A" & Rows.Count).End(xlUp).Row).Copy
                pasteSheet.Range("A" & lRow).PasteSpecial
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws
    Last edited by Simone Fick; 08-03-2017 at 09:59 AM. Reason: changed code to current error code

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2015, 12:00 PM
  2. string macro for a user input button, copy and paste data from all sheets to summary
    By Lawleypops in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2015, 11:17 AM
  3. string macro for a user input button, copy and paste data from all sheets to summary
    By Lawleypops in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2015, 11:09 PM
  4. [SOLVED] copy data from multiple worksheets and paste into summary sheet
    By forquaidian in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2014, 08:27 AM
  5. Copy and paste the most recent entered data from other worksheets to summary sheet
    By cvsrini in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2013, 04:51 AM
  6. [SOLVED] Copy multiple worksheets data & paste into blank columns in summary worksheet
    By guest99999 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-21-2013, 09:56 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