Macro for array vlookup to worksheet

Sub MoveData()

Dim DatadumpSheet As String 'name of data dump sheet
Dim AccountSheet As String 'name of account sheet

Dim lMaxRows As Long 'max number of rows
Dim iTempCol As Integer ' index of a temp column to assist in calc
Dim Sheet As Object 'an object variable to be used for looping of sheet
Dim sAccountName As String 'account number

    'sheet names
    DatadumpSheet = "Datadump"
    AccountSheet = "Accounts"
    
    Sheets(DatadumpSheet).Select
     
    'make sure that data is unfiltered in order to get to right count of max used rows
    Cells.Select
    If ActiveSheet.AutoFilterMode Then
     
        On Error Resume Next
         
        ActiveSheet.ShowAllData
         
        On Error GoTo 0
     
    End If
     
    'max used rows in column A, with presumption that this column would always have most rows
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    'max used column on row 1 and adding one to it to get location of our temp col
    iTempCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
     
    'column header for temp col
    Cells(1, iTempCol) = "Move Where"
     
    'applying vlookup in temp column to find out region of account east/central etc
    With Range(Cells(2, iTempCol), Cells(lMaxRows, iTempCol))
     
        .FormulaR1C1 = "=VLOOKUP(RC3, '" & AccountSheet & "'!C1:C2,2,FALSE)"
         
    End With
     
     
    'looping thru all the sheets, to get the sheet name
    ' and then using that sheet name (EAST /CENTRAL etc) to apply filter on datadump
    For Each Sheet In Sheets
         
        'finding the sheet name and presuming that this is the account region that needs to be seeked
        sAccountName = Sheet.Name
     
        Sheets(DatadumpSheet).Select
        Cells.Select
        
        'if filter  not present, then apply one
        If ActiveSheet.AutoFilterMode = False Then
            Selection.AutoFilter
        End If
        
         
        'apply filter on our temp column of the sheet being checked (east. centeral etc)
        Selection.AutoFilter Field:=iTempCol, Criteria1:="=" & sAccountName, Operator:=xlAnd, Criteria2:="<>"
         
        'finding out max row number that is visible
        lMaxRows = Cells(Rows.Count, iTempCol).End(xlUp).Row
                 
        '>1 indicate that there are some rows where the account region match the sheet name
        If lMaxRows > 1 Then
            ' in case you want heads to be pasted too
            Range(Cells(1, 1), Cells(lMaxRows, iTempCol - 1)).Copy
         
            ' in case you dont want the headers to be pasted
            ' Range(Cells(2, 1), Cells(lMaxRows, iTempCol - 1)).Copy
         
            Sheets(sAccountName).Range("A1").PasteSpecial
        End If
         
    Next Sheet
     
    'clear temp column
    Range(Cells(2, iTempCol), Cells(lMaxRows, iTempCol)).Clear
    
    Cells.Select
    If ActiveSheet.AutoFilterMode Then
     
        On Error Resume Next
         
        ActiveSheet.ShowAllData
         
        On Error GoTo 0
     
    End If
     
    Range("A1").Select
     
End Sub

Sub MoveData()

Dim DatadumpSheet As String 'name of data dump sheet
Dim AccountSheet As String 'name of account sheet

Dim lMaxRows As Long 'max number of rows
Dim iTempCol As Integer ' index of a temp column to assist in calc
Dim Sheet As Object 'an object variable to be used for looping of sheet
Dim sAccountName As String 'account number

Dim iColDateRec As Integer ' column position for Date Received
Dim iColTimeRec As Integer ' column position for Time Received

