Sorry, I'm posting this query again as there was an OE error received just
now
----------------
In a sheet: Z

I have a
Defined range: Branch
which refers to: =Z!$B$2:$V$10

I have a combo box (from control toolbox), with
Linked cell: H3
ListFillRange: BrList2
where BrList2 ='R'!$A$2:$A$86

The combo box selection (linked to H3) drives several formulas within Branch

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

I would also need each pasted range in the new sheet to be named after the
corresponding value in BrList2.
Eg: if 1st value in BrList2 is xx1, then the 1st paste done in B2:V10 will
be named: xx1

Thanks for help with a sub which can automate the above generation.

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 ..

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

Dave, just a quick note to say thanks for your response
Will try it out in office & feedback further here
Gotta leave for work now ...

Whew!

Safe for a few hours!

Max wrote:

Dave, just a quick note to say thanks for your response
Will try it out in office & feedback further here
Gotta leave for work now ...

--

Dave Peterson

Dave,
Many thanks. Tinkered around a little with the code you offered, it works
well (sub below).
I experimented with the recorder & added these 2 lines below (besides the
xlPasteColumnWidths line) as I realized that there was a floating picture (a
legend) within the range Branch which needed to be pasted over as well
(paste special doesn't paste the pic)
DestCell.Select
ActiveSheet.Paste
Would like your expert eye if the above additions are ok as-is (it seems to
work ok),
or, if there's a better way that it should be done
--------------
Sub Gen()
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", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Worksheets("Z")
For Each myCell In myRng.Cells
.Range("H3").Value = myCell.Value
Set RngToCopy = .Range("Branch")
RngToCopy.Copy
DestCell.Select
ActiveSheet.Paste
DestCell.PasteSpecial Paste:=xlPasteValues
DestCell.PasteSpecial Paste:=xlPasteFormats
DestCell.PasteSpecial Paste:=xlPasteColumnWidths
Set DestCell = DestCell.Offset(10, 0)
Next myCell
End With

End Sub