macro help

  • Thread starter Thread starter JOHN
  • Start date Start date
J

JOHN

I have gotten to this point with the great input from others here, and
I'm looking to tweek this a little more.
Below is a copy of one of my macros. I want it to copy and paste A5:V5
into any selected cell.
When I click on the macro button it copies the row and allows me to
right click and paste it in any selected cell, but it leaves the view on the
source row of the macro.
Is there any change I can make to the code to eliminate it from moving
the view to the source row, and do all the steps without right
clicking-paste?
In short I am looking to just select a cell, click on the macro, have
it execute and remain on the selected cell.

Sub JOHN()
'
' JOHN Macro


Range("A5:V5").Select
Application.CutCopyMode = False
Selection.Copy
'Sub S_Copy()
ActiveCell.Value = Range("A5").Value
ActiveSheet.Paste
End Sub
 
John

You might be better with an event macro. The one below will only copy the
range if you right click in column A.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim rng2Copy As Range
Dim rng2Paste As Range

Set Target = Range("A:A")
Set rng2Copy = Range("A5:V5")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

Set rng2Paste = Range(ActiveCell, ActiveCell.Offset(0, 21))
rng2Paste.Value = rng2Copy.Value

End Sub

right-click the sheet tab, select View Code and copy the above code. Beware,
data in the active row is overwritten, no undo facility in this macro.

Regards
Peter Atherton
 
Hi,

You have three possilbe intreptations of you post. 1. You want to click a
button and a predefined range will be copied to a predefined destination - no
selection involved.
2. You select the source and click the button and the copy is pasted to the
predefined destination, 3. You select a destination cell and click the macro
button and the predefined source is copied to the selected destination.

Let's suppose, based on your sample macro that you want to copy from a
predefined range to the active cell:

Sub MyCopy()
ActiveCell.Resize(1, 22) = Range("A5:V5").Value
End Sub
 
Well you can go back to the source row after copying.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim rng2Copy As Range
Dim rng2Paste As Range

Set Target = Range("A:A")
Set rng2Copy = Range("A5:V5")

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

Set rng2Paste = Range(ActiveCell, ActiveCell.Offset(0, 21))
rng2Paste.Value = rng2Copy.Value
Cells(5, 1).Activate

End Sub

Regards
Peter
 
John

If you want to copy the row into any cell, say C34 then remove of rem out
(with an apostrophe)

If Intersect(ActiveCell, Target) Is Nothing Then Exit Sub

Peter
 
Back
Top