+ Reply to Thread
Results 1 to 3 of 3

Loop worksheets Copy range and Paste in LastUsedCell in first worksheet, delete Worksheet

Hybrid View

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

    Loop worksheets Copy range and Paste in LastUsedCell in first worksheet, delete Worksheet

    Hi,

    I have a loop to copy a data range (find last row for data because it always differs) from worksheet 2, paste it in the "Summary" worksheet (next empty row) and delete ws2, then go on to worksheet 3 and repeat.
    I'm finding an error on the paste line :" Method 'Range' of Object '_Worksheet' failed.

    This is the current code

    'Add all tabs to Summary and Delete Tabs
    Sub AddToSummary()
    Dim lRow As Long
    Dim ws As Worksheet
    Dim pasteSheet As Worksheet
    Set pasteSheet = Worksheets("Summary")
    
        For Each ws In ActiveWorkbook.Worksheets
        lRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            If ws.Name <> "Summary" Then
                ws.Range("A15:AD" & Range("A" & Rows.Count).End(xlDown).Row).Copy
                pasteSheet.Range(lRow).PasteSpecial
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws
    Last edited by Simone Fick; 07-24-2017 at 10:01 AM.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,525

    Re: Loop worksheets Copy range and Paste in LastUsedCell in first worksheet, delete Worksh

    A couple of things,

        Dim lRow As Long
        Dim ws As Worksheet
        Dim pasteSheet As Worksheet
        Set pasteSheet = Worksheets("Summary")
    
        For Each ws In Sheets
            lRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
            If ws.Name <> pasteSheet.Name 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

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

    Re: Loop worksheets Copy range and Paste in LastUsedCell in first worksheet, delete Worksh

    Excellent, thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy and Paste from Workbooks to Worksheet Loop
    By David Harris 1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-23-2015, 04:38 AM
  2. VBA Match Cell Range to Worksheet Name and copy/paste to respective worksheet
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2014, 04:06 PM
  3. [SOLVED] VBA Loop through each worksheet except two and then copy paste
    By akulka58 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-30-2014, 06:45 AM
  4. Match Cell Value to Worksheet Name Then Copy/ Paste Range to Worksheet
    By matt2072 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2014, 01:23 PM
  5. Replies: 3
    Last Post: 09-14-2013, 06:45 PM
  6. Copy Range from Worksheet 2, Paste to Next Empty Row Worksheet 1
    By oOarthurOo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2010, 04:07 PM
  7. Loop to copy rows and paste into worksheet
    By damien_carr1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2009, 06:10 PM

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