Merry Christmas everyone,

I highly respect the great minds that solve the many Excel riddles
that are posted on this newsgroup. I hope you are all having a great
holiday.

If anyone is so inclined or feels like solving one on their return,
here is my issue...

I have existing code below that is quite useful for its former
purpose, but I wonder if it could be modified to suit a new
application. It probably just needs a little tinkering, but who knows.

1) My raw data consists of sequential (already sorted) numbers in
column A, running for unspecified (varying, that is) lengths of rows;

2) What I'd like to do is take the data in column A (a sample of which
I've pasted below), and organize it so that I can print it on 8.5" x
11" pages that show the data as follows:

- Flowing sequentially beginning in cell A2 and then down column A on
the first page, then beginning in cell C2 and down column C on the
first page, then beginning in cell E2 and down column E, then
beginning in column G2 and down Column G;

- If there is not enough room on the first page, I'd like the
remaining data to flow on the next page with the same format (column
A, then C, E, G);

3) I want to use the first row on each page as a header row (thus, the
provision for beginning the break-out of the data beginning in the
second cell of the first page):

- I want the header row appearing on each page to be the same:
- columns A, C, E and G should say "Inst. No."
- columns B, D, F and H should say "Memo"

4) The font size for all data and for the headers will always be Arial
10;

5) The number of rows per column, per page, ought to be 57 (not
including the header, which makes it 58 essentially);

6) I use page formatting of 1.3" on all margins except the right
margin, that being set to 0.6"

7) The final column (on whichever page it may fall in accordance with
the size of my raw list of column A data, that is) may not be
completely filled to the bottom.

