Excel macro question..

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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:
 

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
 
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?
 

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
 
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. :)
 
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 :)
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Joined
Jul 1, 2005
Messages
8,546
Rollin_Again said:
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
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. (?)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top