simple copy and paste problem in excel

[eon5]

Hi,
i have a command button on sheet one and want to copy info to sheet7 with the following code

Windows("StockOnHand01.csv").Activate
Sheets("StockOnHand01").Range("A1:AF25000").Select
Selection.Copy
Windows("Test1.xls").Activate
Sheet7.Activate
Sheet7.Range("A1").Select
Sheet7.Range("A1").Paste 'This is where the code boms out

The code copies the info correctly into its memory and selects the cell in sheet7 correctly but it does not want to paste the code, error message of "do not support this object"


any ideas?

 

[AnotherHiggins]

Avoid using Activate and Select as much as possible. Copying and Pasting will also slow down your code.

In your case, you can do away with all of that by simply using:

    workbooks("Test1.xls").sheets("Sheet7").Range("A1:AF25000").value = _
    workbooks("StockOnHand01.csv").sheets("StockOnHand01").Range("A1:AF25000").value

or if you just want to copy over the entire sheet, then you could just use something like:

    Workbooks("StockOnHand01.csv").Sheets("StockOnHand01").Copy After:=Workbooks("Test1.xls").Sheets(6)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[eon5]

The firts suggestion does not work and the 2nd suggestion does not copy the data into the sheet StockOnHand01 but its creating another sheet StockOnHand01 (2)

any suggestions

 

[AnotherHiggins]

-> The firts suggestion does not work

I just tried it and it works for me as written. What error does it give you? Or does it not perform the way you want?

As for the second suggestion, I prefaced it by saying, "if you just want to copy over the entire sheet". The idea is that you could copy it over, then rename it. This obviously won't work for you if there is other data on sheet7 in Test1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[remeng]

Give this a try.

Change:

Sheet7.Range("A1").Paste

to

 activesheet.paste

you have already selected the starting cell that you would like to paste to.

 

[xlbo]

You may also use the single line:

Sheets("StockOnHand01").Range("A1:AF25000").copy destination:=workbooks("Test1.xls").Sheet7.range("A1")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question