help with on error resume next

Hi - can someone help me please. I have a number of columns that I wish to find in a spreadsheet so I repear the following on error resume next. But after it test's the cells.find it still copies the info and pastes it when I want it to ignore and to go to next test. Is there an easy way of doing this?

Many thanks

 On Error Resume Next
   
    test = Cells.Find(What:="RunID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
        If Not test = "" Then
        End If
       
    ActiveCell.Offset(1, 0).Select
    
    Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
    Selection.Copy
    Windows("Excel Spreadsheet Header Row.xlsx").Activate
    Cells.Find(What:="RunID", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
     Application.CutCopyMode = False

     
   ' next column and same run through as above
       
    Windows("PRE_amended.xlsx").Activate
    
     On Error Resume Next
    
    test = Cells.Find(What:="RunDate", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("Excel Spreadsheet Header Row.xlsx").Activate
    Cells.Find(What:="RunDate", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Activate
    If Not test = "" Then
    End If
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Ee ref next column
    
    Windows("Pre_amended.xlsx").Activate
    
     On Error Resume Next

hi romperstomper, is it a typo or sort of educational trick?

If Not test Is Nothing Then

instead of

If Not rcopy Is Nothing Then

hi leanne2011,

it should be:

For Each varItem In varFindItems

plus see post #4

Ok this is perfect - thank you. Just one problem though, in the first column it is going to the last row of data and copying and pasting but when it goes to the varFindItems its one row sort in the copy and paste function ?

not sure if i was clear - the first time the procedure is run and in the 1st varfinditem, the copy and paste is the exact rows ie activerows but when it reruns the procedure 2nd time and onwards its 1 row short in copying the data across.

Originally Posted by romperstomper

Be easier to say why with a sample workbook and all of your code.

Thanks for your help on this

Sub moduletwo()
     

     
     
        Workbooks.Open Filename:= _
        "N:\PAYROLL\Leanne\Example - Macro Work LB only\Excel Spreadsheet Header Row1.xlsx"
    Range("A2").Select
    
    Workbooks.Open Filename:= _
        "N:\PAYROLL\Leanne\Example - Macro Work LB only\PRE_amended.xlsx"
    
    
    Dim rCopy             As Range
   Dim rPaste            As Range
   Dim varFindItems
   Dim varItem

   ' array of items to find
   varFindItems = Array("RunID", "RunDate", "EeRef", "Name", "Dept", "CostCentre", "Branch", "StartDate", "LeaveDate", "TaxCode", "NINumber", "NILetter", _
   "TaxablePay", "NIableTP", "TotalNICs", "PreTaxAddDed", "PostTaxAddDed", "Basic Pay", "Back Pay", "Salary Adj", "Pension Uplift", "Car Allowance", "Holiday Pay", _
   "ESPP Benefit", "ESPP Refund", "Rsu Gain", "Option Gain", "Bonus", "High Perf Bonus", "MBO Bonus", "Net Bonus", "Referral Bonus", "Relocation Allowance", "PILON", _
   "Redundancy Gross", "Redundancy Nett", "Severance Gross", "Severance Nett", "Cycle Scheme", "Sal.Exchange", "Childcare", "Educ.Sacrifice", "Unpaid Leave", _
   "Maternity Pay", "TotalAbsencePay", "Nu.Per.Pension", "Healthcare", "ESPP", "ESPP Already Received", "RSU Gain Less Tax Witheld", "Rsu Ers Nic Adj", "Co Loan" _
   , "Option Gain Less Tax", "Option Gain Already Received", "Option Ers Nic Adj", "Advance Pay", "NegNetBf", "NegNetCf", "StudentLoan", "Tax", "NI", "AEO", _
   "NetPay", "ErNI", "PenEr")

   ' now process each item in the array
   For Each varItem In varFindItems
      Set rCopy = Cells.Find(What:=varItem, After:=ActiveCell, LookIn:=xlFormulas, _
                              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                              MatchCase:=True, SearchFormat:=False)
      ' only continue if it was found
      If Not rCopy Is Nothing Then

         Set rCopy = rCopy.Offset(1, 0)

         Range(rCopy, rCopy.End(xlDown).Offset(-1, 0)).Copy

         Windows("Excel Spreadsheet Header Row.xlsx").Activate
         
         ' find cell to paste to
         Set rPaste = Cells.Find(What:=varItem, After:=ActiveCell, LookIn:=xlFormulas, _
                                 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=True, SearchFormat:=False)
         ' check we found it
         If Not rPaste Is Nothing Then
            rPaste.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, _
                                             SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False

         End If
      End If
   ' now move to next item
   Windows("PRE_amended1.xlsx").Activate
   
   Next varItem

    
    End Sub

can anyone please help me with this