Stack first 100 rows of 4 column group on top of each other VBA

[eddster]

Hi All

So, I have a large data set which has four standard column inputs lets say A, B, C, D (but many (100's) of them working across the page from left to right so A,B,C,D,A,B,C,D,A,B,C,D ETC) I want to stack the data so that all the A's are stacked together in an new A column on on a separate worksheet then all the B's adjacent to A on the the worksheet etc etc. I also only want to pull the data from the first 100 rows of each column,

hopefully the table below shows what i mean:

in terms of data size I currently have circa 726 columns of A's, 726 columns of B's, 726 columns of C's 726 columns of D's currently in that patern of A,B,C,D,A,B,C,D,A,B,C,D.......

Any help gratefully received, ideally a macro I think as my non macro routes for this causing things to hang a bit

A1	B1	C1	D1	A2	B2	C2	D2	A3	B3
ROWS 1- 100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100	ROWS 1-100
so in new worksheet
A	B	C	D
A1 ROWS 1 - 100	B1 ROWS 1 - 100	C1 ROWS 1 - 100	D1 ROWS 1 - 100
A2 ROWS 1 -100	B2 ROWS 1 - 100	C2 ROWS 1 - 100	D2 ROWS 1 - 100
A3 ROWS 1- 100	B3 - ROWS 1 -100	C3 ROWS 1- 100	D3 ROWS 1 - 100
ETC	ETC	ETC	ETC

 

[Peter_SSs]

Assuming original data is on 'Sheet1' and 'Sheet2' exists with nothing on it, try this with a copy of your workbook.

Sub Stack_Cols()
  Dim c As Long
  
  With Sheets("Sheet1")
    For c = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column Step 4
      .Cells(1, c).Resize(100, 4).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next c
  End With
End Sub