Copy/Paste from VBA

Topic

New Reply

Hi.

Firstly i point out i am running this code from within Access 97.

I want to take the contents of a cell in every other row, and paste them into a cell one row above.

The following code selects every other row correctly, but at the selection.copy line errors out with an Error 4605, “This method or property is not available because no text is selected”.

For Each rng In sht.UsedRange.Rows

If rng.row Mod 2 0 Then
sht.Cells(rng.row, 5).Select
Selection.Copy
sht.Cells(rng.row – 1, 2).Paste
End If

Next rng

I know that the cell does contain text, so the error should read something like “Error. Your code is wrong.”

Something simple i am sure, but where is my error???

Many thanks.

Reply | Quote

Replies

Have you set a reference to Excel in your code. The fact that rng.row is not changed to rng.Row (capitalised R), indicates that there might be a problem (assuming you copied the code as is from Access).

I think a similar problem arose recently in a thread involving yourself, and including a reference to Excel seems to have solved it.

see Here

Dim rng As Excel.Range ?

Andrew C

Reply | Quote

Hi Andrew

Yes, i have set a reference to Excel, in fact, this bit of code is in the same procedure as the other code you refer to.

In addition, when i step through the code, the cursor indicates the correct row number when held over rng.row, so that bit is working, and furthermore, if the row number is such that it should be skipped, it is skipped, so the Mod line is definately OK.

Reply | Quote

Hi Peter,
Try changing your code to:
If rng.row Mod 2 0 Then
sht.Cells(rng.row – 1, 2) = sht.Cells(rng.row, 5)
End If
and see if that helps. As a general rule, your code will run faster if you can avoid selecting, copying and pasting wherever possible.
Hope that helps.

Reply | Quote

[indent]

---

As a general rule, your code will run faster if you can avoid selecting, copying and pasting wherever possible.

---

[/indent]
And it can also stop your workbook jumping around like Rory’s userpic!

Reply | Quote

I usually stop the screen jumping with a:
Application.screenupdating = False

Then when the fireworks are over:
Application.screenupdating = true

Reply | Quote

Hopefully in addition to, and not in place of, using ranges instead of selections (were possible).

It certainly does make code faster with ranges.

Reply | Quote

Well, as usual, i have to say a big THANK YOU to those who have helped me

One other little problem: The workbook i am working on is saved under a different name, and then i have the following lines:

appExcel.Application.Visible = True
appExcel.Quit
Set appExcel = Nothing

I am assuming these lines are correct.

If i then double click on the file to open it, it appears to open in “invsible” mode, i.e. i can’t see Excel. But, if i open any other Excel file, then, when Excel opens, i can see both files, including the one that appeared to be invisible???

I am presuming i have not reset or closed something properly…??

Reply | Quote