Macro button that copies the last column of a table including their formulas onto the end of the table continuously?

[Hasanul]

I really need help () to create a button that lets me continuously copy and paste columns from the end of a table. So each time I hit the button it will copy the last 2 columns and paste them into the next 2 columns and again and again using the same button.

I have been able to make a button that does it once () but then it copies the same cells and pastes in the same place it doesnt move across the table ()

I have attatched two screenshots that hopefully help illustrate what I really need .

Really appreciate any help, thank you

 

[jkpieterse]

Why don't you post that code, perhaps the fix is simple!

 

[Hasanul]

Awesome:

Here is the code for the Button I managed to create so far...appreciate any help. It just copies from the same cells and not the end of the table.

Sub Macro1()
'
' Macro1 Macro
'

'
Range("G2:H15").Select
Selection.Copy
Range("I2").Select
ActiveSheet.Paste
Range("I2").Select
End Sub

 

[jkpieterse]

Try this.

1. Select the left most cell on the first row of the table
2. Make sure both the vba editor and excel are side by side
3. Turn on macro recorder
4. Enter macro name and location and click ok
5. In the vba editor open the module containing your new macro so you can see what gets recorded
6. Go back to excel
7. Before doing anything else, on the developer toolbar turn on relative recording
8. Press control+right arrow
9. You should now arrive at the right most column
10. Click the left arrow key once
11. Hold down shift and press right arrow, followed by control-shift pressing the down arrow
12. Press control c
13. Press control +right until you end up in column XFD
14. Press left arrow once and right arrow once (the idea is to get excel to record you are in that last column)
15. Press control left arrow followed by just right arrow (should take you to the first empty column)
16. Paste and stop recording

 

[johnnyL]

How about:

    Dim LastUsedColumnInRow As Long
'
    LastUsedColumnInRow = Rows(2).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
'
    Range(Cells(2, LastUsedColumnInRow - 1), Cells(15, LastUsedColumnInRow)).Copy Cells(2, LastUsedColumnInRow + 1)

 

[Hasanul]

Try this.

1. Select the left most cell on the first row of the table
2. Make sure both the vba editor and excel are side by side
3. Turn on macro recorder
4. Enter macro name and location and click ok
5. In the vba editor open the module containing your new macro so you can see what gets recorded
6. Go back to excel
7. Before doing anything else, on the developer toolbar turn on relative recording
8. Press control+right arrow
9. You should now arrive at the right most column
10. Click the left arrow key once
11. Hold down shift and press right arrow, followed by control-shift pressing the down arrow
12. Press control c
13. Press control +right until you end up in column XFD
14. Press left arrow once and right arrow once (the idea is to get excel to record you are in that last column)
15. Press control left arrow followed by just right arrow (should take you to the first empty column)
16. Paste and stop recording

Hey I tried to follow your steps but unfortunately I messed up or it didnt work. It still copies only one time and not adding to the end of the table continuously...please see the code for ref

Sub Test2()
'
' Test2 Macro
'

'
Range("A2").Select
Selection.End(xlToRight).Select
Range("I2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Range("XFD2").Select
Selection.End(xlToLeft).Select
Range("K2").Select
ActiveSheet.Paste
End Sub

 

[Hasanul]

How about:

    Dim LastUsedColumnInRow As Long
'
    LastUsedColumnInRow = Rows(2).Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
'
    Range(Cells(2, LastUsedColumnInRow - 1), Cells(15, LastUsedColumnInRow)).Copy Cells(2, LastUsedColumnInRow + 1)

Hello Johnny how do I paste the code in? I know how to record a Macro or follow any steps you give but not sure where I paste existing code in.

 

[johnnyL]

In the code that you posted in your last post, delete everything between the top and bottom line and then paste in the middle the code from my last post.

 

[Hasanul]

In the code that you posted in your last post, delete everything between the top and bottom line and then paste in the middle the code from my last post.

Awesome it works! thank you so much!

 

[Hasanul]

Try this.

1. Select the left most cell on the first row of the table
2. Make sure both the vba editor and excel are side by side
3. Turn on macro recorder
4. Enter macro name and location and click ok
5. In the vba editor open the module containing your new macro so you can see what gets recorded
6. Go back to excel
7. Before doing anything else, on the developer toolbar turn on relative recording
8. Press control+right arrow
9. You should now arrive at the right most column
10. Click the left arrow key once
11. Hold down shift and press right arrow, followed by control-shift pressing the down arrow
12. Press control c
13. Press control +right until you end up in column XFD
14. Press left arrow once and right arrow once (the idea is to get excel to record you are in that last column)
15. Press control left arrow followed by just right arrow (should take you to the first empty column)
16. Paste and stop recording

Thank you so much