Dim sCurrentDate As String ' to store current date in MMDDYYYY format
Dim sDateTimeFilter As String ' filter for date and time
Dim sFileName As String   ' file name

    'sheet names
    DatadumpSheet = "Datadump"
    AccountSheet = "Accounts"

    Sheets(DatadumpSheet).Select
    
    ' find the position of text "Data Received" on row 1
    iColDateRec = Application.WorksheetFunction.Match("Date Received", Range("1:1"), 0)
    
    ' find the position of text "Time Received" on row 1
    iColTimeRec = Application.WorksheetFunction.Match("Time Received", Range("1:1"), 0)
    
      'getting current date and formatting it to MMDDYYYY format
    sCurrentDate = Format(Date, "MMDDYYYY")
    
    ' getting filter one day before todays date   (date -1)
    sDateTimeFilter = Format(Date - 7 & " 14:00:00", "YYYY-MM-DD hh:mm:ss")
    
    sFileName = ActiveWorkbook.FullName
    sFileName = Left(sFileName, InStrRev(sFileName, ".") - 1) & "_" & sCurrentDate & Mid(sFileName, InStrRev(sFileName, "."))
    
    'make sure that data is unfiltered in order to get to right count of max used rows
    Cells.Select
    If ActiveSheet.AutoFilterMode Then
     
        On Error Resume Next
         
        ActiveSheet.ShowAllData
        ActiveSheet.AutoFilterMode = False
        On Error GoTo 0
     
    End If
     
    'max used rows in column A, with presumption that this column would always have most rows
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    
    'max used column on row 1 and adding one to it to get location of our temp col
    iTempCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
     
    'column header for temp col
    Cells(1, iTempCol) = "Move Where"
     
    'applying vlookup in temp column to find out region of account east/central etc
    With Range(Cells(2, iTempCol), Cells(lMaxRows, iTempCol))
     
        .FormulaR1C1 = "=VLOOKUP(RC3, '" & AccountSheet & "'!C1:C2,2,FALSE)"
         
    End With
     
    'looping thru all the sheets, to get the sheet name
    ' and then using that sheet name (EAST /CENTRAL etc) to apply filter on datadump
    For Each Sheet In Sheets
         
        'finding the sheet name and presuming that this is the account region that needs to be seeked
        sAccountName = Sheet.Name
     
        Sheets(DatadumpSheet).Select
        Cells.Select
        
        'if filter  not present, then apply one
        If ActiveSheet.AutoFilterMode = False Then
            Selection.AutoFilter
        End If
        
        'apply filter on our temp column of the sheet being checked (east. centeral etc)
        Selection.AutoFilter Field:=iTempCol, Criteria1:="=" & sAccountName, Operator:=xlAnd, Criteria2:="<>"
        
        'finding out max row number that is visible
        lMaxRows = Cells(Rows.Count, iTempCol).End(xlUp).Row
                 
        '>1 indicate that there are some rows where the account region match the sheet name
        If lMaxRows > 1 Then
            ' in case you want heads to be pasted too
            Range(Cells(1, 1), Cells(lMaxRows, iTempCol - 1)).Copy
         
            ' in case you dont want the headers to be pasted
            ' Range(Cells(2, 1), Cells(lMaxRows, iTempCol - 1)).Copy
         
            Sheets(sAccountName).Range("A1").PasteSpecial
            Sheets(sAccountName).Select
            
            'sorting
            Selection.Sort _
                Key1:=Range(Cells(2, iColDateRec).Address), Order1:=xlAscending, _
                Key2:=Range(Cells(2, iColTimeRec).Address), Order2:=xlAscending, _
                Header:=xlYes, OrderCustom:=1, _
                MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
            
    
            'applying FORMULA TO GET DATE AND TIME
            With Range(Cells(1, iTempCol), Cells(lMaxRows, iTempCol))
             
                .FormulaR1C1 = "=RC" & iColDateRec & " + RC" & iColTimeRec
                 .NumberFormat = "YYYY-MM-DD hh:mm:ss"
            End With
    
            'if filter  not present, then apply one
            Cells.Select
            If ActiveSheet.AutoFilterMode = False Then
                Selection.AutoFilter
            End If

            Cells.Select
            Selection.AutoFilter Field:=iTempCol, Criteria1:="<" & sDateTimeFilter, Operator:=xlAnd, Criteria2:="<>"
            
            Rows("2:" & lMaxRows).Delete
            
            ActiveSheet.AutoFilterMode = False

            Range(Cells(1, iTempCol), Cells(lMaxRows, iTempCol)).Clear

            Range("A2").Select
            
        End If
         
    Next Sheet
    
    ActiveWorkbook.SaveAs sFileName
     
End Sub