+ Reply to Thread
Results 1 to 3 of 3

Copy Paste Rows with Condition to Summary - VERY SLOWWW HELP!

Hybrid View

  1. #1
    Registered User
    Join Date
    03-13-2019
    Location
    Toronto
    MS-Off Ver
    365
    Posts
    1

    Copy Paste Rows with Condition to Summary - VERY SLOWWW HELP!

    My current code looks through 1 worksheet at a time copying all rows that have a numeric value in them and pasting them into a summary sheet.
    My only problem the code runs very slow. Here is a sample of the code below copying from two worksheets and pasting into the summary tab.

    Here's my current code:

    or i = 4 To b

    If IsNumeric(Worksheets("SIE 120V Panels").Cells(i, 1).Value) = True Then
    Worksheets("SIE 120V Panels").Rows(i).Copy
    a = Worksheets("BOM").Range("A65356").End(xlUp).Row
    Worksheets("BOM").Cells(a + 1, 1).Select
    ActiveSheet.Paste

    End If

    Next


    For i = 4 To c

    If IsNumeric(Worksheets("SIE 120V Breakers").Cells(i, 1).Value) = True Then

    Worksheets("SIE 120V Breakers").Rows(i).Copy
    a = Worksheets("BOM").Range("A65356").End(xlUp).Row
    Worksheets("BOM").Cells(a + 1, 1).Select
    ActiveSheet.Paste


    End If
    Next

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,692

    Re: Copy Paste Rows with Condition to Summary - VERY SLOWWW HELP!

    Please read the forum rules again. #2 Should be of interest to you.
    What are b (For i = 4 To b) and c (For i = 4 To c)?
    Are these very large, lots of data, sheets?

    If I understand your requirements right, does this work?
    Sub Maybe()
    Dim shArr, i As Long, j As Long, sh3 As Worksheet
    shArr = Array("SIE 120V Panels", "SIE 120V Breakers")
    Set sh3 = Worksheets("BOM")
        For i = LBound(shArr) To UBound(shArr)
            With Sheets(shArr(i))
                For j = 4 To .Cells(.Rows.Count, 1).End(xlUp).Row
                    If IsNumeric(.Cells(j, 1)) Then .Cells(j, 1).EntireRow.Copy sh3.Cells(sh3.Rows.Count, 1).End(xlUp).Offset(1)
                Next j
            End With
        Next i
    End Sub
    Last edited by jolivanes; 03-13-2019 at 10:09 PM. Reason: More questions

  3. #3
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    1,104

    Re: Copy Paste Rows with Condition to Summary - VERY SLOWWW HELP!

    Hello Jeamz02,

    Here's another method if it suits:-
    
    Sub CopyStuff()
              
              Dim ws As Worksheet, sh As Worksheet
              Set sh = Sheets("BOM")
              
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets
           If ws.Name <> "BOM" Then
                ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp)).SpecialCells(2, 1).EntireRow.Copy '----> Change A2 to A4?
                sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
           End If
    Next ws
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    End Sub
    I hope that this helps.

    Cheerio,
    vcoolio.

+ 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. [SOLVED] VBA- Copy and paste rows if condition is met
    By ANGIE90 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2018, 11:14 AM
  2. Copy rows from multiple workbooks and paste into summary workbook
    By elisas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2017, 03:45 PM
  3. Copy Paste Certain Rows on a condition Macro
    By patelchandresh13 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2016, 07:19 AM
  4. [SOLVED] Macro to copy data rows to a summary worksheet if the row satisfies a condition
    By cjfreeseman in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-05-2012, 03:33 AM
  5. Replies: 3
    Last Post: 11-29-2012, 06:05 AM
  6. Copy and Paste Rows For certain condition then loop
    By steve70070 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-14-2011, 06:05 PM
  7. Replies: 1
    Last Post: 09-07-2010, 06:23 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