I'm not sure you gave enough info--or I couldn't pick it out <bg.

But maybe this will give you a start:

dim myCell as range
dim myRng as range
dim RngToCopy as range
dim DestCell as range

set destcell = worksheets.add.range("B2")

with worksheets("r")
set myrng = .range("A2:A86")
'or
'set myrng = .range("a2",.cells(.rows.count,"A").end(xlup))
end with

with worksheets("youdidn'tsharethenameorimissedit")
for each mycell in myrng.cells
.range("H3").value = mycell.value
set rngtocopy = .range("Youdidn'tsaywhatshouldbecopied--or I missed it")
rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues
destcell.pastespecial paste:=xlpasteformats '???
set destcell = destcell.offset(10,0)
next mycell
end with

====
Untested, uncompiled <vvbg

Max wrote:

Any help?

I'm dropping the naming bit

I'm also prepared to just use a DV in H3 instead of the combo box

Could someone help with a sub which can do the copy n paste part?:
-----------------
I would like to iterate through each text value in BrList2, copy Branch then
paste special as values & as formats starting at B2 in a new sheet, leaving
a blank single row in-between successive copy/pastes of Branch for each
value in BrList2. The 1st paste will go into B2:V10, 2nd paste goes into
B12:V20, and so on down the sheet
-----------------
Branch is a defined range
which refers to: =Z!$B$2:$V$10

BrList2 is another defined range used in the DV/combo box
which refers to: ='R'!$A$2:$A$86

Each text value within BrList2 (which are the branch names) will output a
different set of results in the range Branch

Thanks ..

--

Dave Peterson