Macro - Run in a different cell selected by user each time

[AniR]

Hello, I do not know VBA can someone please help to edit below code so that the macro runs in a different cell (cell of users choice) each time. Currently it repeats itself in the same cell. Thanks in advance.

Sub UnitPrice()
'
' UnitPrice Macro
'
' Keyboard Shortcut: Ctrl+u
'
ActiveWindow.SmallScroll Down:=-9
Range("B106:M111").Select
Selection.Copy
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 9
Range("B10").Select
ActiveSheet.Paste
End Sub

 

[maabadi]

Welcome to MrExcel Message Board.
Please update your Account Details Also.
Try:

Sub UnitPrice()
' UnitPrice Macro
' Keyboard Shortcut: Ctrl+u
Range("B106:M111").Copy Range("B10")
End Sub

OR

Sub UnitPrice()
' UnitPrice Macro
' Keyboard Shortcut: Ctrl+u
Range("B10:M15").Value = Range("B106:M111").Value
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you give some specific examples of what you mean by this?

the macro runs in a different cell (cell of users choice) each time.

Give examples of what cell(s) the user is selecting and what should be copied from where to where.

 

[AniR]

Welcome to MrExcel Message Board.
Please update your Account Details Also.
Try:

Sub UnitPrice()
' UnitPrice Macro
' Keyboard Shortcut: Ctrl+u
Range("B106:M111").Copy Range("B10")
End Sub

OR

Sub UnitPrice()
' UnitPrice Macro
' Keyboard Shortcut: Ctrl+u
Range("B10:M15").Value = Range("B106:M111").Value
End Sub

I do not know VBA (so that makes things worse) although I did copy and paste the recommended code. Thanks for the help. It did not serve my purpose. Perhaps I did not explain my requirement clearly.

 

[AniR]

Welcome to the MrExcel board!

Can you give some specific examples of what you mean by this?

Give examples of what cell(s) the user is selecting and what should be copied from where to where.

Basically I need a range of cells to be copied and pasted in any cell that user (I) clicks each time I run the macro. Thanks

 

[AC PORTA VIA]

is this what you are looking for

Range("B6:M11").Copy ActiveCell

 

[AniR]

is this what you are looking for

Range("B6:M11").Copy ActiveCell

Yes Thank you

 

[AniR]

Yes Thank you

Can I ask how to keep the cursor (or stay at the pasted cell) rather than returning to the copied range each time?

 

[AC PORTA VIA]

it should stay if below is the only line in your macro

Range("B6:M11").Copy ActiveCell

do you have anything else after

 

[AniR]

it should stay if below is the only line in your macro

Range("B6:M11").Copy ActiveCell

do you have anything else after

No. Also if I delete cells in the sheet the cell reference for the cells to copy changes each time. Is there any way to lock them so that it is the same range that is copied each time?