Copy row based on criteria from one sheet to another

[roy00a4]

Dear Forumers,

I can't tell you all how happy I am to be a part of this forum and such great minds. I am not new to excel but to VBA. I would request all to help me with a macro that I am unable to create using record macro. I know thats very basic, but I am new to VBA and eager to learn.

I have a work book with two sheets. Sheet 1 - Master, Sheet 2 - Dump Filter. What I am trying to do is .. I get a data extract to generate a report. I have finished all formulations and everything else. In sheet "Dump Filter" I paste the data extract from Column B to Column BT. Doing this column A generates "Not In Master" when it doesn't find a data in the "Master" sheet.

I am trying to record a macro where in the rows with the criteria "Not In Master" from column A would be copied to sheet "Master". For sheet "Dump Filter" the criteria is in Column A and the data would be copied from colB to colBT where ever colA has "Not In Master". Then these copied fields would be pasted in the next blank row of sheet "Master" from colD to colBV.

I hope I am able to make sense of what I am trying to achieve. Any help is really appreciated and would be ever grateful..

Thank you in advance for all who helps and suggest.

Regards
roy00a4

 

[AlphaFrog]

[color=darkblue]Sub[/color] Filter_Not_In_Master()
    
    [color=green]'This macro assumes that your first row of data is a header row.[/color]
    [color=green]'The rows with the criteria "Not In Master" from column A would be copied to sheet "Master".[/color]
    [color=green]'For sheet "Dump Filter" the criteria is in Column A and the data would be copied from colB[/color]
    [color=green]'to colBT where ever colA has "Not In Master". Then these copied fields would be pasted in[/color]
    [color=green]'the next blank row of sheet "Master" from colD to colBV.[/color]
        
    [color=green]'Variables used by the macro[/color]
    [color=darkblue]Dim[/color] shSource [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] shDestination [color=darkblue]As[/color] Worksheet
    
    [color=darkblue]Set[/color] shSource = Sheets("Dump Filter")    [color=green]'Source worksheet[/color]
    [color=darkblue]Set[/color] shDestination = Sheets("Master")    [color=green]'Destination worksheet[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
                
    [color=green]'Autofilter column A for "Not In Master"[/color]
    shSource.UsedRange.AutoFilter Field:=1, Criteria1:="Not In Master"
    
    [color=green]' Copy filtered data from columns B:BT[/color]
    shSource.UsedRange.Columns("B:BT").Offset(1).Copy
    
    [color=green]'Paste the filtered data to Sheet "Master"  column D[/color]
    shDestination.Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Application.CutCopyMode = [color=darkblue]False[/color]
    
    [color=green]'Clean up[/color]
    shSource.AutoFilterMode = [color=darkblue]False[/color]
    Application.Goto shDestination.Range("A1")
    Application.ScreenUpdating = [color=darkblue]True[/color]
    MsgBox "Completed"
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[roy00a4]

Hi AlphaFrog,


I cant thank you enough. You have saved me from 5 days of hell. The code runs like a charm. AMAZING !!!!


Thank you so much for your help


Regards
roy00a4

 

[AlphaFrog]

You're welcome. Thanks for the feedback.