Make 2 long columns into 8 shorter columns

  • Thread starter Thread starter Mike Fogleman
  • Start date Start date
M

Mike Fogleman

I have 2 long columns of numbers on sheet1, columns A&B, sorted descending
by column A. The number of rows of data will vary according to what is
imported. I want to put them on sheet2 for printout, in the same order but
as 8 columns wide, starting at B5. Basically it is a copy/paste the first
1/4th of sheet1, columns A:B to sheet2 B5. Then the second 1/4th of sheet1
to sheet2 E5, etc. Any ideas on how to get this started would be greatly
appreciated.
TIA, Mike
 
Mike,

Start by counting the number of rows in the source list (call this number
RowCount) and divide this number by 4. The first 3 'quarters' will have a
column length (ColLength) of

Int(RowCount/4).

The last 'quarter' will have a column length of

RowCount - 3*ColLength

Using these numbers, define the 4 ranges as rngSource1, rngSource2, ....
Do the same to define the destination ranges: rngDest1, rngDest2, ....

So, for example, if the original list has 17 rows, then ColLength will be 4
and the last column will have a length of 5.
rngSource1 will be Range(Cells(1,1), Cells(4,2)) and rngDest1 will be
Range(Cells(5,2), Cells(8,3)).

You can right this code like this:

With Sheets(1)
Set rngSource1 = Range(.Cells(1,1), .Cells(4,2))
End With
With Sheets(2)
Set rngDest1 = Range(.Cells(5,2), .Cells(8,3))
End With

Then "copy" the ranges using

rngDest1.Value = rngSource.Value

HTH,
Shockley
 
Back
Top