Issue in my macro

Linzen

New Member
Joined
Oct 12, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am using a macro filtering with a "X" in a certain column and pasting in on another table, I need to do it on repetitive way with different filtering column.
It works until a certain column, then the macro is not going forward.
I have first a macro defining a format for the subject made from 2 cell.

Then I use the data as many time as I have patient.
If patient = 10, then it copies 10 times the same copying macro.

VBA Code:
Sub Test2PatientCalculator()

Dim Patient As String
Dim Format As String
Dim Subject As String
Dim Number As Integer

Patient = Worksheets("Annexe 2_1").Range("G12")
Format = Worksheets("Annexe 2_1").Range("L7")

'If Patient is different than 0, then start with value 1:
If Patient > 0 And Patient >= Number Then
    Number = 1
    Subject = Format & Number
    Call Copy_Macro1(Subject)
    Call Copy_Macro2(Subject)
    Call Copy_Macro3(Subject)
    Call Copy_Macro4(Subject)
    Call Copy_Macro5(Subject)
    Call Copy_Macro6(Subject)
    Number = Number + 1
Else: Patient = 0 Or Patient = Number
End If
'If Patient is Different or Less or equal than Number, then number is increasing by 1: Value 2
'Number=2 Patient=x x>=Number
If Patient > 0 And Patient >= Number Then
    Subject = Format & Number
    Call Copy_Macro1(Subject)
    Call Copy_Macro2(Subject)
    Call Copy_Macro3(Subject)
    Call Copy_Macro4(Subject)
    Call Copy_Macro5(Subject)
    Call Copy_Macro6(Subject)
    Number = Number + 1
Else: Patient = 0 Or Patient = Number

    End If
'Validated

'If Patient is Different or Less or equal than Number, then number is increasing by 1: Value 3
If Patient > 0 And Patient >= Number Then
    Subject = Format & Number
    Call Copy_Macro1(Subject)
    Call Copy_Macro2(Subject)
    Call Copy_Macro3(Subject)
    Call Copy_Macro4(Subject)
    Call Copy_Macro5(Subject)
    Call Copy_Macro6(Subject)
    Number = Number + 1
Else: Patient = 0 Or Patient = Number

    End If

Here is the macro then used for the filtering / copying:

VBA Code:
Sub Copy_Macro1(Subject As String)
a = Worksheets("Annexe 2_1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 22 To a
    
    If Worksheets("Annexe 2_1").Cells(i, 11).Value = "X" Then
    Worksheets("Annexe 2_1").Rows(i).Copy
    Worksheets("Annexe 2_1").Cells(i, 1) = Range("K21").Value
    Worksheets("Annexe 2_1").Cells(i, 2) = Subject
    Worksheets("Payment tracker").Activate
    b = Worksheets("Payment tracker").Cells(Rows.Count, 11).End(xlUp).Row
    Worksheets("Payment tracker").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Annexe 2_1").Activate
    
    End If
    
Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Annexe 2_1").Cells(1, 1).Select
    
    a = Worksheets("Annexe 2_1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 22 To a
    
    If Worksheets("Annexe 2_1").Cells(i, 12).Value = "X" Then
    Worksheets("Annexe 2_1").Rows(i).Copy
    Worksheets("Annexe 2_1").Cells(i, 1) = Range("L21").Value
    Worksheets("Annexe 2_1").Cells(i, 2) = Subject
    Worksheets("Payment tracker").Activate
    b = Worksheets("Payment tracker").Cells(Rows.Count, 12).End(xlUp).Row
    Worksheets("Payment tracker").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Annexe 2_1").Activate
    
    End If
    
Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Annexe 2_1").Cells(1, 1).Select
    
    
        a = Worksheets("Annexe 2_1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 22 To a
    
    If Worksheets("Annexe 2_1").Cells(i, 13).Value = "X" Then
    Worksheets("Annexe 2_1").Rows(i).Copy
    Worksheets("Annexe 2_1").Cells(i, 1) = Range("M21").Value
    Worksheets("Annexe 2_1").Cells(i, 2) = Subject
    Worksheets("Payment tracker").Activate
    b = Worksheets("Payment tracker").Cells(Rows.Count, 13).End(xlUp).Row
    Worksheets("Payment tracker").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Annexe 2_1").Activate
    
    End If
    
Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Annexe 2_1").Cells(1, 1).Select

a = Worksheets("Annexe 2_1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 22 To a
    
    If Worksheets("Annexe 2_1").Cells(i, 14).Value = "X" Then
    Worksheets("Annexe 2_1").Rows(i).Copy
    Worksheets("Annexe 2_1").Cells(i, 1) = Range("N21").Value
    Worksheets("Annexe 2_1").Cells(i, 2) = Subject
    Worksheets("Payment tracker").Activate
    b = Worksheets("Payment tracker").Cells(Rows.Count, 14).End(xlUp).Row
    Worksheets("Payment tracker").Cells(b + 1, 1).Select
    ActiveSheet.Paste
    Worksheets("Annexe 2_1").Activate
    
    End If
    
Next

Application.CutCopyMode = False
ThisWorkbook.Worksheets("Annexe 2_1").Cells(1, 1).Select

This is just a part of it. ( I do have 150 columns to be filtered that way).

I am desperate to have it working so if it would be easier to send you the file, please let me know.
When using this macro, it works with 45 filtering then it is not following the same logic anymore.

And yesterday I put a "X" in another column and it got paste above my table without follow any logic.

Thank you for your help.
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Forum statistics

Threads
1,224,816
Messages
6,181,138
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top