Question regarding macro button.

[Olavfinnerud]

Hi,
I have made a table which is for writing hours at work. I recorded a macro where i copied the C6:C17 range and pasted it one to the rigth (D6:D17). I then assigned the macro to the button so when i press it, it adds another column to the table. The problem is that this only works the first time i press the button. I need help to make the button add a column each time i press it, (to the rigth). So when i press it the second time it should add the same column to E6:E17 and the third time F6:F17 etc. I have uploaded a picture of the sheet and the code i have is:

Sub Macro2()

Range("C6:C17").Select
Selection.Copy
Range("D6").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub


Thank you in advanced

 

[BlueHornet]

Hello, and welcome to the board.

I see that you've used the 'record macro' feature in Excel, since that's about the only method that generates the Range( "specific range address").Select format. Many of us started there, so don't be disheartened; you'll learn more as you go, and I commend you for starting the voyage you're on. (Range.Select is almost never necessary; it's just making the code longer and more tedious to edit later.)

As to your specific question, there may be an easier way to do what you're already attempting. Instead of copying and moving a range (which will obviously grow day by day as you do this), why not consider simply adding a column at C:C, which will have the same effect of 'pushing old data to the right', but without all the added steps.

 

[Joe4]

Welcome to the Board!

Try this:

Sub Macro2()

Range("C6:C17").Copy
Cells(6, Columns.Count).End(xlToLeft).Offset(0,1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

 

[Olavfinnerud]

Welcome to the Board!

Try this:

Sub Macro2()

Range("C6:C17").Copy
Cells(6, Columns.Count).End(xlToLeft).Offset(0,1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Thank you very much for the great tips, and the code you gave me worked perfectly!

 

[Olavfinnerud]

Hello, and welcome to the board.

I see that you've used the 'record macro' feature in Excel, since that's about the only method that generates the Range( "specific range address").Select format. Many of us started there, so don't be disheartened; you'll learn more as you go, and I commend you for starting the voyage you're on. (Range.Select is almost never necessary; it's just making the code longer and more tedious to edit later.)

As to your specific question, there may be an easier way to do what you're already attempting. Instead of copying and moving a range (which will obviously grow day by day as you do this), why not consider simply adding a column at C:C, which will have the same effect of 'pushing old data to the right', but without all the added steps.

Thank you for the welcome and the tips

 

[Joe4]

Thank you very much for the great tips, and the code you gave me worked perfectly!

You are welcome.
Glad we were able to help!

 

[Olavfinnerud]

I also have a problem with the text in the first row, i want to be able to write longer words/sentences in the cell and have the cell expand with the text so it fits in the column. I have tried for some hours now and cant find a solution that works.

 

[Joe4]

I also have a problem with the text in the first row, i want to be able to write longer words/sentences in the cell and have the cell expand with the text so it fits in the column. I have tried for some hours now and cant find a solution that works.

That is a whole different question, so it should be posted to a new thread.

 

[Olavfinnerud]

That is a whole different question, so it should be posted to a new thread.

Ok, I diden`t know that thanku!

 

[Joe4]

Ok, I diden`t know that thanku!

You are welcome.

The general rule of thumb is if you have a new/different question (even if it is on the same project), then you should post it to a new thread so it appears as a new, unanswered question for all to see (and it shows up on the "Unanswered threads" listing that many helpers use to locate unanswered questions).

If you have something like a clarification question about something someone posted, then it is OK to post back to the original thread.