Creating a "Double Column" Report

I have 5 fields in the "first column" of the page and the same five fields in the 2nd column of the report. So columns A:F are "Column 1", column G is a divider cell, and columns H:M are "Column 2".

I'd like to fill all of column 1 data before moving over to column two starting from Cell A4 and moving through to row 35. Instead of moving to row 36, the data (from another sheet), would continue filling in from the top of Column 2 in Cell H4 all the way throuh M35.

Finally, the data resumes populating Column 1 (on page 2) back at Cell A36 and continues that trend until all of the data is reported.

Ok, so basically I've got a master sheet full of records. I want to take that general list and split it between two main columns to condense a report (go green and save paper . I'd like to automate it with a VBA Macro to automatically create a double column report but struggling with code on how to perform loops since the data size will always vary.

See attached spreadsheet for the example.

Thanks in advance for your help on this!

Wow. This is great. It worked and it's really fast! I'll have to tweak some of the code just so I can grasp what is going on in the function, but was amazed that it worked right out of the box.

Thank you!

Oops. I guess I looked at the macro too late last night and got too excited to see all the data flow in. I realized that this macro goes down the entire left column before starting over on the right column. My goal is to populate left column page 1 first, followed by right column page 1, left column page 2/right column page 2, etc (so the data flow kind of zig-zags dow the page).

Will do. Thanks again!

Perfect! The rows on page variable helps tremendously, too. Thanks!

Originally Posted by xladept

Thanks for the rep!

Try this:

Sub SplitRepSS(): Dim wm As Worksheet, wd As Worksheet
Dim P, Q, R, c As Integer, n As Integer, u As Long
Dim k As Integer, e As Integer, d As Integer
Set wm = Sheets("Master"): Set wd = Sheets("DoubleColumn")
u = wm.UsedRange.Rows.Count: c = 3: n = 34 'rows on first page
    Do: d = IIf(k = 0, 1, 2 * k * n - 2): e = IIf(k = 0, 1, 1 + k * n)
    P = wm.Cells(d, 1).Resize(n, 4): wd.Cells(e, 1).Resize(n, 4) = P
    Q = wm.Cells(d, 6).Resize(n, 1): wd.Cells(e, 5).Resize(n, 1) = Q
    R = wm.Cells(d, 9).Resize(n, 1): wd.Cells(e, 6).Resize(n, 1) = R
P = wm.Cells(n + d, 1).Resize(n, 4): wd.Cells(e + c, 8).Resize(n - c, 4) = P
Q = wm.Cells(n + d, 6).Resize(n, 1): wd.Cells(e + c, 12).Resize(n - c, 1) = Q
R = wm.Cells(n + d, 9).Resize(n, 1): wd.Cells(e + c, 13).Resize(n - c, 1) = R
              c = 0: k = k + 1: Loop Until d > u
  wd.Cells(1, 8).Resize(3, 6).Value = wd.Cells(1, 1).Resize(3, 6).Value
End Sub

Hello,

I wanted to reach back out to you to see if you could help me out with one change I'd like to make this code work even better for me. I have headers that sometimes fall on the very last row of a column and sometimes goes missed when reading the report (i.e. you don't realize a new section of the report started because it's so far down the page). I would like to try to bump this to the next column, by adding a row before it if the header falls on the last line of the page or insert a line at the start of the new column/page that gives the Header text plus "(cont'd)"

The Header will always start with ";;ColumnHead" so perhaps knowing whether ;;ColumnHead falls on a multiple of 34 (number of rows on first page), insert row.