Brilliant people & nobody can paste special for value in VB?
Posted by nica on February 06, 2002 1:08 PM
I need to have this function(or something similar calculating the date) on a sheet that gets copied & pasted to a new sheet. All I need to do is paste-special using VB and Value, however I don't have a clue & can't find out how from any resource.
From the hours I've spent looking through your code snippets I'd certainly have thought I'd have seen it by now.
This is a desparate plea!! -jk-
Thanks well in advance for the help!
nica
Posted by faster on February 06, 2002 1:29 PM
this pastes clipboard contents as values:
Sub PasteSpec()
On Error GoTo Etrap
Selection.PasteSpecial Paste:=xlValues
Exit Sub
On Error GoTo Lesson
Etrap:
Beep
Exit Sub
End Sub
Posted by Brad Smith on February 06, 2002 1:30 PM
You need to create a event and then attach this code in a Private sub to fire
Private Sub Cell_Exit()
Sheets("X").Range("Y" & Z).value = Sheets("A").Range("B" & C)
' where X & A are the Worksheet Names
' Where Y & B are the Row #s ( Not an _
alpha character)
' Where Z & C are the Col #s
End Sub
Set this to run on exit from the specified source cell, or tie it to a button and run it after all your updates are done and it will copy the info to your receiving cell.
HTH...Brad
Posted by Mark W. on February 06, 2002 1:43 PM
When in doubt use the Macro Recorder and exam the code that it produces [NT]
Posted by Capulet Mercutio on February 06, 2002 4:10 PM
This is excellent advice and applies to many questions asked on this board (nt)
Posted by Brad on February 07, 2002 6:30 AM
Re: correction to code snippet
Update to Col & Row settings in snippet
' Sorry Lost my head and got Col & Row backwards
Posted by Brad Smith on February 07, 2002 6:36 AM
But It Isn't a Panacea
Viewing a recorded Macro can yield a number insights in to how the code works, but IT isn't going to show you complex code. It simply copies your keystrokes and automates activities into VBA where applicable.
Some things require asking people on the board or searching for code snippets.
Brad
Posted by Mark W. on February 07, 2002 6:47 AM
However, it's sufficient for command related statements like Paste Special... [NT]
Some things require asking people on the board or searching for code snippets. Brad
