Looping Copy and Paste

[NavyPlayboy]

Hello,

I am just getting back into using excel macros. This is the very basic macro I am using right now:

Sub AddColumn()
'
' AddColumn Macro
' Macro recorded 9/13/2011 by
'

'
Range("A1").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A1").Select
Selection.End(xlToRight).Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.EntireColumn.AutoFit
Range("A1").Select
End Sub


This copies the last column of data in my spreadsheet and pastes that data in the following column. It only does it once but I am trying to get it to repeat x number of times without having to re-run the macro. I eventually want to get to the point where a prompt will ask how many columns you would like to add and then have to macro loop that many times. But for now I would settle for just finding out how to get the dang thing to loop. Any help would be appreciated, and I'm a beginner, please be gentle

 

[njimack]

Welcome to the board.

You don't need to select cells to work with them. Something like this:

Sub AddColumn()
Dim LC As Long
Dim LR As Long
Dim No As Integer
Dim i As Long
No = InputBox("How many times would you like to copy/paste the column?", , 1)
 
LC = Range("A1").End(xlToRight).Column
LR = Cells(1, LC).End(xlDown).Row
Range(Cells(1, LC), Cells(LR, LC)).Copy
For i = 1 To No
    Cells(1, LC).Offset(, i).PasteSpecial xlPasteAll
Next i
Application.CutCopyMode = False
End Sub

 

[NavyPlayboy]

Thank you for your help!! That worked PERFECTLY! I'm trying to understand how this macro works though and was wondering if you could help?

I've never used the dim function but I get it, setting the dimensions so that something can be easily referenced later. But I'm confused about the LR one.

LR = Cells(1, LC).End(xlDown).Row

Is that saying select 1 cell to the right of where LC is located, then selecting the last cell down in this column? Why is '.Row' at the end?

 

[njimack]

Thank you for your help!! That worked PERFECTLY! I'm trying to understand how this macro works though and was wondering if you could help?

I've never used the dim function but I get it, setting the dimensions so that something can be easily referenced later. But I'm confused about the LR one.

LR = Cells(1, LC).End(xlDown).Row

Is that saying select 1 cell to the right of where LC is located, then selecting the last cell down in this column? Why is '.Row' at the end?

You're welcome, thanks for the feedback.

That line of code tells Excel to start from the cell where row 1 meets the last column (Cells(1,LC). It then does the equivalent of holding Ctrl and pressing the down cursor key (End(xlDown). We then read the Row of this cell so that we know on which row your data ends.