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.
Here is the macro then used for the filtering / copying:
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.
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: