If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
macro help
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 |
#2
|
|||
|
|||
macro help
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 "JOHN" wrote: 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 |
#3
|
|||
|
|||
macro help
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JOHN" wrote: 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 |
#4
|
|||
|
|||
macro help
I'm not if i did something wrong but this code did the same as what I had?
"Billy Liddel" wrote: 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 "JOHN" wrote: 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 |
#5
|
|||
|
|||
macro help
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" wrote: I'm not if i did something wrong but this code did the same as what I had? "Billy Liddel" wrote: 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 "JOHN" wrote: 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 |
#6
|
|||
|
|||
macro help
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 "JOHN" wrote: I'm not if i did something wrong but this code did the same as what I had? "Billy Liddel" wrote: 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 "JOHN" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|