* New to VBA but experience with C, Matlab, and Python
I'm trying to create a macro that allows me to copy columns and then paste those selected columns x times to the right of the original selection.
e.g.
original columns: u v w x y z
columns to copy twice: x y z
new columns: u v w x y z x y z x y z
Challenges:
- Not all columns have the same number of rows (some are shorter than desired range and some are longer)
- Every third column is blank until row 61
Right now my macro looks like this:
Sub CopyColumns()
Dim CopyRange As Range, x As Long, nextcolumn As Long, i As Long, lrow As Long
Set CopyRange = Application.InputBox(Prompt:="Enter range to copy:", Type:=8)
x = InputBox("Enter number of times to paste:")
lrow = CopyRange.Rows.Count
With ActiveWorkbook.ActiveSheet
For i = 1 To x
nextcolumn = .Cells(lrow, CopyRange.Columns.Count).End(xlToRight).Column + 1
CopyRange.Copy .Cells(1, nextcolumn)
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
So instead of inserting where my input range ends, it inserts at the first instance where there is no value in the 10th row (+1)...
Please help
Bookmarks