Check condition which involves matching filtered tables and copying it in new sheet

[amrita17170909]

I have a filtered table named "Table 3".

I have to match filtered values from column A of "Table 3" to column A of "Table 2" and if the condition is met copy the entire row to a new sheet named "Table 4"

I have tried the below code without any success

Sub Copy_data_to_report()

Application.ScreenUpdating = False

lastRow = Worksheets("Table 3").Cells(Rows.Count, 1).End(xlUp).Row

nextRow = 2
      
        For thisRow = 1 To lastRow
      
           If Worksheets("Table 3").Cells(thisRow, 1).Value = Worksheets("Table 2").Cells(thisRow, 1).Value Then
         
               Worksheets("Table 2").Rows(thisRow).Copy

               Worksheets("Table 4").Activate

               B = Worksheets("Table 4").Cells(Rows.Count, 1).End(xlUp).Row

               Worksheets("Table 4").Cells(B + 1, 1).Select

               Worksheets("Table 4").Paste
             
               nextRow = nextRow + 1
             
            End If
                  
   Next

Application.CutCopyMode = False

End Sub

 

[DanteAmor]

Do you have table3 filtered and you also have table2 filtered?

Do you want to search each of the visible cells on table3 in each of the visible cells on table2, that is, that the value of cell A4 in table3 can match the value of cell A10 in table2?

Then copy the record from table2 to table4.

If the above is not correct, then you could explain with a example what you have and what you expect from the result.

 

[amrita17170909]

Hi Dante,

I only have the Table 3 filtered and table 2 is without filtering.


And yes I only want to search the visible cells on table 3 and if it matches then copy it across to table 4.


Thanks

 

[amrita17170909]

I wrote the below code and it works if there is no filtering in Table 3

Sub Generate_table4()

LastRow = Worksheets("Table 3").Cells(Rows.Count, 1).End(xlUp).Row

For thisRow = 5 To LastRow

For Thisrow2 = 5 To LastRow

If Worksheets("Table 3").Cells(Thisrow2, 1).Value = Worksheets("Table 4 ").Cells(thisRow, 1).Value Then
                                     
               Worksheets("Table 3").Rows(thisRow).Copy
       
               Worksheets("Table 4 ").Activate
       
               B = Worksheets("Table 4 ").Cells(Rows.Count, 1).End(xlUp).Row
       
               Worksheets("Table 4 ").Cells(B + 1, 1).Select
       
               Worksheets("Table 4 ").Paste
              
             End If
   Next Thisrow2
  
   Next thisRow
            
End Sub

 

[DanteAmor]

Check if this is what you need.

Sub Generate_table4_v2()
  Dim sh3 As Worksheet, sh4 As Worksheet, f As Range, r As Range, lr As Long, i As Long
  Set sh3 = Sheets("Table 3")
  Set sh4 = Sheets("Table 4")
 
  lr = sh3.Cells(Rows.Count, 1).End(xlUp).Row
  Set r = sh3.Range("A" & lr + 1)
  For i = 5 To lr
    If sh3.Range("A" & i).EntireRow.Hidden = False Then
      Set f = sh4.Range("A:A").Find(sh3.Range("A" & i).Value, , xlValues, xlWhole)
      If Not f Is Nothing Then
        Set r = Union(r, sh3.Range("A" & i))
      End If
    End If
  Next
  r.EntireRow.Copy sh4.Range("A" & Rows.Count).End(xlUp)(2)
End Sub

 

[amrita17170909]

Hey DanteAmor,

The above code doesn't work and doesn't produce any results.

Any ideas?

Thanks

 

[DanteAmor]

You can put the data with which you tried.
What you have on the "Table 3" sheet, what you have on the "Table4" sheet.
Explain what data from table3 should be copied to table4

You can upload the cell ranges with the following tool.

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[amrita17170909]

Hi DanteAmor,

Thanks for all your help and being patient with me as I am fairly new to the forum .

I am drowning in tables at the moment as somehow it is harder for me to work with filters. Since I have posted I have added another Table 3a which basically copies the values from Table 3 after a filter has been applied .

Table 3a has the following columns :

Customer Labels

Description

Group

BUDGET PROGRAM

Fund

Total Sum

Sum of 2020

Sum of 2021

Sum of 2022

Sum of 2023

Sum of 2024

Sum of 2025

Sum of 2026

Sum of 2027

Sum of 2028

Sum of 2029

Sum of 2030

Sum of 2031

Sum of 2032

Sum of 2033

Sum of 2034

Sum of 2035

Sum of 2036

Sum of 2037

Sum of 2038

Sum of 2039

Sum of 2040

Sum of 9999

202012230​

Project

Group 1

Name 1

1​

371979000​

0​

0​

0​

0​

0​

7323000​

18130000​

17985000​

28334000​

21868000​

23679000​

24110000​

24555000​

23554000​

25434000​

24381000​

25106000​

25218000​

26620000​

27411000​

28271000​

0​

I have approximately 27 lines in Table 3a with 14 unique customer labels.

Table 2 has 176 rows and exactly the same number of columns but more data.

The rows of data will change each month

Logic which has to be followed:

1. Pick the customer label from Table 3a and check if it exists in Table 2 if it does then all the rows with that customer label has to copied across to Table 4 . Additional check which needs to occur is if the customer label in table 3a has already been copied across to Table 4 as it will lead to duplication otherwise


I hope this makes more sense.

 

[DanteAmor]

But you said that the macro does not work, to check why it does not work you have to put the data with which you tried.
Then, I ask you again, put the data on the "table3" and "table4" sheets to review them.

You can upload the cell ranges with the following tool.

Upload an excel range:
XL2BB - Excel Range to BBCode

 

[amrita17170909]

Hey DanteAmor,

Unfortunately I am unable to share the actual data and at best can make reference to the columns.

Also my companies setting do not allow any external downloads.

Is there any other way ?

Thanks

Amrita