Populating an Excel companion sheet with names continuing in a second - Microsoft Community

have excel sheet of club members , personal data , want format 2 other sheets meeting signs , outing sign ups.  for these other sheets, need names , place make check mark.  that part easy.  the tricky part club director wants names flow 2 different columns , since membership varies, cannot use formula ='2016'!a3.  they equal number of names in each column.  is there way first factor in total count , populate names accordingly?  for example, if 80 members, 40 names in each column.  but if 84, 42 per column.  i hope question clear.  thanks!

this vba code should you. you'll have replace sheet names actuals. splits , copies names in column column , c of other 2 sheets after clearing names there.

sub pop_names()
dim lastrow long
dim halfrow long

dim moveamount long

dim x long

'clear names sheet2
worksheets("sheet2")
    lastrow = .cells(.rows.count, "a").end(xlup).row
    if lastrow > 1 then
        .range("a2:a" & lastrow).clearcontents
        .range("c2:c" & lastrow).clearcontents
    end if
end with

'clear names sheet3
worksheets("sheet3")
    lastrow = .cells(.rows.count, "a").end(xlup).row
    if lastrow > 1 then
        .range("a2:a" & lastrow).clearcontents
        .range("c2:c" & lastrow).clearcontents
    end if
end with

'get last row of names
worksheets("sheet1")
    lastrow = .cells(.rows.count, "a").end(xlup).row
end with

'calc half of row length copying
halfrow = int(lastrow / 2) + 1

'copy half of names columns a
worksheets("sheet1").range("a2:a" & halfrow).copy worksheets("sheet2").range("a2")
worksheets("sheet1").range("a2:a" & halfrow).copy worksheets("sheet3").range("a2")

'copy remainder of names columns c
if (lastrow - 1) mod 2 = 0 then
    moveamount = lastrow - halfrow + 2
else
    moveamount = lastrow - halfrow + 3
end if

worksheets("sheet1").range("a" & moveamount & ":a" & lastrow).copy worksheets("sheet2").range("c2")
worksheets("sheet1").range("a" & moveamount & ":a" & lastrow).copy worksheets("sheet3").range("c2")

end sub

Office / Excel / Mac / Office 365 Home