[RESOLVED] Help with object

[ttocs]

Hey guys,

Stumbled across this site while looking for an answer to this issue. Wish I knew about this forum a month ago while I was learning! You guys are amazing and the forums are full of knowledge. Wondering if you could help me out.

I'm making a macro to to automate a few processes and then save everything so templates stop getting saved over (and formula's lost) in Excel. I'm using VBA 6.5.

What I am trying to do is copy an embedded word object on Sheet 1 over to the top of Sheet 2. I have tried everything to do this but the Object always has a different name. Copying all cells around it (which works with a right click) doesn't work in VBA. The formula in the sheet is =EMBED("Word.Document.8",""). I don't create the sheet (only the one that is using this information) so I can't create a naming convention or copy contents into Excel, etc. Any idea how to do this in VBA?

Thank you in advance for your help.
-Scott

[Hack]

Welcome to the forums.

Can you do it manually?

If so, start a macro recording, perform the steps, stop the macro, and see what kind of code results.

[ttocs]

I tried recording it but it just said selection. It didn't give me anything special that I could use.

[ttocs]

Actually, I just tried it again and it gave the following:

 ActiveSheet.Shapes("Object 7").Select
    Selection.Copy
    Sheets("Master Summary").Select
    Range("A1").Select
    ActiveSheet.Paste

This would be helpful, but it's not always named Object 7. It could be any number. Is there a way to take that into account?

[ttocs]

I think I just figured this out. Stumbled across a link on another page which then lead to another link, then the answer

Looks like I need to use the following if I do not know the name of the Shape:

ActiveSheet.Shapes(1).Select

This way, it will always take the first shape it comes into contact with and it doesn't matter if the name changes.

FYI to anyone else who needs to figure this out. Hope this helps.