Loop taking hours to copy values

[jakel27]

I have a 2 Workbooks A and B. The following loop normally sits in workbook B and it takes a couple of seconds to run.

But when I try to run this function in Workbook B by having the code sit in Workbook A, it ends up taking over an hour.

Can someone explain why and is there maybe a better way for me to copy data?

The follow is what I want...

Workbook B is a database updated daily. I want code from Workbook A to search through Workbook B database ("SEC Sheet delay data") for order numbers associated with yesterdays date, find all other orders in the database with the same order number from previous dates before that, then copy and paste all selected orders onto the 'Timestamp' worksheet in Workbook B.

I am currently doing this with my code by highlighting yesterdays orders in the database red, then search database for any orders with the same values of those highlighted red, and highlight them red too.
Then copy and paste these selected orders onto 'Timestamp'.

If there is a better way than to copy and paste without highlighting as well, that would be greatly appreciated.

----------------------------------------------------------------------------------------------------------------

Sub updateData()
  
'Copy and Paste Highlighted
Dim TransIDField As Range
Dim TransIDCell As Range
Dim ATransWS As Worksheet
Dim HTransWS As Worksheet
 
With ThisWorkbook
    Set ATransWS = .Worksheets("SEC Sheet delay data")
    Set TransIDField = ATransWS.Range("C12000", ATransWS.Range("C12000").End(xlDown))
    Set HTransWS = .Worksheets("Timestamp")
End With
 
ATransWS.Activate
 
 'Highlight
Dim R As Range, WOCell As Range, datetoday As Range
Dim WO As String, Var As String
Set R = Range("C12000", Range("C12000").End(xlDown))
Set datetoday = Range("E12000", Range("E12000").End(xlDown))

Columns(3).Interior.Color = xlNone

For Each Cell In datetoday

    If Cell.Value = Date - 1 Then                           'Highlight all WO from yesterday
        Cell.Offset(0, -2).Interior.Color = RGB(255, 0, 0)
        Var = Cell.Offset(0, -2).Value
       
        For Each WOCell In R                                'Highlight all cells with same WO different dates
            If InStr(WOCell.Value, Var) > 0 Then
                WOCell.Interior.Color = RGB(255, 0, 0)
            End If
        Next WOCell
    End If
   
Next


HTransWS.Range("A2:P" & Rows.Count).Clear

For Each TransIDCell In TransIDField

    If TransIDCell.Interior.Color = RGB(255, 0, 0) Then
       
        TransIDCell.Resize(1, 16).Copy Destination:= _
            HTransWS.Range("A1").Offset(HTransWS.Rows.Count - 1, 0).End(xlUp).Offset(1, 0)                                                 
           
   End If                                                                                                                                                   'WHEN EXECUTING MACRO IN WORKBOOK B FROM WORKBOOK A, THE CODE TAKES AGES AT THIS 'END IF'


End Sub

------------------------------------------------------------------------------

TIA

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

 

[jakel27]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

I never noticed that. Thank you, a lot easier to read now

 

[Peter_SSs]

You're welcome. It also makes it easier for helpers to copy/paste with the indentation format using the icon at the top-right of that code window that is created.

 

[jakel27]

*Bump* because additional information...

I have 2 macros located in Worksheet A which I call to perform functions in Worksheet B.

If I execute each code individually, they run fine and fast, such as...
If I run Macro1 by itself it takes seconds
If I run Macro2 by itself it takes seconds

But if I do the following it takes hours...

Sub Test()
Call Macro1
Call Macro2
End sub

Or if I copy the code from Macro2 into Macro1 to make 1 code it takes hours.

The code above is in Macro2

 

[jakel27]

After days of questioning my code I found my problem...

Macro1 activated a really laggy worksheet which I generally try to avoid leaving open in the background and when executing Macro2 it caused loops to take ages by just sitting in the background.

All I had to do was select a different less laggy worksheet to leave open in the background and Macro2 loops took seconds again...

This was such a pain to find, so I'm leaving this as a solution for others. This could not be solved by anyone else but me in this case as it's pretty unique.