copy cell with macro and increment down each time

[RJJ]

I need to create a macro that copies two adjacent cells A148 & B148 (same
cells each time but with a different value each time) and pastes them into
another column but moving down one row each time. The object is to retain the
previous values ultimately resulting in a column of various data values. I am
able to copy and paste with a macro but when run, it overwrites the previous
value because the paste was assigned to a particular cell. Or, is it somehow
possible to store the first set of values elsewhere before overwriting?

 

[Gary''s Student]

Will will use column Y & Z:

Sub rjj()
Set r = Range("A148:B148")
n = Cells(Rows.Count, "Y").End(xlUp).Row + 1
r.Copy Cells(n, "Y")
End Sub

 

[RJJ]

I'm sorry but I don't quite understand. Should I edit my existing "copy
macro"? This is what I have now that copies to the same cell each time. The
reason it is a "Paste Special" is because I am excluding the formula in the
cell I am copying from.


Sub SaveCells()
'
' SaveCells Macro
'

'
Range("A148:B148").Select
Selection.Copy
Range("E148:F148").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[Gary''s Student]

No, continue using your macro.

Just replace the single line:

Range("E148:F148").Select

with

n = Cells(Rows.Count, "E").End(xlUp).Row + 1
Range("E" & n & ":F" & n).Select

 

[RJJ]

Thank You, works great but now:

It only works with the macro button on the original worksheet. I assigned
the macro to a new button on sheet "PO-LLC". but it does not fill the cells
in sheet "P.O.# Usage" The original sheet is "P.O. # Usage". My now working
macro (only on original sheet) is:

Range("A1:B1").Select
Selection.Copy
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & n & ":B" & n).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
End Sub

I've noticed that when I activate the macro button that resides on the
PO-LLC sheet, it tries to populate cells A2 and B2 on that sheet although
nothing appears in them. They only highlight when I activate the macro
button. I hope that makes sense. I can't tell you how much I appreciate your
help with this.

 

[Gary''s Student]

I am glad we are making progress. I don't know how your buttons are setup,
but let's say we have ten sheets with a button on each sheet. Create a
single macro in a module (bring up VBA and Insert > Module). Then you can
connect each button with the same macro. When the macro runs, it should run
on the active sheet.

 

[RJJ]

I can "bring up VBA and Insert Module, but then what? Now, when I set up a
macro, I record it with my mouse clicks and then stop recording. That said,
I'm not sure what to do when I open a new module via VBA. As far as buttons