VBA macro contains - Loop Copy Filter Paste Remove Duplicates - modify

[yazansalsa]

Hello,

I am working on a macro that would remove the duplicates from sheet "RT_Modified" based on a criteria on sheet "To check with". So basically the macro would pick the first criteria from sheet "To check with" copy the criteria onto sheet "RT_Modified" and paste its value. In this case I would use advanced filter to filter on this criteria, and once Its done I would copy the results from sheet "RT_Modified" to update - paste into sheet "To check with". after I paste the date I would remove the duplicates from the lest that I have pasted. this is the whole purpose of this macro.

For the filter the macro has to skip the first column - criteria in "To check with" sheet.

I am having difficulty with coding the loop in order to take the next set of criteria and pasting it under its corresponding range.

At a later step I know that I will have a problem with the filter - but I hope we can do this one step at a time.

This is the code that I am using, but its not working.

Please note that I am a VBA noob. I like to think that I know excel - but I think I try to play with things that I cant handle sometime - curiosity kills! - that is why I need help and am not afraid to ask question.

Sub Modify_RT()

Dim v As Integer, j As Range, i As Range
Sheets("To check with").Select
v = Cells("1", Columns.Count).End(xlLeft).Row '/ count number of columns
col = Cells(Rows.Count, v).End(x1up).Row '/ count number of rows for the range
j = Range("1" & v)

    Do Until j.Cells(1, v).Value = "" '/ stop when you get to the last cell that has nothing
    
        Sheets("RT_Modified").Select
        Range("B2").Select
        
            For Each i In j
    
        ActiveCell.Formula = "='To check with'!range(cells(1,v))" '/ adjust the formula - criteria for the filter
        
            a.Range.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("B1:B2"), Unique:=False '/ filter
    
            a.Range.Select
            Range(Selection, Selection.End(xlDown)).Select 
            Selection.Copy
            
            Sheets("To check with").Select
            Range(Cells(3, v)).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            ActiveSheet.Selection.RemoveDuplicates Columns:=1, Header:=xlNo '/ remove dublicates
            
            i = i + 1 '/ next criteria

      Loop
              
   next
    
End Sub

this is a sample of sheet "RT_Modified"

Level 3</SPAN>	Level 4</SPAN>	Level 5</SPAN>	Level 6</SPAN>	Level 7</SPAN>	Level 8</SPAN>
NSE_LATAM</SPAN>
Level 3</SPAN>	Level 4</SPAN>	Level 5</SPAN>	Level 6</SPAN>	Level 7</SPAN>	Level 8</SPAN>
CANADA</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA100</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA100</SPAN>	SUB_GMA100</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA101</SPAN>	SUB_GMA101</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA101</SPAN>	SUB_GMA101</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA102</SPAN>	SUB_GMA102</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA102</SPAN>	SUB_GMA102</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA103</SPAN>	SUB_GMA103</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA103</SPAN>	SUB_GMA103</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA104</SPAN>	SUB_GMA104</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA104</SPAN>	SUB_GMA104</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA105</SPAN>	SUB_GMA105</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA105</SPAN>	SUB_GMA105</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA106</SPAN>	SUB_GMA106</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA106</SPAN>	SUB_GMA106</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA107</SPAN>	SUB_GMA107</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA107</SPAN>	SUB_GMA107</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA108</SPAN>	SUB_GMA108</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA108</SPAN>	SUB_GMA108</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA109</SPAN>	SUB_GMA109</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA109</SPAN>	SUB_GMA109</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA110</SPAN>	SUB_GMA110</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	GIS_T1</SPAN>	GIS_GMA</SPAN>	GMA110</SPAN>	SUB_GMA110</SPAN>

<TBODY>
</TBODY>

this is a sample of the "To check sheet" :

LEVEL3</SPAN>	CANADA</SPAN>	USA</SPAN>	UNITED_KINGDOM</SPAN>	NSE_LATAM</SPAN>	CE_EUROPE</SPAN>	FRANCE</SPAN>	ASIA_PACIFIC</SPAN>	CORPORATE</SPAN>
CANADA</SPAN>	GIS_T1_DMS</SPAN>	FEDERAL_GROUP</SPAN>	UNITED_KINGDOM_L4</SPAN>	NSE_LATAM_L4</SPAN>	CE_EUROPE_L4</SPAN>	FRANCE_OPERATIONS</SPAN>	ASIA_PACIFIC_L4</SPAN>	CORPORATE_EVP</SPAN>
USA</SPAN>	GMA_PSC</SPAN>	US_EM</SPAN>				EVP_FRANCE</SPAN>		CORPORATE_OTHER</SPAN>
UNITED_KINGDOM</SPAN>	QUEBEC</SPAN>	GIS_USA</SPAN>
NSE_LATAM</SPAN>	NCR</SPAN>	EVP_USA</SPAN>
CE_EUROPE</SPAN>	GTA_MG</SPAN>
FRANCE</SPAN>	ATLANTIC</SPAN>
ASIA_PACIFIC</SPAN>	WEST_CANADA</SPAN>
CORPORATE</SPAN>	OSS</SPAN>
	CSB</SPAN>
	CAN_FX_RISK</SPAN>
	CANADA_EVP</SPAN>

<TBODY>
</TBODY>

Any suggestions are welcome.

Thank you in advance.