troubleshoot pasting values in VBA Code from one sheet to another

**MVscarlet** · 04-26-2009, 10:42 AM

Hi - any hints as to why I keep getting an error on the final line?

Please Login or Register  to view this content.

**MVscarlet** · 04-26-2009, 10:53 AM

Hope that's better?

So any ideas?

**broro183** · 04-26-2009, 12:14 PM

hi,

To fix your existing code (in the original post) you could just change the last line...

Please Login or Register  to view this content.

Or you could try the below code which I have optimised slightly by removing the un-necessary* use of "select":
*This is the way it is recorded by the macro recorder.

Please Login or Register  to view this content.

hth
Rob

**MVscarlet** · 04-26-2009, 01:01 PM

Hi
Thanks
I am not 100% sure but it did not work - the debugger still stops it at the ActiveSheet.Paste line....

What is the logic behind the

Please Login or Register  to view this content.

line?

It really is confusing me because just a paste should be really straightforward and it seemed to work before...even this worked the first time - then it didn't work...any ideas?

Thanks

**broro183** · 04-26-2009, 01:37 PM

Ooopps!

Sorry about that, the reason your code doesn't work is that the ".copy" is "undone" (for want of a better word) as soon as another action is done by the code (ie the "Sheets("B").Unprotect"). I would be very surprised if it had even worked once with the way the code is presented - have you change the order of the code since recording it?
To overcome this you need to change the order of your code so that ".copy" is immediately before the ".paste".

I included the

Please Login or Register  to view this content.

to deactivate the clipboard once the range had been pasted into the right place. Without this line it is possible that the range could be re-pasted if [enter] is pressed in the spreadsheet (depending what other lines of code you have). If you were to shrink the VBE window & step through the original code, you could see the dotted "copy lines" around the original range & possibly the comment "select destination & press ENTER or choose paste" at the bottom of the Excel window.

However, I suggest using my optimised code as this is more efficient (without all the selections & associated screen flickering). The one line tecnique of using "range.copy destination" in my code prevents Excel from showing the previously mentioned comment.

btw, to learn about specific properties & methods in VBA code, you can select the phrase in the VBE window & press [F1] to bring up the Help files.

hth
Rob

**MVscarlet** · 04-30-2009, 07:59 PM

Thanksss - that was very useful

**broro183** · 05-01-2009, 07:53 AM

I'm pleased I coud help - thanks for the feedback

Rob

**crozanski** · 05-11-2010, 03:11 PM

I have pasted this the worksheet code but when I hit F5 or play, no macros show up. In fact the only macros that show up are those that I physically record. This doesn't help me b/c I want a date stamp for any time a row of data is modified.

The security is set to low in the file.

Please help b/c my frustration level is out of control!

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1, 1).Row > 1 Then
With Cells(Target(1, 1).Row, "A")
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

**broro183** · 05-12-2010, 06:25 PM

hi Crozanski,

Welcome to the Forum

Can you please start a new thread for your question?
(see the Forum Rules esp. #2)

Also, when you start a new thread please wrap your code in "Code Tags" as per Rule #3. If you think it relevant, you can provide a link to this thread in your new thread.

Rob