I have a problem with a VBA code. I'm trying to create a file where you place a long list in column A and it divides it into smaller ones with 45 rows - so I want the first 45 rows from the long list seperated into smaller list 1 with 45 rows, next 45 rows from the long list into smaller list2 with 45 rows etc. Here is the code:
Sub COPY()
Dim a As Variant
a = Array("J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For i = 0 To 16
For j = LBound(a) To UBound(a)
Range("A" & 1 + 45 * i & ":A" & 46 + 45 * i).Select
Selection.COPY
Range(a(j) & "3:" & a(j) & "47").Select
ActiveSheet.Paste
Next i
Next j
End Sub
The problem that I'm having is that it copies the first 45 rows - then it pastes it into all the colums. I want it to paste only into the first column - then brake, copy next 45 rows and paste it into the 2 column and I don't know how to get around that.
Don't crack your head - use Excel on the full speed!
Sub Columnisator()
Dim r As Range
Set r = [A1].CurrentRegion ' r can be the input parameter like Columnisator(r As Range)
Dim a
a = Application.Evaluate("WRAPROWS(" & r.Address & ",45)")
[J3].Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub
[A1] is the source top cell. [J3] is the destination top left cell.
If you want to cycle in VBA, use this code:
Sub COPY()
Dim a As Variant, i As Long
a = Application.Evaluate("CHAR(SEQUENCE(,17,74))")
For i = 1 To UBound(a)
Cells(i * 45 - 44, 1).Resize(45, 1).COPY Cells(3, a(i))
Next i
End Sub