Split a column into several columns formula/macro

[Spiru]

Hi everyone,

Every month I'm exporting in excel a column with ~ 8000 rows and I need to split this column into several columns - each column having 1000 rows. E.g. Copy from column A first 1000 rows and paste them in column B, copy again from column A from row 1001-2000 and post in column C, etc.

I've looked online but I couldn't find a formula for this. I'm thinking that also a macro could help in copy the first 1000 rows and paste them in column B, after copy next 1000 rows and paste in column C (from row 1001 to 2000) and if the last column is less than 1000 then copy until it finds the last row with data in and paste in column I:

"
Sub Splitcolumn()

Dim Last_Row_Copy, Last_Row_Paste, Last_Row, lr As Long

Application.ScreenUpdating = False

Worksheets(1).Activate

Last_Row_Copy = Cells(Rows.Count, 1000).End(xlUp).Row

Range("A1:A" & Last_Row_Copy).Copy
Sheets(1).Select
Range("B1" & Last_Row_Paste).PasteSpecial Paste:=xlPasteAll
"
Could you please help with a solution?


Thank you

 

[Spiru]

You are welcome
And thank you for the feedback
Be happy and safe

Thank you! You too