Help needed with editing macro please

[Lee Jeffery]

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.

 

[JulieD]

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

 

[Lee Jeffery]

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

 

[JulieD]

Hi lee

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

Cheers
JulieD

 

[Don Guillett]

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

 

[Lee Jeffery]

Don,

Great suggestion. Many thanks for helping me tidy up

 

[Don Guillett]

glad to help.