Hello,

I have the following code which i think is close but not close enough. I
want to get the value from a cell in one workbook, look up that value which
will be a tab name in another workbook, find a structure chart in that sheet
and paste it back in to the sheet I started with. This is what I have:

Dim RegionName As String

RegionName = Sheets("Qry_Basic_Information").Range("A2").Value
Windows("My Pack (Structure Charts).xls").Activate
Sheets(RegionName).Select

For Each myshape In ActiveSheet.Shapes
If myshape.Type = 12 Then myshape.Copy
Next myshape

Windows("My Pack.xls").Activate
Range("B6").Select
ActiveSheet.Paste

Can anyone point me in the right direction?

Many thanks in advance,

Martin

See if this works. When you paste a shape you have to paste it and then move
it to the correct location. Vecause a shape sits ontop of the worksheet you
can't just position the shape to a range location, instead you have to use
the left and top properties like below.


Dim RegionName As String
Set StructChrts = Workbooks("My Pack (Structure Charts).xls")
Set PackBk = Workbooks("My Pack.xls")

With PackBk.Sheets("Qry_Basic_Information")
RegionName = .Range("A2").Value

With StructChrts.Sheets(RegionName)

For Each myshape In .Shapes
If myshape.Type = 12 Then

myshape.Copy
.Paste
Set NewShape = Selection
NewShape.Top = Range("B6").Top
NewShape.Left = Range("B6").Left
End If
Next myshape
End With
End With

"Martin" wrote:

Hello,

I have the following code which i think is close but not close enough. I
want to get the value from a cell in one workbook, look up that value which
will be a tab name in another workbook, find a structure chart in that sheet
and paste it back in to the sheet I started with. This is what I have:

Dim RegionName As String

RegionName = Sheets("Qry_Basic_Information").Range("A2").Value
Windows("My Pack (Structure Charts).xls").Activate
Sheets(RegionName).Select

For Each myshape In ActiveSheet.Shapes
If myshape.Type = 12 Then myshape.Copy
Next myshape

Windows("My Pack.xls").Activate
Range("B6").Select
ActiveSheet.Paste

Can anyone point me in the right direction?

Many thanks in advance,

Martin

Joel, thank you, worked a treat!

Martin

"joel" wrote:

See if this works. When you paste a shape you have to paste it and then move
it to the correct location. Vecause a shape sits ontop of the worksheet you
can't just position the shape to a range location, instead you have to use
the left and top properties like below.


Dim RegionName As String
Set StructChrts = Workbooks("My Pack (Structure Charts).xls")
Set PackBk = Workbooks("My Pack.xls")

With PackBk.Sheets("Qry_Basic_Information")
RegionName = .Range("A2").Value

With StructChrts.Sheets(RegionName)

For Each myshape In .Shapes
If myshape.Type = 12 Then

myshape.Copy
.Paste
Set NewShape = Selection
NewShape.Top = Range("B6").Top
NewShape.Left = Range("B6").Left
End If
Next myshape
End With
End With

"Martin" wrote:

Hello,

I have the following code which i think is close but not close enough. I
want to get the value from a cell in one workbook, look up that value which
will be a tab name in another workbook, find a structure chart in that sheet
and paste it back in to the sheet I started with. This is what I have:

Dim RegionName As String

RegionName = Sheets("Qry_Basic_Information").Range("A2").Value
Windows("My Pack (Structure Charts).xls").Activate
Sheets(RegionName).Select

For Each myshape In ActiveSheet.Shapes
If myshape.Type = 12 Then myshape.Copy
Next myshape

Windows("My Pack.xls").Activate
Range("B6").Select
ActiveSheet.Paste

Can anyone point me in the right direction?

Many thanks in advance,

Martin