Paste to active Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thomp - 15 Feb 2007 16:10 GMT

I know nothing about VBA programming so this may be simple for most.
I am trying to create a macro that once I click on the autoshape it
will copy and paste the object in the cell which I have select or
have
the pointer on. I can make it copy and paste to the cell below but
can't figure out how to make it paste to my active cell. I also
really
need in to delete the current autoshape that is in the cell.

thanks in advance

Here is the code

Range("F9").Select
Selection.ClearContents
ActiveSheet.Shapes("AutoShape 7").Select
Selection.Copy
Range("F9").Select
ActiveSheet.Paste

Reply to this Message

Joel - 15 Feb 2007 16:25 GMT

You have to paste and then move the item.

I had to postion a lot of charts and used these statements to get it to work

Worksheets(TemperatureSheetName).Shapes(ChartName).Top = _
Worksheets(TemperatureSheetName). _
Rows((ChartRowOffset * (ModChartNumber)) + 1).Top
Worksheets(TemperatureSheetName).Shapes(ChartName).Left = _
Worksheets(TemperatureSheetName).Columns(MyColumnOffset).Left

> I know nothing about VBA programming so this may be simple for most.
> I am trying to create a macro that once I click on the autoshape it
[quoted text clipped - 15 lines]
> Range("F9").Select
> ActiveSheet.Paste

Reply to this Message

Jim Thomlinson - 15 Feb 2007 16:35 GMT

Give this a whirl...

Sheets("Sheet1").Shapes("AutoShape 4").Copy
ActiveSheet.Paste

Signature

HTH...

Jim Thomlinson

Reply to this Message

Bob Phillips - 15 Feb 2007 16:40 GMT

What do you mean by paste to the cell below. The top left corner of the
pasted shape overlaps cell F9, at least in my testing it does.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I know nothing about VBA programming so this may be simple for most.
> I am trying to create a macro that once I click on the autoshape it
[quoted text clipped - 15 lines]
> Range("F9").Select
> ActiveSheet.Paste

Reply to this Message

Thomp - 15 Feb 2007 17:01 GMT

> What do you mean by paste to the cell below. The top left corner of the
> pasted shape overlaps cell F9, at least in my testing it does.
[quoted text clipped - 28 lines]
>
> - Show quoted text -

I just got part of it to work. It now copies and pastes to my active
cell but my only problem now is that there is an object or shape
already in the cell and I need to delete it first before I paste the
new object into cell

One important point the object name ("Autoshape 42") will change every
time so the code can't look for it by name but rather by the fact I am
in that active cell before I run the macro

thanks,
Bill
Here is the new code

ActiveCell.Select
Selection.ClearContents
ActiveSheet.Shapes("AutoShape 7").Select
Selection.Copy
ActiveCell.Select
ActiveSheet.Paste

Reply to this Message

Joel - 15 Feb 2007 18:47 GMT

Thom: from Joel Look at the code I sent. You will see .top and .left to
indicate how the object gets referenced to the cell location.

> > What do you mean by paste to the cell below. The top left corner of the
> > pasted shape overlaps cell F9, at least in my testing it does.
[quoted text clipped - 48 lines]
> ActiveCell.Select
> ActiveSheet.Paste

Reply to this Message

Thomp - 15 Feb 2007 21:18 GMT

> Thom: from Joel Look at the code I sent. You will see .top and .left to
> indicate how the object gets referenced to the cell location.
[quoted text clipped - 57 lines]
>
> - Show quoted text -

Joel, I have some other shapes in the column that do not need to be
delete. Will this work as I only want the active cell to delete the
shape. I know this might be hard as I assume the actual shape does not
really reside in that cell?

Reply to this Message

Joel - 15 Feb 2007 16:40 GMT

To find object on a page you can use (see below) then you can delete object
and move objects.

For Each AllCharts In Worksheets(TemperatureSheetName).Shapes

Next allcharts

Reply to this Message