1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel macro question..

Discussion in 'Business Applications' started by AMD2800, Apr 10, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. AMD2800

    AMD2800 Thread Starter

    Joined:
    May 3, 2005
    Messages:
    165
    HI everyone,

    My question is about a macro. I would like to be able to Copy some cells and paste them in another cell. I record a macro where I pick the columms and rows that I want then I paste it one row down the last row that I copy from. and it works great but I need to be able to paste it in any cell that I pick. Since I record the macro it keep using A20. My question is how to I change the macro to said the cell that I put the curson on.?

    Thanks YOU!!! :confused:
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Please post your existing macro code.

    Regards,
    Rollin
     
  3. AMD2800

    AMD2800 Thread Starter

    Joined:
    May 3, 2005
    Messages:
    165
    Thanks.. here it's the code...
    Sub Macro2()
    '
    ' Macro2 Macro
    ' Macro recorded 4/10/2008 by Toby Kemper
    '
    ' Keyboard Shortcut: Ctrl+d
    '
    Rows("15:18").Select
    Selection.Copy
    Range("A20").Select
    ActiveSheet.Paste
    Range("A21").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("B21").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("C21").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("E21").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("F21").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C"
    Range("A20").Select
    End Sub
     
  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Is Rows("15:18").Select always going to be the ones selected, or will you manually be selecting the range of data you want copied?
     
  5. AMD2800

    AMD2800 Thread Starter

    Joined:
    May 3, 2005
    Messages:
    165
    Actually it could be more rows. If it's easier I could select the rows and then run the macro.. I am open to anything.. LOL
     
  6. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    AMD,

    I want to try to get the code how you would like for it to work. When you have a moment, explain exactly what you would like to happen when the macro is ran, and I or someone else here will try our best to get the code to work like that. :)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "Since I record the macro it keep using A20"

    ... because the code specifies exactly where to paste (A20).

    "how to I change the macro to said the cell that I put the curson on.?"

    As long as the cell that the cursor is on is in column A,

    Rows("15:18").Copy ActiveCell

    should do the job.

    HTH :)
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Bomb,

    The problem with your solution is that the source rows may change. I think it's best for the OP to select the rows to copy manually and then run a macro to copy and paste to the destination. This may be a good scenario for an inputbox that prompts the user to enter the destination row number.

    Regards,
    Rollin
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Agree totally. The thing is the goalposts moved -- it started out as "destination is variable" and developed into "source and destination is variable". At that stage, you can't have >1 focus.

    Might be best to prompt twice (for source and dest). John W did some code once for Creating a UserForm Programmatically which could accommodate a RefEdit control. Could be overkill though. (?)
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/702416

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice