Help needed with editing macro please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lee Jeffery - 23 Jun 2004 01:28 GMT

Thank you to BrianB who assisted with a starting point for copying cell
data to a new workbook. I have now had a number of coffees, taken a
deep breath (thank you for this excellent advice, Brian!) and started
at the beginning. I have performed the steps manually and recorded this
as a macro. The code that has been generated for copying and pasting (1
cell only for this demo) follows:
Sub Test2()
Range("B2").Select
Selection.Copy
Workbooks.Open FileName:="D:\Worksheet in Basis (1) Database.xls"
ActiveSheet.Shapes("Picture 12").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
The problem with this method is that it is pasting a picture into the
destination workbook instead of copying the cell value from the source
cell B2 into the destination cell C4.
If I can get the data to paste into cells correctly I know I need to
build a loop for the next action of repeating the whole thing with a
new source workbook (perhaps I need more coffee). Please help.

---
Message posted from http://www.ExcelForum.com/

Reply to this Message

JulieD - 23 Jun 2004 03:20 GMT

Hi Lee

in your recorded macro you are selecting a picture:-

> ActiveSheet.Shapes("Picture 12").Select

before pasting

> ActiveSheet.Paste

try the following
replace the line

> ActiveSheet.Shapes("Picture 12").Select

with
Range("C4").select

(you might want to comment out the line - put an apostraphe in front of
it - rather than deleting it to ensure that something else that you want to
do doesn't break)

Let us know how you go
Cheers
JulieD

> Thank you to BrianB who assisted with a starting point for copying cell
> data to a new workbook. I have now had a number of coffees, taken a
[quoted text clipped - 21 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Lee Jeffery - 23 Jun 2004 11:10 GMT

Thanks Julie.

I tried your suggestion but was still seeing these picture boxes.
cleared everything from the destination file and re-recorded the macro
This time I used the arrow keys to move to the required destinatio
cells and now have no funny "invisible" picture boxes and th
spreadsheet is behaving. I suspect the source file has a few "funnies
in it but this is out of my control.
The new macro looks like this for 2 cells copied and pasted to a ne
workbook then both workbooks are closed with only the destination boo
being saved.
Sub POLexport()
Range("B2").Select
Selection.Copy
Workbooks.Open FileName:="D:\Worksheet in Basis (1) Database.xls"
Range("C4").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ActivateNext
Range("D4").Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
ActiveWorkbook.Save
ActiveWorkbook.Close
ActiveWindow.Close
End Sub

It's a bit clumsy because I am a raw beginner at this but it works. No
I'll try to come up with a loop to make the next exported record past
to the destination cells in Row 5. I reckon I'll need a lot mor
coffee!

Thanks again

--
Message posted from http://www.ExcelForum.com

Reply to this Message

JulieD - 23 Jun 2004 12:59 GMT

Hi lee

looks fine to me, if you need further assistance - just ask.

Cheers
JulieD

> Thanks Julie.
>
[quoted text clipped - 35 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Don Guillett - 23 Jun 2004 14:46 GMT

Suggest removing as many "selections" as possible. Some possibilities:

Range("B2").Copy
Workbooks.Open FileName:="D:\Worksheet in Basis (1) Database.xls"
Range("C4").Paste

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Thanks Julie.
>
[quoted text clipped - 35 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Lee Jeffery - 25 Jun 2004 08:14 GMT

Don,

Great suggestion. Many thanks for helping me tidy up

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Don Guillett - 25 Jun 2004 12:20 GMT

glad to help.

Signature

Don Guillett
SalesAid Software
donaldb@281.com

> Don,
>
> Great suggestion. Many thanks for helping me tidy up!
>
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message