Note: My intention is to use columns B, D, F and H for the purpose of
allowing me to insert written comments beside each item, when using
the final printouts of data in A, C, E and G (thus they need to remain
blank.

The code I currently use is pasted below:

Application.ScreenUpdating = False
Set Rng = Range("A1").CurrentRegion
lrow = Rng.Rows(Rng.Rows.Count).Row
x = 51 'row before page break
y = 52 'row after page break
C = 1 ' first set of columns
z = C + 1 'start column of second set
N = 4 'number of columns required including first column
For Count = 1 To (lrow / (x * N / C) + 1)
Do
Range(Cells(y, 1), Cells((y + x - 1), C)).Copy _
Range(Cells(y - x, z), Cells(y - x, z))
Range(Cells(y, 1), Cells((y + x - 1), C)).Delete
Shift:=xlUp
z = z + C
Loop Until z >= N + 1 'to put data into n columns
y = y + x
z = C + 1
Next Count
Application.ScreenUpdating = True

End Sub



Sample data that I might use:

9T2266019
9T2266020
9T2266021
9T2266022
9T2266023
9T2266024
9T2266025
9T2266026
9T2266027
9T2266028
9T2266029
9T2266030
9T2266031
9T2266032
9T2266033
9T2266034
9T2266035
9T2266036
9T2266037
9T2266038
9T2266039
9T2266040
9T2266041
9T2266042
9T2266043
9T2266044
9T2266045
9T2266046
9T2266047
9T2266048
9T2266049
9T2266050
9T2266051
9T2266052
9T2266053
9T2266054
9T2266055
9T2266056
9T2266057
9T2266058
9T2266059
9T2266060
9T2266061
9T2266062
9T2266063
9T2266064
9T2266065
9T2266066
9T2266067
9T2266068
9T2266069
9T2266070
9T2266071
9T2266072
9T2266073
9T2266074
9T2266075
9T2266076
9T2266077
9T2266078
9T2266079
9T2266080
9T2266081
9T2266082
9T2266083
9T2266084
9T2266085
9T2266086
9T2266087
9T2266088
9T2266089
9T2266090
9T2266091
9T2266092
9T2266093
9T2266094
9T2266095
9T2266096
9T2266097
9T2266098
9T2266099
9T2266100
9T2266101
9T2266102
9T2266103
9T2266104
9T2266105
9T2266106
9T2266107
9T2266108
9T2266109
9T2266110
9T2266111
9T2266112
9T2266113
9T2266114
9T2266115
9T2266116
9T2266117
9T2266118
9T2266119
9T2266120
9T2266121
9T2266122
9T2266123
9T2266124
9T2266125
9T2266126
9T2266127
9T2266128
9T2266129
9T2266130
9T2266131
9T2266132
9T2266133
9T2266134
9T2266135
9T2266136
9T2266137
9T2266138
9T2266139
9T2266140
9T2266141
9T2266142
9T2266143
9T2266144
9T2266145
9T2266146
9T2266147
9T2266148
9T2266149
9T2266150
9T2266151
9T2266152
9T2266153
9T2266154
9T2266155
9T2266156
9T2266157
9T2266158
9T2266159
9T2266160
9T2266161
9T2266162
9T2266163
9T2266164
9T2266165
9T2266166
9T2266167
9T2266168
9T2266169
9T2266170
9T2266171
9T2266172
9T2266173
9T2266174
9T2266175
9T2266176
9T2266177
9T2266178
9T2266179
9T2266180
9T2266181
9T2266182
9T2266183
9T2266184
9T2266185
9T2266186
9T2266187
9T2266188
9T2266189
9T2266190
9T2266191
9T2266192
9T2266193
9T2266194
9T2266195
9T2266196
9T2266197
9T2266198
9T2266199
9T2266200
9T2266201
9T2266202
9T2266203
9T2266204
9T2266205
9T2266206
9T2266207
9T2266208
9T2266209
9T2266210
9T2266211
9T2266212
9T2266213
9T2266214
9T2266215
9T2266216
9T2266217
9T2266218
9T2266219
9T2266220
9T2266221
9T2266222
9T2266223
9T2266224
9T2266225
9T2266226
9T2266227
9T2266228
9T2266229
9T2266230
9T2266231
9T2266232
9T2266233
9T2266234
9T2266235
9T2266236
9T2266237
9T2266238
9T2266239
9T2266240
9T2266241
9T2266242
9T2266243
9T2266244
9T2266245
9T2266246
9T2266247
9T2266248
9T2266249
9T2266250
9T2266251
9T2266252
9T2266253
9T2266254
9T2266255
9T2266256
9T2266257
9T2266258
9T2266259
9T2266260
9T2266261
9T2266262
9T2266263
9T2266264
9T2266265
9T2266266
9T2266267
9T2266268
9T2266269
9T2266270
9T2266271
9T2266272
9T2266273
9T2266274
9T2266275
9T2266276
9T2266277
9T2266278
9T2266279
9T2266280
9T2266281
9T2266282
9T2266283
9T2266284
9T2266285
9T2266286
9T2266287
9T2266288
9T2266289
9T2266290
9T2266291
9T2266292
9T2266293
9T2266294
9T2266295
9T2266296
9T2266297
9T2266298
9T2266299
9T2266300
9T2266301
9T2266302
9T2266303
9T2266304
9T2266305
9T2266306
9T2266307
9T2266308
9T2266309
9T2266310
9T2266311
9T2266312
9T2266313
9T2266314
9T2266315
9T2266316
9T2266317
9T2266318
9T2266319
9T2266320
9T2266321
9T2266322
9T2266323
9T2266324
9T2266325
9T2266326
9T2266327
9T2266328
9T2266329
9T2266330
9T2266331
9T2266332
9T2266333
9T2266334
9T2266335
9T2266336
9T2266337
9T2266338
9T2266339
9T2266340
9T2266341
9T2266342
9T2266343
9T2266344
9T2266345
9T2266346
9T2266347
9T2266348
9T2266349
9T2266350
9T2266351
9T2266352
9T2266353
9T2266354
9T2266355
9T2266356
9T2266357
9T2266358
9T2266359
9T2266360
9T2266361
9T2266362
9T2266363
9T2266364
9T2266365
9T2266366
9T2266367
9T2266368
9T2266369
9T2266370
9T2266371
9T2266372
9T2266373
9T2266374
9T2266375
9T2266376
9T2266377
9T2266378
9T2266379
9T2266380
9T2266381
9T2266382
9T2266383
9T2266384
9T2266385
9T2266386
9T2266387
9T2266388
9T2266389
9T2266390
9T2266391
9T2266392
9T2266393
9T2266394
9T2266395
9T2266396
9T2266397
9T2266398
9T2266399
9T2266400
9T2266401
9T2266402
9T2266403
9T2266404
9T2266405
9T2266406
9T2266407
9T2266408
9T2266409
9T2266410
9T2266411
9T2266412
9T2266413
9T2266414
9T2266415
9T2266416
9T2266417
9T2266418
9T2266419
9T2266420
9T2266421
9T2266422
9T2266423
9T2266424
9T2266425
9T2266426
9T2266427
9T2266428
9T2266429
9T2266430
9T2266431
9T2266432
9T2266433
9T2266434
9T2266435
9T2266436
9T2266437
9T2266438
9T2266439
9T2266440
9T2266441
9T2266442
9T2266443
9T2266444
9T2266445
9T2266446
9T2266447
9T2266448
9T2266449
9T2266450
9T2266451
9T2266452
9T2266453
9T2266454
9T2266455
9T2266456
9T2266457
9T2266458
9T2266459
9T2266460
9T2266461
9T2266462
9T2266463
9T2266464
9T2266465
9T2266466
9T2266467
9T2266468
9T2266469
9T2266470
9T2266471
9T2266472
9T2266473
9T2266474
9T2266475
9T2266476
9T2266477
9T2266478
9T2266479
9T2266480
9T2266481
9T2266482
9T2266483
9T2266484
9T2266485
9T2266486
9T2266487
9T2266488
9T2266489
9T2266490
9T2266491
9T2266492
9T2266493
9T2266494
9T2266495
9T2266496
9T2266497
9T2266498
9T2266499
9T2266500
9T2266501
9T2266502
9T2266503
9T2266504
9T2266505
9T2266506
9T2266507
9T2266508
9T2266509
9T2266510
9T2266511
9T2266512
9T2266513
9T2266514
9T2266515
9T2266516
9T2266517
9T2266518
9T2266519
9T2266520
9T2266521
9T2266522
9T2266523
9T2266524
9T2266525
9T2266526
9T2266527
9T2266528
9T2266529
9T2266530
9T2266531
9T2266532
9T2266533
9T2266534
9T2266535
9T2266536
9T2266537
9T2266538
9T2266539
9T2266540
9T2266541
9T2266542
9T2266543
9T2266544
9T2266545
9T2266546
9T2266547
9T2266548
9T2266549
9T2266550
9T2266551
9T2266552
9T2266553
9T2266554
9T2266555
9T2266556
9T2266557
9T2266558
9T2266559
9T2266560
9T2266561
9T2266562
9T2266563
9T2266564
9T2266565
9T2266566
9T2266567
9T2266568
9T2266569
9T2266570
9T2266571
9T2266572
9T2266573
9T2266574
9T2266575
9T2266576
9T2266577
9T2266578
9T2266579
9T2266580
9T2266581
9T2266582
9T2266583
9T2266584
9T2266585
9T2266586
9T2266587
9T2266588
9T2266589
9T2266590
9T2266591
9T2266592
9T2266593
9T2266594
9T2266595
9T2266596
9T2266597
9T2266598
9T2266599
9T2266600
9T2266601
9T2266602
9T2266603
9T2266604
9T2266605
9T2266606
9T2266607
9T2266608
9T2266609
9T2266610
9T2266611
9T2266612
9T2266613
9T2266614
9T2266615
9T2266616
9T2266617
9T2266618
9T2266619
9T2266620
9T2266621
9T2266622
9T2266623
9T2266624
9T2266625
9T2266626
9T2266627
9T2266628
9T2266629
9T2266630
9T2266631
9T2266632
9T2266633
9T2266634
9T2266635
9T2266636
9T2266637
9T2266638
9T2266639
9T2266640
9T2266641
9T2266642
9T2266643
9T2266644
9T2266645
9T2266646
9T2266647
9T2266648
9T2266649
9T2266650
9T2266651
9T2266652
9T2266653
9T2266654
9T2266655
9T2266656
9T2266657
9T2266658
9T2266659
9T2266660
9T2266661
9T2266662
9T2266663
9T2266664
9T2266665
9T2266666
9T2266667
9T2266668
9T2266669
9T2266670
9T2266671
9T2266672
9T2266673
9T2266674
9T2266675
9T2266676
9T2266677
9T2266678
9T2266679
9T2266680
9T2266681
9T2266682
9T2266683
9T2266684
9T2266685
9T2266686
9T2266687
9T2266688
9T2266689
9T2266690
9T2266691
9T2266692
9T2266693
9T2266694
9T2266695
9T2266696
9T2266697
9T2266698
9T2266699
9T2266700
9T2266701
9T2266702
9T2266703
9T2266704
9T2266705
9T2266706
9T2266707
9T2266708
9T2266709
9T2266710
9T2266711
9T2266712
9T2266713
9T2266714
9T2266715
9T2266716
9T2266717
9T2266718
9T2266719
9T2266720
9T2266721
9T2266722
9T2266723
9T2266724
9T2266725
9T2266726
9T2266727
9T2266728
9T2266729
9T2266730
9T2266731
9T2266732
9T2266733
9T2266734
9T2266735
9T2266736
9T2266737
9T2266738
9T2266739
9T2266740
9T2266741
9T2266742
9T2266743
9T2266744
9T2266745
9T2266746
9T2266747
9T2266748
9T2266749
9T2266750
9T2266751
9T2266752
9T2266753
9T2266754
9T2266755
9T2266756
9T2266757
9T2266758
9T2266759
9T2266760
9T2266761
9T2266762
9T2266763
9T2266764
9T2266765
9T2266766
9T2266767
9T2266768
9T2266769
9T2266770
9T2266771
9T2266772
9T2266773
9T2266774
9T2266775
9T2266776
9T2266777
9T2266778
9T2266779
9T2266780
9T2266781
9T2266782
9T2266783
9T2266784
9T2266785
9T2266786
9T2266787
9T2266788
9T2266789
9T2266790
9T2266791
9T2266792
9T2266793
9T2266794
9T2266795
9T2266796
9T2266797
9T2266798
9T2266799
9T2266800
9T2266801
9T2266802
9T2266803
9T2266804
9T2266805
9T2266806
9T2266807
9T2266808
9T2266809
9T2266810
9T2266811
9T2266812
9T2266813
9T2266814
9T2266815
9T2266816
9T2266817
9T2266818
9T2266819
9T2266820
9T2266821
9T2266822
9T2266823
9T2266824
9T2266825
9T2266826
9T2266827
9T2266828
9T2266829
9T2266830
9T2266831
9T2266832
9T2266833
9T2266834
9T2266835
9T2266836
9T2266837
9T2266838
9T2266839
9T2266840
9T2266841
9T2266842
9T2266843
9T2266844
9T2266845
9T2266846
9T2266847
9T2266848
9T2266849
9T2266850
9T2266851
9T2266852
9T2266853
9T2266854
9T2266855
9T2266856
9T2266857
9T2266858
9T2266859
9T2266860
9T2266861
9T2266862
9T2266863
9T2266864
9T2266865
9T2266866
9T2266867
9T2266868
9T2266869
9T2266870
9T2266871
9T2266872
9T2266873
9T2266874
9T2266875
9T2266876
9T2266877
9T2266878
9T2266879
9T2266880
9T2266881
9T2266882
9T2266883
9T2266884
9T2266885
9T2266886
9T2266887
9T2266888
9T2266889
9T2266890
9T2266891
9T2266892
9T2266893
9T2266894
9T2266895
9T2266896
9T2266897
9T2266898
9T2266899
9T2266900
9T2266901
9T2266902
9T2266903
9T2266904
9T2266905
9T2266906
9T2266907
9T2266908
9T2266909
9T2266910
9T2266911
9T2266912
9T2266913
9T2266914
9T2266915
9T2266916
9T2266917
9T2266918
9T2266919
9T2266920
9T2266921
9T2266922
9T2266923
9T2266924
9T2266925
9T2266926
9T2266927
9T2266928
9T2266929
9T2266930
9T2266931
9T2266932
9T2266933
9T2266934
9T2266935
9T2266936
9T2266937
9T2266938
9T2266939
9T2266940
9T2266941
9T2266942
9T2266943
9T2266944
9T2266945
9T2266946
9T2266947
9T2266948
9T2266949
9T2266950
9T2266951
9T2266952
9T2266953
9T2266954
9T2266955
9T2266956
9T2266957
9T2266958
9T2266959
9T2266960
9T2266961
9T2266962
9T2266963
9T2266964
9T2266965
9T2266966
9T2266967
9T2266968
9T2266969
9T2266970

On Dec 25, 1:19*pm, joel <joel.43r...@thecodecage.com> wrote:
> Try this code. *It should work. *fully tested
>
> Sub formatData()
>
> Set SourceSht = Sheets(1)
>
> NewRowCount = 2
> NewColCount = 3
> SheetCount = 1
> With SourceSht
> 'If data start i A1 then add new row
> If .Range("A1") <> "Inst. No." Then
> .Rows(1).Insert
>
> With Sheets(SheetCount)
> .Range("A1") = "Inst. No."
> .Range("B1") = "Memo"
> With .Cells.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 10
> End With
> End With
> End If
>
> .Range("C1") = "Inst. No."
> .Range("D1") = "Memo"
>
> RowCount = 2
> Do While .Range("A" & RowCount) <> ""
> If NewRowCount > 58 Then
> NewColCount = NewColCount + 2
> 'create new sheet
> If NewColCount > 8 Then
> If SheetCount = Sheets.Count Then
> 'add new sheet
> Sheets.Add after:=Sheets(Sheets.Count)
> End If
> SheetCount = SheetCount + 1
> With Sheets(SheetCount).Cells.Font
> .Name = "Arial"
> .FontStyle = "Regular"
> .Size = 10
> End With
> NewColCount = 1
> End If
>
> With Sheets(SheetCount)
> .Cells(1, NewColCount) = "Inst. No."
> .Cells(1, NewColCount + 1) = "Memo"
> End With
> NewRowCount = 2
> End If
>
> Sheets(SheetCount).Cells(NewRowCount, NewColCount) = _
> .Range("A" & RowCount)
>
> NewRowCount = NewRowCount + 1
> RowCount = RowCount + 1
> Loop
>
> 'Delete data from sheet 1
> LastRow = .Range("A" & Rows.Count).End(xlUp).Row
> If LastRow > 58 Then
> .Rows("59:" & LastRow).Delete
> End If
> End With
> End Sub
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=165111
>
> Microsoft Office Help


Hey joel,

This is a great solution, and I only found one small bug.

I'm just wondering also, is it possible at all to have the macro act
to use a single sheet for the resulting, formatted data?

- Currently, when it runs, it breaks out the same numbers in column B
as in column A - but only on the first sheet. After that, each column
is unique (on subsequent sheets).

- In terms of using a single sheet, is it possible to have the numbers
broken out on one sheet, rather than using a separate sheet for each
new page? There are a couple of reasons to this, one being that I
have another automated function that formats a sheet at a time. I've
ran it using some data and it produced 70+ sheets...

I think the existing code (the code I pasted, that is) had to modified
a couple of times to compensate for a similar issue I was having when
I first developed it.

Hope your day is a great one,

randy

On Dec 25, 3:24*pm, joel <joel.43r...@thecodecage.com> wrote:
> Here is the updates. *I added a page break after each page.
>
> Sub FormatData()
>
> Set SourceSht = Sheets(1)
>
> FirstPageRow = 2
> LastPageRow = 58
>
> NewRowCount = FirstPageRow
> NewColCount = 3
>
> With SourceSht
> With .Cells.Font
> Name = "Arial"
> FontStyle = "Regular"
> Size = 10
> End With
>
> 'If data start i A1 then add new row
> If .Range("A1") <> "Inst. No." Then
> Rows(1).Insert
>
> Range("A1") = "Inst. No."
> Range("B1") = "Memo"
> End If
>
> Range("C1") = "Inst. No."
> Range("D1") = "Memo"
>
> RowCount = 59
> Do While .Range("A" & RowCount) <> ""
> If NewRowCount > LastPageRow Then
> NewColCount = NewColCount + 2
> 'create new sheet
>
> If NewColCount > 8 Then
> 'add page break
> ActiveWindow.SelectedSheets.HPageBreaks.Add _
> Before:=.Range("A" & (LastPageRow + 1))
> NewColCount = 1
> FirstPageRow = FirstPageRow + 58
> LastPageRow = LastPageRow + 58
> End If
>
> Cells(FirstPageRow - 1, NewColCount) = "Inst. No."
> Cells(FirstPageRow - 1, NewColCount + 1) = "Memo"
>
> NewRowCount = FirstPageRow
> End If
>
> Cells(NewRowCount, NewColCount) = _
> Range("A" & RowCount)
>
> NewRowCount = NewRowCount + 1
> RowCount = RowCount + 1
> Loop
>
> LastRow = .Range("A" & Rows.Count).End(xlUp).Row
>
> If NewColCount = 1 Then
> Rows(NewRowCount & ":" & LastRow).Delete
> Else
> Rows((LastPageRow + 1) & ":" & LastRow).Delete
> End If
> End With
> End Sub
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=165111
>
> Microsoft Office Help


Hey joel,

Thanks for doing an amazing job here. Just two more small things..

I ran into this same issue last time when I developed the original
code, by the way... I couldn't seem to get data from column A from
being duplicated in column B. This time, we've managed to get it
mainly fixed, but there is still one cell that duplicates its data.

- The last entry on the first page of column A (cell A58, that is),
appears duplicated into cell C2. That only happens on the first page.

- The font and the font size doesn't seem to change to Arial 10. I'm
working with raw data in Calibri 11 font, when I activate the macro.

Any idea what we could possibly do on those two things? I've been at
it for a while trying some things, but no luck so far.

This is really excellent otherwise!


randy

On Dec 25, 10:15*pm, joel <joel.43s...@thecodecage.com> wrote:
> I just reran the code and not getting either problem. *Make sure you
> source data is correct. *Also I did leave the period out infront of the
> lines inside the "With Font" block in my first solution but did fix this
> problem in the 2nd solution. *The1sst piece of data that I put on the
> first Page Second Row is set with the statement : RowCount = 59. *It is
> impossible that the data in A58 gets moved to the 2nd column with my
> posted macro unless A58 and A59 are the same.
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: 229
> View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=165111
>
> Microsoft Office Help


Hey there,

Still getting exactly the same issues.

I'm using Excel 2007, default font set to Calibri 11.

The cell that is duplicated from the list below is the one containing
"XQ1676053".

I've even tried this on another system that I have here (also Excel
2007, default dont Calibri 11). Again, same thing.


