Simple macro to shift updates to an archived cell

phogon

New Member
Joined
Aug 22, 2018
Messages
7
I have a worksheet with 2 columns.

Column 1 is Latest update.
Column 2 is Archive update.

I want to write a macro that would detect the cell I've selected, copy its contents, and paste it into the top of the archived update cell.

BEFORE:

CELL A1 CELL B1
Update 5 Update 4
Update 3
Update 2
Update 1

After I select cell A1 and run the Macro

AFTER:

CELL A1 CELL B1
Update 5
Update 4
Update 3
Update 2
Update 1

I'm imagining a macro tht copy cells, offset (0,1), then paste content but I'm havin trouble retining existing data in the Column B cells
 
I think you need to show a before and after results:

Your original post had that but then you said that was incorrect and in your second example it was more confusing to me. And did not show a before and after.

And you never answered this question which I earlier asked:

And are you saying the updates have to stay in order in column A
So the script has to look at the last character which in this case is 4 and insert it between 5 and 3
Or can the latest update go to the top of the list in column A

Thank for your time. Let me try again but to answer your question goin to the top of the list in Column B (the archive column) is fine.

Imagine a football game. Column A is where I add in the ltest score. Column B is an archive of all score movements.

So there are 3 games in row 1,2,3 respectively.

Game 1 has a goal, so I type 1-0 into A1.
Game 1 has another goal, so I select cell A1, and press macro. 1-0 goes to cell B1. Then I type 1-1 in cell A1.
Game 1 has anothr goal, so I select cell A1 and press macro. 1-1 goes to TOP of cell B1, then I type 2-1 in cell A1.

Game 2 has a goal, so I type 1-0 into A2.
Game 3 has a goal, so I type 1-0 into A3.
Game 2 has anothr goal, so I press the macro, 1-0 goes to cell B2. Then I type 2-0 in cell A2.

And so on.

At the end, Column A willshow the latest score, Column B will show score History of each game (i.e. Cell B2 would eventully be something like:


5-4
4-4
3-4
2-4
2-3
2-2
2-1
1-1
1-0

Thanks! (this is for project updates, not sport, so the test copied will be more complex.)
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Can it be one where it only triggrs when Ipress a button. Basically I want to be able to select cell A15, click the button, thn the contents get moved to the top of cell B15.

Yes

is this what you want?
Press button \ value in A16 moves down \ value in B15 copied to A16 \ value in A15 copied to B15 \ delete value in A15
 
Upvote 0
Yes

is this what you want?
Press button \ value in A16 moves down \ value in B15 copied to A16 \ value in A15 copied to B15 \ delete value in A15

No. Please refer to my football example above.

Select cell A16 \ Press button \ text in A16 is copied \ text in A16 is ADDED to the top of text in B16 \ delete value in A16

or

Select cell A1005 \ Press button \ text in A1005 is copied\ text is A1005 is ADDED to the top of text B1005 \ Delete value in A1005
 
Upvote 0
text in A16 is ADDED to the top of text in B16

What does this really mean :confused:

How about...

Add the value currently in A16 and insert it as the first line of text in cell B16 above existing text in B16
OR
Insert a cell above B16 and move the other cells in column B down
Copy the value of A16 into B16
 
Upvote 0
Sounds like to me you are wanting more then one value in the cells in Column B

You keep saying:

1-1
goes to TOP of cell B1

Meaning you want more then one value in B1

You are not saying goes to the Top of column B

 
Upvote 0
Add the value currently in A16 and insert it as the first line of text in cell B16 above existing text in B16

This is exactly it. Thanks and sorry.
 
Upvote 0
Well now we know that. You want to keep adding more and more data to the same cell

So B16 may look like this:

1-0
2-1
3-2

And on and on.

I would not call this a "Simple" Macro

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
Try this...
Code:
Private Sub CommandButton1_Click()
    Dim celA As Range, celB As Range
    Set celA = ActiveCell
    Set celB = celA.Offset(, 1)
    If celA.Column > 1 Then Exit Sub

    celB.Value = celA.Value & Chr(10) & celB.Value
End Sub

This does one cell
Do you want the whole column updating?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top