Dynamic VLookup Function to match the data in two sheet and copy in one sheet

[bunty]

Hi,

With lots of hope writing my query and looking for help from all the experts:

Here I am trying to implement the excel vlookup function into my vba command.There are two sheet (Sheet1 and sheet 2) and both sheets are dynamic.
using Vlookup i am looking the name column of sheet1 to sheet2 name column and matching value from sheet2 (column name team) needs to be copy to sheet 1 (column :Team).
using the vlookup function: VLOOKup(E2,SHEET2!A:B,2,False) and using record macro.This is working fine for static records.But don't know how to do it for dynamic set of records.
Please find the set of sample records with below query:
Q1. I am trying to create pivot table and the code is working find.But when I have included the vlookup function to copy the data from sheet 2 to sheet 1 and running getting run time error to create pivot table.
Q2.Please help me to correct this code.

Sub CreatePivot()    
' Creates a PivotTable report from the table on Sheet1
    ' by using the PivotTableWizard method with the PivotFields
    ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField
    Dim rngGroup As Range
    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("Sheet1").Select
    Range("A1").Select
    '--------------VLookup Function to match and copy data-----'
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],SHEET2!C[-5]:C[-4],2,False)"
    Range("F2").Select
    Selection.Copy
    Range("F2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],SHEET2!C[-5]:C[-4],2,False)"
    Range("F2").Select
    Selection.Copy
    Range("F3:F13").Select
    ActiveSheet.Paste
    '------------------------'
    
    ' Create the PivotTable object based on the Employee data on Sheet1.
    Set objTable = Sheet1.PivotTableWizard
    
    ' Specify row and column fields.
    Set objField = objTable.PivotFields("STATUS")
    objField.Orientation = xlRowField
    ' Set objField = objTable.PivotFields("Name")
    Set objField = objTable.PivotFields("Team")
    objField.Orientation = xlColumnField
    
  
    
    ' Specify a data field with its summary
    ' function and format.
    Set objField = objTable.PivotFields("SL#")
    objField.Orientation = xlDataField
    objField.Function = xlCount
    
      
For Each objTable In Worksheets("Sheet1").PivotTables
If MsgBox("Refresh " & objTable & "?", vbYesNo) = vbYes Then
objTable.RefreshTable
End If
Next
 ThisWorkbook.RefreshAll
    ' Preview the new PivotTable report.
    'ActiveSheet.PrintPreview
    
    ' Prompt the user whether to delete the PivotTable.
    Application.DisplayAlerts = False
    If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
    

End Sub

Thanks a lot...

 

[bunty]

Hi Everyone,

Looking for your help...

Thanks....