| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking out rows of data, sequentially, into headered columns
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
On Dec 25, 1:19*pm, joel 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
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 _ Befo=.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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
On Dec 25, 3:24*pm, joel 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 _ Befo=.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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
On Dec 25, 10:15*pm, joel 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Breaking out rows of data, sequentially, into headered columns
I'm tired and going to sleep. Yo can upload the file at http://tinyurl.com/yc58bw5 When you reply to a message there is a button labeled Manage Attqhments. More then one websites share postings. theCodeCage allows attachments. I think the problem may be where the data is starting and any headers you may have in the column. I'm tooo tired to look at the problem tonight. Mke sure you don't have any data in column c when you run the macro. Also delete any header row that may exits. Start the 1st data you posted in cell A1. the code is using the first tab in the workbook Set SourceSht = Sheets(1) This my not be Sheet1 You may want to change to Set SourceSht = Sheets("Sheet1") -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165111 Microsoft Office Help |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| breaking apart columns in pivot table | Excel Discussion (Misc queries) | |||
| Breaking a row into multiple rows | Excel Programming | |||
| Breaking the data of column A into two columns First Name & Last name. | Excel Programming | |||
| Adding data sequentially | Excel Discussion (Misc queries) | |||
| Breaking up a string into separate columns | Excel Worksheet Functions | |||
Linear Mode
