Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy data based on highlighted items

0

hello

I  have  a problem  with running speed for  this  code ,despite  of  the  data  are  small  not  huge . I tested  and  gives 0.4 !!!  .the  code  copy  data across all  of  the  sheets  based on highlighted item  in  column  B  into sheet COPY  . any  suggestion  make  it  more  fast  like 0.03 or 0.04  as   usaual with  simple  data?

Sub test()
    Dim ws As Worksheet, lr As Long, rCell As Range

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    If Application.CountA(sheet2.Range("A:A")) > 1 Then
        sheet2.Range("A2:H" & sheet2.Range("A" & Rows.Count).End(xlUp).Row).Delete xlUp
    End If

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "copy" Then
            lr = ws.Range("A" & Rows.Count).End(xlUp).Row
            For Each rCell In ws.Range("B2:B" & lr).Cells
                If rCell.Interior.ColorIndex <> -4142 Then
                    ws.Range("A" & rCell.Row & ":H" & rCell.Row).Copy
                    sheet2.Range("A" & sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
                    Application.CutCopyMode = False
                End If
            Next rCell
        End If
    Next
    With sheet2
        .Range("A:H").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
        .Range("A2") = 1
        .Range("A2").AutoFill .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row), xlFillSeries
        .Columns("A:A").NumberFormat = "General"
        .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Interior.ColorIndex = -4142
    End With

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub
Answer
Discuss

Discussion

Ali

Do you have many files (/other apps) open when you run this macro
John_Ru (rep: 6142) Mar 1, '22 at 6:41 pm
Hi John ,
no . do  me  favor please?  may  you  test  it  and  tell me how  is  the  speed? maybe  the  problem  from my PC.
Ali M (rep: 28) Mar 2, '22 at 3:03 am
Add to Discussion

Answers

0

Ali

I too get about the same result (run duration = 0.3671875 seconds). My life isn't so busy that I can't spare half a second!

Discuss

Discussion

do you mean this is normal?
I don't think so with comparison simple data ,shouldn't be
Ali M (rep: 28) Mar 3, '22 at 3:51 am
Ali, I can't say if it's "normal" or not since I don't time things which take less than a second and happen in a fraction of the time it would take manually. What you see as normal would depend on many things (file size, CPU speed, memory speed, concurrent apps etc.).

I'm not near my PC now but don't see any glaring errors in your code (though using copy/paste with recalculation of last row each time probably isn't very fast). 

Sorry I can't help. 
John_Ru (rep: 6142) Mar 3, '22 at 5:54 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login