excel - Concatenate range names in VBA -

I have 14 categories defined in the macro What I would like to do, I will copy and paste all of them, which are individually There is one for loop as opposed to. I was going to structure my loop in this way but I'm not sure how to add object names and Variable I any help would be greatly appreciated.

  Generate sub-new report rng1 = FindHeader ("category", "extension") set rng2 = FindHeader ("RXCUI", "Detail") as a rnd1 range set rng3 Rng5 = FindHeader ("Ndc", "Extensions") set rng4 = FindHeader ("DDI", "Extensions") set rng5 = FindHeader ("GPI", "Extension") set rng6 = FindHeader ("Made Name", Rng8 = FindHeader Set ("Dos form", "Extensions") set rng9 = FindHeader ("FORMULARY_TIER", "Detail") set rng10 = FindHeader ("extension") set rng7 = FindHeader ("power", "extension") Rng8 = FindHeader QUANTITY_MAX "," Extensions ") set rng11 = FindHeader (" QUANTITY_TIME "," Extension ") set rng 12 = FindHeader ("PA_REQUIRED", "Extensions") set rng13 = FindHeader ("PA_Group_NAME", "Extensions") set rng14 = FindHeader ("STEP_THERAPY", "Extension") rng1.AutoFilter_CONTRECT: = 1, _CERTRIIA1: = 'I know' for = array of arrays ("disease prevention agents", "antidepressant drugs", "psychotic disorder", "anticonvulsant", "antiretrovirals", "antineoplastics"), _ operator: = xlFilterValues ​​= 2 That's this string combination, but I'm sure how to do this for variable names ... RNG & amp; Icopy RNG & amp; I.paste Next I End sub  

If you do not use the variable name anywhere are otherwise, you can create a collection of categories and then can use each loop, something like:

  range as dim rng1 dim rngcollec dim rngitem as new Collection Rng1 = FindHeader ("range", "expansion") set as a range set rng2 = FindHeader ("RXCUI", "expansion") rngcollec.Add rng2 set rng3 = FindHeader ("NDC", "Extension") rngcollec.Add Set rng4 = FindHeader (rng3 "DDI", "Vista Rngcollec.Add rng4 set rng5 = FindHeader (rng6 = FindHeader "GPI", "Extension") rngcollec.Add rng5 set ("Made Name", "Extension") rngcollec.Add set rng6 rng7 = FindHeader ("power", "expansion") rngcollec.Add rng7 set rng8 = FindHeader ( "dose form", "expansion") rngcollec.Add rng8 set rng9 = FindHeader ( "FORMULARY_TIER", "expansion") rngcollec.Add rng9 set rng10 = FindHeader ( "QUANTITY_MAX "," expansion ") rngcollec.Add rng10 set rng11 = FindHeader (" QUANTITY_TIME "," expansion ") rngcoll ec.Add set rng11 rng12 = FindHeader rng13 = FindHeader (" PA_REQUIRED "," expansion ") rngcollec.Add rng12 set ( "PA_Group_NAME", "Extensions") rngcollec.Add set rng13 rng14 = FindHeader ("STEP_THERAPY", "Extension") Rngcollec.Add rng14 rng1.AutoFilter_City: = 1 , _ Criteria 1 = Arre ( "Imunolojikl agents", "Antisaidentents", "Antipsychotics", "Anticollsents", "antiretroviral", "Antinoplastiks"), _ operator: = Rngcollec rngitem.copy for each rngitem in rngitem.paste XlFilterValuesNext