XQ1675997
XQ1675998
XQ1675999
XQ1676000
XQ1676001
XQ1676002
XQ1676003
XQ1676004
XQ1676005
XQ1676006
XQ1676007
XQ1676008
XQ1676009
XQ1676010
XQ1676011
XQ1676012
XQ1676013
XQ1676014
XQ1676015
XQ1676016
XQ1676017
XQ1676018
XQ1676019
XQ1676020
XQ1676021
XQ1676022
XQ1676023
XQ1676024
XQ1676025
XQ1676026
XQ1676027
XQ1676028
XQ1676029
XQ1676030
XQ1676031
XQ1676032
XQ1676033
XQ1676034
XQ1676035
XQ1676036
XQ1676037
XQ1676038
XQ1676039
XQ1676040
XQ1676041
XQ1676042
XQ1676043
XQ1676044
XQ1676045
XQ1676046
XQ1676047
XQ1676048
XQ1676049
XQ1676050
XQ1676051
XQ1676052
XQ1676053
XQ1676054
XQ1676055
XQ1676056
XQ1676057
XQ1676058
XQ1676059
XQ1676060
XQ1676061
XQ1676062
XQ1676063
XQ1676064
XQ1676065
XQ1676066
XQ1676067
XQ1676068
XQ1676069
XQ1676070
XQ1676071
XQ1676072
XQ1676073
XQ1676074
XQ1676075
XQ1676076
XQ1676077
XQ1676078
XQ1676079
XQ1676080
XQ1676081
XQ1676082

What do you think could be the problem?


randy