Macro is ignoring code

[Pauljj]

I have stepped through the code below and it jumps from

For r = 2 to total .........through to the bottom instance of ws4.activate

I can't for the life of me workout why ?

Any ideas ?

'Copy Super and Excpetional Clients who are yet to travel to  Tab 3

ws1.Activate



For r = 2 To total
   
clientdb = Cells(r, 1).Offset(0, 7).Value

durdate = Cells(r, 4) - Date

If durdate > 0 And clientdb = "SUPER CLIENT" Or durdate > 14 And clientdb = "EXCEPTIONAL CLIENT" Then


Range(Cells(r, 1), Cells(r, 18)).Copy
   
    
ws4.Activate

    lastrow = Cells(Rows.Count, 1).End(xlUp).Row + 1


    Cells(lastrow, 1).Activate

    ActiveCell.PasteSpecial xlPasteAll
    
    ws1.Activate
    
    End If
    
    
    
    Next r
    
    
    
'Sort bookings on TAB 3 by departure date order

ws4.Activate

 

[mrhstn]

Where do you define what 'total' is?

 

[Pauljj]

Where do you define what 'total' is?

A bit earlier in the code

Dim total As Integer
Dim r As Integer
Dim ws4 As Worksheet
Dim ws3 As Worksheet
Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim dept As Variant
Dim clientdb As String
Dim durdate As Long
Dim freq As Long
Dim Cat As Long

 

[mrhstn]

My apologies, I should have said where is the value of total set. Somewhere between the Dim and the For total should be set to a value (in your case greater than or equal to 2).

 

[Pauljj]

My apologies, I should have said where is the value of total set. Somewhere between the Dim and the For total should be set to a value (in your case greater than or equal to 2).

I hadn't in fact defined the value for 'r' as I didn't think I needed to. I added it in but it didn't seem to make any difference, assuming I have done it right

'Copy Super and Excpetional Clients who are yet to travel to  Tab 3

ws1.Activate

r = 2

For r = 2 To total
   
clientdb = Cells(r, 1).Offset(0, 7).Value

durdate = Cells(r, 4) - Date

If durdate > 0 And clientdb = "SUPER CLIENT" Or durdate > 14 And clientdb = "EXCEPTIONAL CLIENT" Then


Range(Cells(r, 1), Cells(r, 18)).Copy
   
    
ws4.Activate

    lastrow = Cells(Rows.Count, 1).End(xlUp).Row + 1


    Cells(lastrow, 1).Activate

    ActiveCell.PasteSpecial xlPasteAll
    
    ws1.Activate
    
    End If
    
    
    
    Next r
    
    
    
'Sort bookings on TAB 3 by departure date order

ws4.Activate

 

[Pauljj]

Ignore that mthstn, I have just realised you said 'total'

I have now done that and it does indeed sort the problem

Thanks very much for getting back to me and helping my hungover head out

Regards
Paul

 

[mrhstn]

You're very welcome.