Please help with cut and paste

[Boe]

I know this sounds simple but I am having a problem with cut and paste in
Excel 2003. When I go to Pricewatch.com, copy and paste a group of cells,
for example the lowest price on a specific CPU to an Excel spreadsheet, the
column which has the price with shipping only shows with the big BUY NOW
button. In office XP, I used to be able to select the button and move it to
the side of the columns so it wasn't in the way. Now I don't seem to have
any control of that button so it blocks the price on my excel spreadsheet.

Thanks

 

[David McRitchie]

If you want to remove pictures within the spreadsheet.
Several choices depending on how you want to select the shapes.
http://www.mvps.org/dmcritchie/excel/shapes.htm

If not familiar with installing and using macros you might also want
to read Getting Started with Macros on my pages.
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Since it seems like you are adding them one at time you could
right click on the shape and delete.

But you say before you were moving the shape to side, is it any
good to you ?

 

[Boe]

Thanks, I'll see what I can figure out with the macro although it says I
should open the web page in Excel? How do you do that?

As for moving it to the side, I used to do it in XP because I could never
delete it, now right clicking doesn't do anything so I can't do anything
with the link in Office XP.

 

[Boe]

Sorry, I read the link you sent me and I'm unclear on the code/comments. It
looks like they are stating what is wrong with the code, but not which code
to use.

 

[David McRitchie]

What says you should open the page in Excel, though you were
copying and pasting. You can open a web page in Excel though,
Excel 97 and up. I used to take online bank statements and
paste into Excel and remove the shapes on each row, but the banks
all now provide for exporting to spreadsheet, Quicken (Intuit), or MS Money.

Now you can delete any or all of the shapes. Actually you can left click on the shape
to select it unless it has link; otherwise, right click is what I normally use
to delete a single shape. What happened instead of working when you
tried to delete one shape...

 

[David McRitchie]

Comments are comments.
There are several macros the document and the comments together should
help you determine which macro you want to use for a particular situation.
This macro will delete all shapes on the sheet. The comments warn you
that that is what it is going to do. If you don't want to delete all shapes use one
of the other macros.

Sub delShapesOnSht()
'Dave Peterson, misc 2001-11-12, no loop required
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes
Selection.Delete
End Sub

 

[Boe]

Left clicking depresses the button but nothing happens - it doesn't open up
the link. Right clicking doesn't do anything.

 

[Boe]

Thanks - I tried it - in Excel 2003 it gives a runtime error 7 "out of
memory" This is the line it appears to have an issue with
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes

 

[David McRitchie]

Hi Boe,
Don't know why you get that, I am using Excel 2000 and yours is brand
new. Perhaps someone else can test in Excel 2003 just to make sure.

I did find another problem though, if there were no shapes on the page
it would delete whatever was selected. i.e. cells
So would modify the macro to read as follows:

Sub delShapesOnSht()
'Dave Peterson, misc 2001-11-12, no loop required
If ActiveSheet.Shapes.Count = 0 Then
MsgBox "No Shapes on page for deletion"
Exit Sub
End If
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes
Selection.Delete
End Sub

I've updated the page, and I did see another SUB on same page with
same name that was completely commented out. I removed that to
avoid further confusion.

See if the macro above works better, Trychanging zoom to 100% if it is
not at that setting now. If you changed it. Take Excel down and bring it
back up then try the new macro.

How many shapes do you have
on the page. There were problems with earlier versions and print drivers
with too many shapes.
http://www.mvps.org/dmcritchie/excel/slowresp.htm
look for Memory Leakage.

If you still have a problem, perhaps the following might help someone
diagnose the problem.
What printer do you have, you have Excel 2003, what operating system.

 

[Boe]

I appreciate you trying. I've changed the macro but it gives the same error
at the same line -
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes

I'll be happy to e-mail the spreadsheet to you if you like although I'm
guessing the problem as you say may be in Excel 2003

 

[Dave Peterson]

Maybe something like:

Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
myshape.Delete
Next myshape

 

[David McRitchie]

Hi Boe,
Okay email the spreadsheet, if the workbook is not small please
cut it down -- I have a dial-up connection.

 

[Boe]

SWEET!!! Thanks, both of you. I have one more question - much easier - how
do I make that macro come up whenever I open Excel, before I start creating
my spreadsheet. I'll be using this function a lot!

Thanks again

 

[Boe]

The code works thanks. I just need to make it a universal macro so it is
enabled when I start a spreadsheet from scratch.

 

[Dave Peterson]

You can name a macro Auto_Open() and keep it in a General module. Then the
macro will run each time you open that workbook.

But I think you're asking about how to make sure the macro is always available
when you open excel. The simplest solution would be to use a separate workbook
and store that workbook in your XLStart folder. Each time excel starts up,
it'll load all the files that are in that folder.

(The folder location varies depending on the version of excel and windows--use
Windows start button|Find to search for your exact location.)

A lot of people have macros that they want to have available whenever excel
starts. The most common name for this kind of workbook is personal.xls.

I don't like hitting Alt-F8 to run macros, so I use a workbook based on John
Walkenbach's MenuMaker program:

http://j-walk.com/ss/excel/tips/tip53.htm

It gives you the ability to modify the worksheet menubar with your macros--it's
really nice.

 

[Boe]

much obliged!

You can name a macro Auto_Open() and keep it in a General module. Then the
macro will run each time you open that workbook.

But I think you're asking about how to make sure the macro is always available
when you open excel. The simplest solution would be to use a separate workbook
and store that workbook in your XLStart folder. Each time excel starts up,
it'll load all the files that are in that folder.

(The folder location varies depending on the version of excel and windows--use
Windows start button|Find to search for your exact location.)

A lot of people have macros that they want to have available whenever excel
starts. The most common name for this kind of workbook is personal.xls.

I don't like hitting Alt-F8 to run macros, so I use a workbook based on John
Walkenbach's MenuMaker program:

http://j-walk.com/ss/excel/tips/tip53.htm

It gives you the ability to modify the worksheet menubar with your macros--it's
really nice.