Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / March 2008

Tip: Looking for answers? Try searching our database.

Sub to iterate thru combo box n copy paste n name ranges successively in a new sht

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Max - 27 Mar 2008 04:45 GMT
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.
Max - 27 Mar 2008 21:50 GMT
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 - 27 Mar 2008 23:41 GMT
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>      

> Any help?
>
[quoted text clipped - 20 lines]
>
> Thanks ..

Signature

Dave Peterson

Max - 28 Mar 2008 00:12 GMT
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 - 28 Mar 2008 01:10 GMT
Whew!

Safe for a few hours!

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

Signature

Dave Peterson

Max - 28 Mar 2008 07:55 GMT
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
Dave Peterson - 28 Mar 2008 12:14 GMT
Untested...

dim myPict as picture 'is it really a picture

With Worksheets("r")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
set mypict = .pictures("picturenamehere")
End With

Then you can use:

 mypict.copy
 destcell.parent.paste

And then position it where you want usint .top, .left stuff.

> Dave,
> Many thanks. Tinkered around a little with the code you offered, it works
[quoted text clipped - 36 lines]
>
> End Sub

Signature

Dave Peterson

Dave Peterson - 28 Mar 2008 14:31 GMT
Here's one that's a little more fleshed out (er, tested):

Option Explicit
Sub testme()

   Dim myPict As Picture
   Dim myNewPict As Picture
   
   With Worksheets("sheet1")
       Set myPict = .Pictures("Picture 1")
   End With
   
   With Worksheets.Add
       myPict.Copy
       .Paste
       'the last picture added is what I want
       'if it's the only picture, I could have used .pictures(1)
       'but that could mess me up when I copy the code to a differet project
       Set myNewPict = .Pictures(.Pictures.Count)
   End With
   
   With myPict
       myNewPict.Top = .Top
       myNewPict.Left = .Left
   End With
   
End Sub

> Untested...
>
[quoted text clipped - 56 lines]
>
> Dave Peterson

Signature

Dave Peterson

Max - 29 Mar 2008 04:25 GMT
Dave, thanks for your responses.

The picture is: Picture 3 in sheet: Z
This pic floats over cell M6 which is within the range Branch to be
copied/pasted

Tried fitting in your code (shown below), but couldn't quite get it right

In the new sheet, the pic does get copy/pasted with each iteration but the
pastes are not in the correct position. The pastes all seem to be somewhere
just below the top left cell in the pasted ranges, eg C3, C13, and so on.
The correct positions should be over M6, M16, etc

------------------
Sub Generate()
Dim myCell As Range
Dim myRng As Range
Dim RngToCopy As Range
Dim DestCell As Range
Dim myPict As Picture
'Dim myNewPict As Picture

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 myPict = .Pictures("Picture 3")
     myPict.Copy
     DestCell.Parent.Paste

     With myPict
       .Top = .Top
       .Left = .Left
       'myNewPict.Top = .Top
       'myNewPict.Left = .Left
     End With

     Set DestCell = DestCell.Offset(10, 0)
  Next myCell
End With

End Sub
Dave Peterson - 29 Mar 2008 13:18 GMT
I didn't realize that you wanted this in your loop.

But this won't do much.  It just assigns the .top to the .top (no change at
all).  And even worse, it's refering to the original picture.

     With myPict
       .Top = .Top
       .Left = .Left
       'myNewPict.Top = .Top
       'myNewPict.Left = .Left
     End With

Untested, but it did compile:

Option Explicit
Sub Generate()

   Dim myCell As Range
   Dim myRng As Range
   Dim RngToCopy As Range
   Dim DestCell As Range
   Dim myPict As Picture
   Dim myNewPict As Picture
   
   Set DestCell = Worksheets.Add.Range("B2")
   
   With Worksheets("r")
       Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
   End With
   
   With Worksheets("Z")
   
       'this never changes, so don't do it in the loop
       Set myPict = .Pictures("Picture 3")
       
       For Each myCell In myRng.Cells
       
           .Range("H3").Value = myCell.Value
           
           Set RngToCopy = .Range("Branch")
           RngToCopy.Copy
   
           DestCell.PasteSpecial Paste:=xlPasteValues
           DestCell.PasteSpecial Paste:=xlPasteFormats
           DestCell.PasteSpecial Paste:=xlPasteColumnWidths

           myPict.Copy
           DestCell.Parent.Paste
           
           '"grab" the newest picture that was pasted
           With DestCell.Parent
               Set myNewPict = .Pictures(.Pictures.Count)
           End With
           
           'on the first loop, m6 is 4 rows and 11 columns from B2
           'should be the same relationship for the rest
           With myNewPict
               .Top = DestCell.Offset(4, 11).Top
               .Left = DestCell.Offset(4, 11).Left
               'give it a unique name
               .Name = "Pict_" & DestCell.Offset(4, 11).Address(0, 0)
           End With
           
           'get ready for next time
           Set DestCell = DestCell.Offset(10, 0)
       Next myCell
   End With

End Sub

> Dave, thanks for your responses.
>
[quoted text clipped - 52 lines]
>
> End Sub

Signature

Dave Peterson

Max - 29 Mar 2008 16:24 GMT
Marvellous, Dave. Thanks. That does it well.
Appreciate the patience & learnings, too.
Dave Peterson - 29 Mar 2008 16:25 GMT
Glad you got it working!

> Marvellous, Dave. Thanks. That does it well.
> Appreciate the patience & learnings, too.

Signature

Dave Peterson

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.