L
Legacy 93538
Guest
Hi
I have created a macro which opens a workbook, creates a new sheet called "Values" and copies the forumla data from one sheet and paste special into the Values sheet. It then creates a vlookup in cell B1 on sheet Input_Reference_Table that lookups the value in cell A1 within a table on the "Values" sheet and returns the values in column 1 within the table range.
However when the macro runs i get an error in B1 cell of "#NAME??" i'm not sure why as the value in the cell is in the table range.
Can anyone help me?
Thanks
Jessicaseymour
I have created a macro which opens a workbook, creates a new sheet called "Values" and copies the forumla data from one sheet and paste special into the Values sheet. It then creates a vlookup in cell B1 on sheet Input_Reference_Table that lookups the value in cell A1 within a table on the "Values" sheet and returns the values in column 1 within the table range.
Code:
Option Explicit
Sub PP3InputRef()
Dim StrFldr As String
Dim PPWB As Workbook
Dim Nrow As Long
Dim cell As Range
Application.DisplayAlerts = False
StrFldr = ThisWorkbook.Path
Set PPWB = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_Input_Reference_Table_V1.xlsx")
PPWB.Sheets.Add.Name = ("Input_Reference_Table"): PPWB.Sheets.Add.Name = ("Values")
PPWB.Sheets("PPIIIFORM").Select: Cells.Select: Selection.Copy: PPWB.Sheets("Values").Select: Cells.Select: Selection.PasteSpecial Paste:=xlPasteValues
PPWB.Sheets("Input_Reference_Table").Select: Range("B2").Select: ActiveCell.FormulaR1C1 = "=VLOOKUP(A1,Values!A4:AB700,1,FALSE)"
However when the macro runs i get an error in B1 cell of "#NAME??" i'm not sure why as the value in the cell is in the table range.
Can anyone help me?
Thanks
Jessicaseymour