Excel macro to copy cell I4 to active cell

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,

I'm trying to write a macro to copy data in cell I4 to the cell I select (also in column I), but I'm confused between absolute and relative cell referrences. Can anyone help?

Many Thanks





3 replies

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi CS Macro,

Absolute and relative cell references.
When writing a function it is automatically a relative cell reference. Meaning that when you drag the cell, the cell reference will change.
Putting a $ sign in front of the column letter makes it absolute. Meaning that when you drag the cell to the right, the column letter will not change.
Same goes for putting a $ sign in front of the row number.
You can toggle between the available cell references by hitting F4.

Hope that make sense.

To achieve what you want you don't have to think about absolute and relative cell references.
Just use this macro:
Sub test()
Dim x As Integer
x = InputBox("Which row to copy to?")
Range("I4").Copy Cells(x, 9)
Application.CutCopyMode = False
End Sub

Best regards,
Trowa
TrowaD,
Please can you repost your response as for some reason I can't read it?
Many Thanks for your help.
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Strange, but here is my message:

Hi CS Macro,

Absolute and relative cell references.
When writing a function it is automatically a relative cell reference. Meaning that when you drag the cell, the cell reference will change.
Putting a $ sign in front of the column letter makes it absolute. Meaning that when you drag the cell to the right, the column letter will not change.
Same goes for putting a $ sign in front of the row number.
You can toggle between the available cell references by hitting F4.

Hope that make sense.

To achieve what you want you don't have to think about absolute and relative cell references.
Just use this macro:

Sub test()
Dim x As Integer
x = InputBox("Which row to copy to?")
Range("I4").Copy Cells(x, 9)
Application.CutCopyMode = False
End Sub

Best regards,
Trowa
Thank you Trowa. This is working really well, but is there any way I can remove the requirement to type in the row number? The macro will always be run from the cell I need to paste to.
Thanks.
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Sure thing CS Macro,

The macro will then look like this:
Sub test()
Range("I4").Copy Destination:=ActiveCell
End Sub

Best regards,
Trowa
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
I just learned that non-members can't see comments (thanks Aquarelle), so this message is a "reply", which you should be able to see.

Hi CS Macro,

Absolute and relative cell references.
When writing a function it is automatically a relative cell reference. Meaning that when you drag the cell, the cell reference will change.
Putting a $ sign in front of the column letter makes it absolute. Meaning that when you drag the cell to the right, the column letter will not change.
Same goes for putting a $ sign in front of the row number.
You can toggle between the available cell references by hitting F4.

Hope that make sense.

To achieve what you want you don't have to think about absolute and relative cell references.
Just use this macro:

Sub test() 
Dim x As Integer 
x = InputBox("Which row to copy to?") 
Range("I4").Copy Cells(x, 9) 
Application.CutCopyMode = False 
End Sub

Best regards,
Trowa