Simple Macro - I want to repeat screen moves, not select "ranges"
I'm simply trying to replicate identical screen moves of a cursor.
I have 2 columns of data.
The last row of this data has formulas in it.
When I run the macro, I simply want to copy that last row of formulas, to the row
directly beneath it.
Then, turn the original last row (now the next to the last row) into values, by doing a
"Paste value".
When recording the macro, this simply does the same actions to the same cells, (even though I recorded it with "END, DOWN ARROW", etc., etc.
I thought that macros were recorded, "literally" with each move of the cursor.
If resolving, please make the answer specific relevant to the code below, or any specific declarations, etc. required.
Thanks.
Sub Macro2()
'
' Macro2 Macro
' part 2
'
' Keyboard Shortcut: Ctrl+i
'
Range("C2").Select
Selection.End(xlDown).Sele
Range("C5:D5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C5:D5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
I have 2 columns of data.
The last row of this data has formulas in it.
When I run the macro, I simply want to copy that last row of formulas, to the row
directly beneath it.
Then, turn the original last row (now the next to the last row) into values, by doing a
"Paste value".
When recording the macro, this simply does the same actions to the same cells, (even though I recorded it with "END, DOWN ARROW", etc., etc.
I thought that macros were recorded, "literally" with each move of the cursor.
If resolving, please make the answer specific relevant to the code below, or any specific declarations, etc. required.
Thanks.
Sub Macro2()
'
' Macro2 Macro
' part 2
'
' Keyboard Shortcut: Ctrl+i
'
Range("C2").Select
Selection.End(xlDown).Sele
Range("C5:D5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C5:D5").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Martin Liss
I don't think I understand what you want. What I did was to but the values 1, 2, 3, 4 in cells C2, C3, C4 and C5. I then put a simple formula (=C5*5) in cell D5 so D5 became 20. When I ran your macro the value in C5 was copied to C6, the formula in D5 was copied and adjusted to D6 so it became =C6*5 and the formula in C5 was removed but the value of 20 remained. What do you want that's different than that?
I corrected a typo in my question above but I still got it wrong.
...and the formula in D5 was removed...
...and the formula in D5 was removed...
ASKER
The very last ROW, should always have formulas.
No matter how long the list grows, everything will be "pure values", except for that last row.
The way Excel "records" the macro, it only acts on the same cells each time.
That's why, the "variable", really is the "END (keyboard button) DOWN ARROW (keyboard button)".
But it doesn't record that way. It records as "Cell B5". I need that destination to "expand, one cell lower, each time it is run".
No matter how long the list grows, everything will be "pure values", except for that last row.
The way Excel "records" the macro, it only acts on the same cells each time.
That's why, the "variable", really is the "END (keyboard button) DOWN ARROW (keyboard button)".
But it doesn't record that way. It records as "Cell B5". I need that destination to "expand, one cell lower, each time it is run".
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Almost there.
That works, but not for both columns (of just the last row).
I have 2 columns:
Date Amount
6/15/2013 1,000
6/16/2013 1,250
TODAY() =OTHER $Absolute reference$ CELL TOTAL
I need to copy both of those down.
Paste value on the "original" ones.
and make sure the last row always has those formulas.
Thanks for your help.
I still don't understand why, when I do a "record macro", it's turning my
screen and keyboard moves into VB, but more specifically, VB that is not literal to what is on the screen. To me, that's not recording crap.
Thanks for your help.
That works, but not for both columns (of just the last row).
I have 2 columns:
Date Amount
6/15/2013 1,000
6/16/2013 1,250
TODAY() =OTHER $Absolute reference$ CELL TOTAL
I need to copy both of those down.
Paste value on the "original" ones.
and make sure the last row always has those formulas.
Thanks for your help.
I still don't understand why, when I do a "record macro", it's turning my
screen and keyboard moves into VB, but more specifically, VB that is not literal to what is on the screen. To me, that's not recording crap.
Thanks for your help.
I don't understand this
=OTHER $Absolute reference$ CELL TOTAL
In your example should it be the sum of the rows above, in other words 2,250?
=OTHER $Absolute reference$ CELL TOTAL
In your example should it be the sum of the rows above, in other words 2,250?
ASKER
No. It's just a reference to a cell (on another sheet)
that needs to stay the same reference, each time it's copied down.
i.e. =$Q$99
The dollar signs ensure that the Q doesn't change when copied.
that needs to stay the same reference, each time it's copied down.
i.e. =$Q$99
The dollar signs ensure that the Q doesn't change when copied.
In my code in line 9 has Paste:=xlPasteFormulas so whatever formula is in the last row of column D will be copied to the new last row of column D. A formula like =$Q$99 that has two absolute references will be copied without any adjustment, so what is wrong? In other words exactly what to you get as the formula in the new last line and what do you want?
BTW I forgot to mention that line 7 should be
Cells(lngLastRow + 1, 3).Value = Date
if you want the last row to show the current date.
Cells(lngLastRow + 1, 3).Value = Date
if you want the last row to show the current date.
ASKER
It was only doing it for one column.
Based on your instruction and the bulk of your code, this is what I came up with that works for both column.
Only one of the columns was being selected, then copied.
So I needed to add that loop a second time....this works.
Thanks for your help!
Sub AddRow()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("C65536").End(xlUp).
Cells(lngLastRow, 3).Copy
Cells(lngLastRow + 1, 3).PasteSpecial Paste:=xlPasteFormulas
Cells(lngLastRow, 4).Copy
Cells(lngLastRow + 1, 4).PasteSpecial Paste:=xlPasteFormulas
Cells(lngLastRow, 4).Select
Cells(lngLastRow, 4).PasteSpecial Paste:=xlPasteValues
Cells(lngLastRow, 3).Copy
Cells(lngLastRow, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
Based on your instruction and the bulk of your code, this is what I came up with that works for both column.
Only one of the columns was being selected, then copied.
So I needed to add that loop a second time....this works.
Thanks for your help!
Sub AddRow()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("C65536").End(xlUp).
Cells(lngLastRow, 3).Copy
Cells(lngLastRow + 1, 3).PasteSpecial Paste:=xlPasteFormulas
Cells(lngLastRow, 4).Copy
Cells(lngLastRow + 1, 4).PasteSpecial Paste:=xlPasteFormulas
Cells(lngLastRow, 4).Select
Cells(lngLastRow, 4).PasteSpecial Paste:=xlPasteValues
Cells(lngLastRow, 3).Copy
Cells(lngLastRow, 3).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
You're welcome and I'm glad I was able to help.
Marty - MVP 2009 to 2013
Marty - MVP 2009 to 2013