Simple macro help.....

[ExcelNovice]

I included the matter below for someone's assistance. Everything on the macro works well, except that when I run the macro, the content of cells A6:L9 is copied to cell A18. How can I change the macro so that it copies to the cell selected by the user(whatever cell that is)?
See macro below.......

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/30/02 by ExcelNovice'

'
Range("A6:L9").Select
Selection.Copy
Range("A18").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A10:A13").Select
End Sub

 

[Juan Pablo González]

On 2002-07-30 12:18, ExcelNovice wrote:
I included the matter below for someone's assistance. Everything on the macro works well, except that when I run the macro, the content of cells A6:L9 is copied to cell A18. How can I change the macro so that it copies to the cell selected by the user(whatever cell that is)?
See macro below.......

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/30/02 by ExcelNovice'

'
Range("A6:L9").Select
Selection.Copy
Range("A18").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A10:A13").Select
End Sub

Try with

<pre>Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/30/02 by ExcelNovice'

'
Range("A6:L9").Copy ActiveCell
Range("A10:A13").Select
End Sub</pre>

 

[Mudface]

Something like this might work OK for you, it'll paste to the active cell. I've included a quick check that the data to be pasted doesn't get overwritten:-

If Application.Intersect(Range("A6:L9"), ActiveCell) Is Nothing Then
Range("A6:L9").Copy Destination:=ActiveCell
Else
MsgBox "Please pick another cell"
End If

 

[ExcelNovice]

Thanks Mudface, thanks for your help. The macro works except that as soon as a cell is selected the macro runs! I need to have it so that the macro runs only when the user enables it (that is, click the macro button).

Can this be done?

 

[Mudface]

Yes, I'm guessing you have put the code into the worksheet Selection_Change event. Cut out the code in between the sub declaration and the 'End Sub' and delete the rest. Then, click on Insert-Module, then Insert- Procedure. Give your Procedure an appropriate name and paste in the code. Then just run it from the macro toolbar.

 

[ExcelNovice]

Hi Juan Pablo G., your macro works well, but although it does what it is suppose to do, I keep getting this message:

"Cannot change part of a merged cell"

How can I stop this from happening?

Thanks.

 

[ExcelNovice]

Works like a charm mudface!! Really appreciate it!!