Copy/Paste a Column X number of Times

[NocoffeeNoWorkee]

The question seems simple but I cant find an answer.

Using a macro, I need to copy and paste a column X number of times to the right on the same sheet. X is given by cell A1 on Sheet 2. The rows in the column will probably vary each time I import data.

I'm using excel 2013. I cant write this with the macro recorder and I'm a noob.

 

[My Aswer Is This]

Your quote:
I need to copy and paste a column X number of times
What column on sheet(2) do we want to copy x number of times?

 

[NocoffeeNoWorkee]

I wanted to copy Sheet 1 column A and get X from a certain cell. I thought sheet 2 A1 would be convenient for X because the amount of rows will change.

 

[My Aswer Is This]

Try this:
It gets the number of times from a Inputbox

Sub Copy_Columns()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Dim num As Long
On Error GoTo M

num = InputBox("How many times do you want to copy Column ""A""?")
    For i = 2 To num + 1
        Range("A1" & ":" & "A" & Lastrow).Copy Destination:=Cells(1, i)
    Next
    Application.ScreenUpdating = True

    Exit Sub
    
M:
MsgBox "That is a invalid number"
Application.ScreenUpdating = True

End Sub

 

[NocoffeeNoWorkee]

Its fantastic , thanks.

 

[My Aswer Is This]

Glad I was able to help you. Helping people here keeps my brain working. It's much better then watching the tube or chatting on FB.Come back here to Mr. Excel next time you need additional assistance.

Its fantastic , thanks.

 

[Rick Rothstein]

Try this:
It gets the number of times from a Inputbox

Sub Copy_Columns()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Dim num As Long
On Error GoTo M

num = InputBox("How many times do you want to copy Column ""A""?")
    For i = 2 To num + 1
        Range("A1" & ":" & "A" & Lastrow).Copy Destination:=Cells(1, i)
    Next
    Application.ScreenUpdating = True

    Exit Sub
    
M:
MsgBox "That is a invalid number"
Application.ScreenUpdating = True

End Sub

You can simplify your code a little bit...

[table="width: 500"]
[tr]
	[td]Sub Copy_Columns()
  Dim Num As Long
  Application.ScreenUpdating = False
  Num = Application.InputBox("How many times do you want to copy Column ""A""?", Type:=1)
  If Not Num Like "*[!0-9]*" Then
    Columns("A").Copy Columns("B").Resize(, Num)
  Else
    MsgBox "Whole numbers greater than zero only!"
  End If
End Sub[/td]
[/tr]
[/table]