VBA Row splitting into columns Excel

Hi Nicole

Thanks, I understand now! (please ignore the last reply)

Here is the macro code that will divide up numbers 1 to 10,000 (in cells A1:A10000) on Sheet1 into column blocks of 500 numbers on Sheet2 (in columns A:T)

Sub CopyCols()

Dim RowNum As Long

Sheets("Sheet1").Select

For RowNum = 1 To 10000 Step 500

Range("A" & RowNum & ":A" & RowNum+499).Select
Selection.Copy
Sheets("Sheet2").Select
Range("AF1").Select
Selection.End(xlToLeft).Select 'same as ctrl left
ActiveCell.Offset(0, 1).Select 'select 1 cell to right
ActiveSheet.Paste
Sheets("Sheet1").Select
Next RowNum

Sheets("Sheet2").Columns("A").Delete ' delete A Col

End Sub

How the macros works

The first 500 cells in Sheet1 are first copied and pasted into B1 on Sheet2. Then with the help of the RowNum variable the next 500 cells are copied (501 to 1000). By changing RowNum in steps of 500 the process continues till all data is copied.

Hope this helps with your actual example.

Doug Dunn
Best STL