VBA Code for Consolidation

[morz68]

Hello all

I have made some basic code in VBA to generate 2 pivot tables. This works
I have then converted the pivots to values in order to consolidate into one report.

However, I want name the ranges or at the very least in the data consolidate box allow for it to pick up the current region? Any ideas? I've tried recording, but my knowledge is very novice at this stage.

Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="Consolidate1", RefersToR1C1:= _
"=Sheet1!R5C6:R11C8" (THIS IS WHERE I WOULD LIKE IT TO PICK UP THE CURRENT REGION)
ActiveWorkbook.Names("Consolidate1").Comment = ""
Range("O14").Select
Application.Goto Reference:="Consolidate1"

Thanks
M

 

[Fluff]

How about

Range("F5").CurrentRegion.Name = "Consolidate1"

 

[morz68]

Thank you

 

[Fluff]

You're welcome