Rearrange ranges for a catalog

Hello everyone

My question is based on my previous thread (I can not post links but the name of the thread was 1330395-copy-and-paste-loop-depending-on-set-value) where jolivanes tried to help me but finally requested to open a new thread.
I have a catalog template with defined pages spanning over several cells/columns (I call them pages). This is fixed to 20 pages per catalog row.
Now I want to be able to adjust these 20 pages to whatever the end user wants. According to the users selected amount of pages, already existing catalog data should be moved to the appropriate page and the now not needed/empty pages should be deleted, or added if needed.
I will add a before and after sheet as an example. In the before sheet we have the original 20 pages setting, while in the after sheet I simulated a setting of 10 pages.
1. user sets page setting to 10
2. Pages after page 11 (if exist) get copied a row below (if data exists already on that row it has to be copied before)
3. Now empty pages (11 to 20) needs to be deleted

This should work in any direction (pages could be more than 20 for example) and the starting point is not always 20 pages.
jolivanes kindly provided the following code, but unfortunately I was not able to apply this to my template.
If anyone has any idea I would appreciate it.
Thanks in advance.

Please Login or Register  to view this content.

With the sheet to be re-arranged as the activesheet:

Please Login or Register  to view this content.

Well, the problem that I have is that I'm not sure how your sheet was formatted to show the ranges the way that you do - I typically don't care what things look like out to the far right or below what I have - but if you explain what you do, I can add that to the macro so that the presentation is similar. Also, I was not sure if you wanted 60 or 120 pages to be processed. That is an easy change - you just had different amounts on the two sheets.

The macro rearranges the active sheet onto a new sheet. I did not want to start with the same sheet because it was not clear if you would ever want to rearrange a sheet that had already been modified or had values entered into it. So the macro can work on any sheet.

I did not delete the source sheet - though that is easy enough to do - until you are happy with how the macro works, for testing purposes.

Originally Posted by Bernie Deitrick

Well, the problem that I have is that I'm not sure how your sheet was formatted to show the ranges the way that you do - I typically don't care what things look like out to the far right or below what I have - but if you explain what you do, I can add that to the macro so that the presentation is similar. Also, I was not sure if you wanted 60 or 120 pages to be processed. That is an easy change - you just had different amounts on the two sheets.

The macro rearranges the active sheet onto a new sheet. I did not want to start with the same sheet because it was not clear if you would ever want to rearrange a sheet that had already been modified or had values entered into it. So the macro can work on any sheet.

I did not delete the source sheet - though that is easy enough to do - until you are happy with how the macro works, for testing purposes.

Thank you for your answer.
The problem is not so much how the things look to the far right/below, more that these empty pages show up when I try to print them (and are just empty pages of course).
I did not create the original sheet and can therefore not exactly say how it was formatted unfortunately. If I adjust the empty pages by hand (and record the a macro for it), I get the following out of it:

Please Login or Register  to view this content.

With the first line I expanded the pages below, while on the second I reduced the pages from the right to the left. Don`t know if this is somewhat usable. In worst case I can adjust this by hand if needed.

The processed pages do not matter much as long the existing data fits.

It would be great if I could rearrange a sheet that is already modified.

OK - Replace all your code with this, and run "RearrangePages" to rearrange the activesheet. You will be asked if you want to optionally delete the original. If you don't delete the original, the rearranged sheet will be named "New Arrangment" and will be deleted if you don't rename it before running "RearrangePages" again.

When you want to print, run "PrintPages" to print the pages without any blank pages.

Please Login or Register  to view this content.

Thank you very much for your code. That is exactly what I wanted!
I found one strange behavior though. If for example (with my sample file) I set the pages across to 4 pages, than everything is fine. But if I set it to 3, the last row gets strangely cut off at the head.
The same happens if I set it to 1 or 2 pages across. I do not really need that few pages across, but it seems if there is more existing data (i.e. 60 pages) from the beginning, the phenomenon happens for example with 9 pages across too.
Other thing is that the iPages thing works with the example, but not with the "real" data sheet, which is strange because the sample file is just a copy of that real data file where I changed the text and replaced the pictures.
If there is another coverpage, then it also does not work (just copies something). Sorry that I did not mention that there might be some coverpages randomly between. I thought they would just work like any other range.
I will attach another sample with a coverpage between.

Try this one, which uses the same macro names but slightly different logic.

Originally Posted by Bernie Deitrick

Try this one, which uses the same macro names but slightly different logic.

Thank you, the coverpages work now. The phenomenon that the pages get cut off if I set them below 4 pages across still remains though. Anything I could do to fix that?

Edit: It seems the above behavior happens as soon as the rows exceed the currently available print pages.
i.e. In the Sample V2 file we have 6 rows at the start (page 1 to 120, rows 2 to 6 are empty). If I rearrange to 3 pages across, we now have 8 rows in total and after row 6 the pages get cut off.
It does not matter though if I change the print pages to only two rows by hand before and rearrange to 3 pages across. I need to be able to print single or only selected pages, therefore would it be nice to adjust the print area within the RearrangePages sub.

I don't know what you mean - If I set the width to 3, when I print the pages (actually, I only preview instead of print) everything is fine for all the pages as far as I can tell. Could you attach a screen capture that shows the issue that you are having?

To select the pages to print, replace "PrintPages" with this version of the macro:

Please Login or Register  to view this content.

Originally Posted by Bernie Deitrick

I don't know what you mean - If I set the width to 3, when I print the pages (actually, I only preview instead of print) everything is fine for all the pages as far as I can tell. Could you attach a screen capture that shows the issue that you are having?

Sure, I will attach some screen captures. In the first picture you can see that the print area is wrong for the last 3 rows.
Therefore in the print preview (and in the actual print too), the title get cut off. (i.e. for page 22)

Edit: Think I was able to find a fix to my problem by removing and resetting PageBreaks. Therefore I will call the following Sub at the end of the rearrange Sub.

Please Login or Register  to view this content.

You don't need to use page breaks, since the print macro prints page by page. Reset all page breaks on all sheets and both macros should work well.

Originally Posted by Bernie Deitrick

You don't need to use page breaks, since the print macro prints page by page. Reset all page breaks on all sheets and both macros should work well.

True. Thank you very much for your help!

May I ask one question though?
In the below code line, the criteria is "Code" but to what does this reference to? Is this some kind of hidden flag?

Please Login or Register  to view this content.

Edit: Ok, I am stupid. Found the answer by myself. And the reason it did work with the sample but not with the real file was that in the real file that part is in Japanese.