Simple macro help.....

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Simple macro help.....

  1. #1
    Board Regular ExcelNovice's Avatar
    Join Date
    May 2002
    Posts
    559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

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

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

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular ExcelNovice's Avatar
    Join Date
    May 2002
    Posts
    559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular ExcelNovice's Avatar
    Join Date
    May 2002
    Posts
    559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  7. #7
    Board Regular ExcelNovice's Avatar
    Join Date
    May 2002
    Posts
    559
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com