vlookup in VBA using selection.currentregion in another sheet

Mishka4595

New Member
Joined
Nov 17, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
i'm new to both macros and vba

I have a pivot table in sheet FR Pivot and a table in sheet Final. I want to pick up the pivot table data and use vlookup to fill in values in my table in the Final sheet. The issue is that the pivot table can be dynamic. So I'm trying the following:

VBA Code:
Dim Range1 As Range
    
     With Sheets("FR Pivot")
            
      
        .Range("E4").Select
        
       
        Selection.CurrentRegion.Select
                
        
        Set Range1 = Selection
        
    End With
    Sheets("Final").Select
    Range("J7").Select
    ActiveCell.FormulaR1C1 = _
        "=IFNA(VLOOKUP([@[Row Labels]],Range1,2,0),""NA"")"

I think I'm going wrong with the named range part but unable to figure out how to fix it. For the macro, I'm recording my actions and then modifying code to make it dynamic which is why there are so many selects.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Mishka4595

New Member
Joined
Nov 17, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Got the answer:

VBA Code:
Dim addr 

addr = Sheets("FR Pivot").Range("E4").Currentregion.Address()

Sheets("Final").Range("J7").Formula = _
        "=IFNA(VLOOKUP([@[Row Labels]], 'FR Pivot'!" & addr & ",2,0),""NA"")"
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,999
Messages
5,628,052
Members
416,289
Latest member
Jbelisari

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top