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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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"")"
 
Upvote 0
Solution

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top