Macro to insert # of row specific in a cell

[Baboo]

Hi All,

hope someone can help to achieve the below.
I am working on an excel file as attached file, tiger.xls
this is what i am trying to accomplish to write in Marco.

Part A:
in the H column, i need to insert the # of row minus the number that is in the cell. for ex. when
H2 = 3, to insert 2 blank row down
H3 = 2, to insert 1 blank row down

however, if the cell is 1, then do not add any row

Inserting the blank row should come to a STOP whenit reaches [Total] on colunm A.
when this is done, the file shouuld like file as attached: lion.xls

Part B:
Auto populate the number in an increasement format in the empty cells follow by the Max# in Column A.

in this example, in Colunm A the max# is 10. so
the first empty cell [A3]l should be listed as 11.
The increasment should be carried on, unitl it reaches a smaller number.
in this case:

A3 = 11
A4 = 12
A5 = 2

stop, and then

A6 = continue to auto populate [increasement] from last Max #
which means, A6 = 13

auto populate number should be stopped when it reaches to the cell [Total]

the final file should look like as attached, bear.xls

Appreciate for any thoughts and comment.

Regards

[TMS]

For the first part:

Please Login or Register  to view this content.

Regards

[Marcol]

Try this with your Sheet "Tiger"

Please Login or Register  to view this content.

[Baboo]

TMShucks and Marcol, it works perfectly! you guys are awesome! thanks.

[Marcol]

Happy to help.

[TMS]

You're welcome.

[Baboo]

You're welcome.

I am greedy... and want to to build the Marco from scratch with the oringal file: see file as: Monkey.xls

Part 1:
Re-listing from A2 in order (1,2,3) - stop when it reaches [Total]

Part 2:
Insert 3 column after [G column]
the new columns need to be named as follow:

new [H column] "Carton (Qty.)"
new [I column] "Case Number"
new [J column] "Carton Label #"

Next, action the code that written by Marcol (addrow and number new row]

After this, I need to add one more Marco:

in Column J, from:
J2 = listing as: Carton 1 of (Max#) in column A
J3 - listing as: Carton 2 of (Max#) in column A

Continue and stop when the [Total] is reached in column A

the final file should look like as attached: wolf.xls

[NBVC]

Please stop editing your title... this is the 3rd time that I fixed it... just add your new post to the thread and somebody will look at it.

Thank you!

[Baboo]

Please stop editing your title... this is the 3rd time that I fixed it... just add your new post to the thread and somebody will look at it.

Thank you!

new user here....sorry for the inconvenience it caused to you and thanks for the fixed.

[Marcol]

Try this with your Sheet "Tiger"

Please Login or Register  to view this content.

Hope this helps.

[Baboo]

Try this with your Sheet "Tiger"

Please Login or Register  to view this content.

Hope this helps.

Marco:

the add J column for Carton 1 of 35 is wokring.
however, part 1 and Part 2 where i needed in the Monkey file is not working.

appreciated if you can re-look the monkey file
also, is there any way not to rename the sheet2 to" tiger"

thanks for your help!

[Marcol]

Post a sensible workbook.
Forget the Lion, the Tiger, and the Turkey Buzzard.
Put your problem in one workbook, simple names like "Sheet1", "Sheet2", etc will suffice.

A simple before and after example is usually enough.

[TMS]

@Marcol: Turkey Buzzard? Missed that one ;-)

[Marcol]

@ TMShucks
It's a workbook after "Aardvark" and before "Zebra", but it's on a different server.

[Baboo]

Post a sensible workbook.
Forget the Lion, the Tiger, and the Turkey Buzzard.
Put your problem in one workbook, simple names like "Sheet1", "Sheet2", etc will suffice.

A simple before and after example is usually enough.

Applogies Marcol, my DD has alot of stuff animals in the house. so thought it would be easy when recall. next time i will use flowers like, Lilly, Rose and Jasmine... LOL

okay, basically this is what i want:

- Relisting in [A column] in an increasement order from A2 (1,2,3) stop when it reaches [Total]. A2 was=1001, A3 was 1002

Next
- Insert 3 columns after [G column]
the new columns need to be named as follow:

new [H column] "Carton (Qty.)"
new [I column] "Case Number"
new [J column] "Carton Label #"

Next
- in [H column], =G2/12, =G3/12 - stop when it reaches the last # in [G colunm]

at last, i will execute the code that written by Marcol on the above.
in case, you want to see the sample file i am attaching [before] and [after] file.
thanks for all the help!

[Baboo]

@ Marcol
Can u help ??

[Marcol]

This is not how I would normally code this but as I can't fully understand what you finally want as a result. I have broken the code into several subs so that you can easily comment them out in the macro "Convert Sheet", or edit the individual subs.

The numbering you have asked for in "Line" seems to me, to be at odds with what you ask for in "Carton Label#".

Please Login or Register  to view this content.

"Sheet1" is a copy of your before sheet ("monkey"?), the code will convert "Sheet1"
To convert one of the sheets from your DDs' stuffed animals colection change ths line in the sub "ConvertSheet"

Please Login or Register  to view this content.

e.g.

Please Login or Register  to view this content.

Hope this helps

[EDIT]
If the line numbers are to be 1001, 1002, etc
Change these lines in Sub RenumberLines

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

[Baboo]

This is not how I would normally code this but as I can't fully understand what you finally want as a result. I have broken the code into several subs so that you can easily comment them out in the macro "Convert Sheet", or edit the individual subs.

The numbering you have asked for in "Line" seems to me, to be at odds with what you ask for in "Carton Label#".

Please Login or Register  to view this content.

"Sheet1" is a copy of your before sheet ("monkey"?), the code will convert "Sheet1"
To convert one of the sheets from your DDs' stuffed animals colection change ths line in the sub "ConvertSheet"

Please Login or Register  to view this content.

e.g.

Please Login or Register  to view this content.

Hope this helps

[EDIT]
If the line numbers are to be 1001, 1002, etc
Change these lines in Sub RenumberLines

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

@Marcol,
i wanted to take the time and thank you! the code works perfectly! you are amazing!!
thanks again for taking your time to help me! I'm truly grateful!