Formating PageBreaks based on sequential data (sort of...)

Wanna_Be_Guru

New Member
Joined
Jul 17, 2006
Messages
4
I am trying to set up a macro that will automatically take a range of data starting in A4 and running horizontally to K4 with headers of:
  • Account # __ Customer Name __ Address __ City __ State __ Zip __ Phone __ SalesID __ SalesManID __ Import Date __ Validation Code
and make a nice printable format for the data and take all of the codes in order and make PageBreaks after each code is complete.

The sequence I need to align the printable pages with is the Validation Code. (EXAMPLE - 1004GAS or 9095ELEC). The numbers are sequential... Well sort of. They are not EXACTLY in numerical order, but may skip integers like 1004 to 1021 then possibly to 5023 (you get my drift). I can easily make it so that I only get the raw integer by itself but I need to print the Validation Code as an indicator to what type of order it is and exactly how it will be displayed on the report at the end of the day.

I need some help getting this macro or even module to find the first line that contains the lowest validation code, copy that line (or lines if that validation code is repeated) and put it into another sheet (within the same workbook, preferably), and then go to the next highest validation code and do the same thing until it reaches the end of the data. This whole process will give me a pretty and tidy (ready to print) format for all of my orders.

So far, I am manually cutting and pasting all of this information into one large sheet that usually takes up about 1 MB of space and roughly 3 to 6 hours of my time (depending on the number of orders placed which varies from week to week). I know that there has to be a better way of doing this, so I am done fretting and now I am posting.

The way that my final layout look has this as the header row (repeated on every page):
  • Account # _ Customer Name _ Address _ City _ State _ Zip _ Phone _ SalesID _ SalesManID _ Import Date _ Validation Code _ Ref Code
Then the information to be printed:
  • 123456789000007 _ JOSE VITO _ 120 GRIN PL 1F _ ALBANY _ NY _ 12234 _ 5555555555 _ 800 _ 8502003 _ 8/25/2006 _ 2003GAS _ 2003
The final thought would be to put this whole mess into Microsoft Access and just run a query to give me that data back, however it's in Excel right now and I am very unfamilar with Access and can't tell you the data from the base (haha, I know... Just bear with me)

Desperately seeking sleep, :unsure:

Ty (Wanna_Be_Guru)
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
are the values completely sequential?
if they are you can just link them by putting some code like this in your second sheet A1:

=Sheet1!A1

and drag it down. It produces a 1on1 copy.

If they have to be ordered first, you put this line in the A1 and A2 of sheet2:

=RANK(Sheet1!A1;Sheet1!A:A;1)
=RANK(Sheet1!A2;Sheet1!A:A;2)

and drag it down. this gives an ordered list as a result, but sadly I couldn't figure how to convert the values to numbers only, so you have to insert your own formula there (I think I've been out of the running for too long :p).

You can then use VLOOKUP after to get the other data from the corresponding row.
I hope this helps a bit, I know it's not complete but maybe it's just the missing piece ;)
 

Wanna_Be_Guru

New Member
Joined
Jul 17, 2006
Messages
4
Code:
=RANK(Lead!L4,Lead!L4:L3181,1)

I tried that however the lead table that I use to find the value uses this type of function
Code:
=RIGHT(I4,4)
which is in Text. I tried changing the cells to Numbers, however it still didnt' give me a value (only #N/A)

Thanks for the advice, but alas, it didn't work....

Appreciatively,

Ty (Wanna_Be_Guru)
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Code:
=RIGHT(I4,4)
you can use:
Code:
=VALUE(RIGHT(I4,4))
instead to convert it to an integer, so you can then use other functions like rank.
 

Wanna_Be_Guru

New Member
Joined
Jul 17, 2006
Messages
4
Almost there...

I am so close! (THANK YOU HARVEY!) The Rank worked well, but I can't seem to get the VLOOKUP to work right. I guess it's all manual labor on this type of thing. I need the printable table to read the rank now and input the exact amount of lines that the rank returns, without having to input each one individually (or group of lines simply selecting them). But still, it's HOURS to get this information in the correct spots.

My friend suggested a tool in Pivot that will automatically put a PageBreak in, but I couldn't locate it. Besides, it appears that there's not way to group specific data without turning it into a number inside the PivotTable.

I guess I could design a way to get the program to read the data from one sheet and transfer it to the next table, but it seems like a Macro might work better.

Thanks again for all your help!
 

Forum statistics

Threads
1,137,060
Messages
5,679,387
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top