Move Data, from one sheet to another based on one matching data entry

[Christopherdj]

Hi all
Two sample files uploaded, this request is for file ExcelForum Test: I’m really hoping someone can solve a rather complex issue for me. My main problem is that when a code is manually populated into cell A3 (highlighted yellow SPprintable sheet), I need all related codes from the RAW DATA sheet located in column F (that match the code manually entered and populate into and starting from A73, B73 and F73 respectively (all coloured Orange) from the Raw Data sheet into the SPprintable sheet. The manual code entered into A3 of the SPprintable sheet also appears in column B of the Raw Data Sheet which identifies which ones from the Raw Data in columns “F, G and I” respectively to be used commencing at row 73 of the SPprintable sheet. When the process completes the data from one sheet to the other, it would be good if we had 4 additional “Blank” columns at the end just in case we need to add some more manually.
In doing the above, it creates another issue by potentially creating extra sheets for any longer data sets. This process, I expect can create just one page of data or it could create 3 or 4. Note: I have a header and footer inserted that must remain in the sheets.
So the added complexity from my perspective is that I need the data table (which is to include the content populated into it) located in the SPprintable sheet from Cells “A63 to M68 inclusive” to be replicated at the top for any NEW page created after Page 3 BUT – NOT on the last page which currently starts from row 147. I have added manual page breaks where required and necessary apart from those the above process creates. It would also be great if it would create a "new sheet" named as per the title/code in A3 or even better, create a new file (new file preferred - to "save as"). This is a nice to have but not an absolute need to.

I think this is a big ask, I hope someone is up for the challenge. thanks in advance, kindest regards Chris.

[Christopherdj]

If someone is working on this, then thanks.
I think I will have to simplify it then try to merge the responses.

Can I have someone just do the following based on the ExcelForum Test file:
Matching the data which is manually populated into cell A3 (highlighted yellow SPprintable sheet), I need all related data from the RAW DATA sheet located in column F (that match that data A3 SPprintable sheet entry) to populate into and starting from A73, B73 and F73 respectively (all coloured Orange) from the Raw Data sheet into the SPprintable sheet.
The manual data entry at A3 of the SPprintable sheet also appears in column B of the Raw Data Sheet which identifies which ones from the Raw Data in columns “F, G and I” respectively to be used commencing at row 73 of the SPprintable sheet.
End of my first problem/issue.

[jindon]

Please Login or Register  to view this content.

[Christopherdj]

Thanks Jindon, your solution works a treat.

I think I'll go with what you've provided and do the rest manually.
Thank you so much, I was getting worried no one could help.
IF anyone has a solution to the first request, please let me know and I'll certainly test it.

thanks again - I'm forever indebted. Great Work.

[jindon]

Your explanation is too long to me...

Can you just upload the result workbook from your attached workbook?

The "Merge" is hard to understand.

[jindon]

Here's my guess...
Next response from me is late as I need to go out now.

Please Login or Register  to view this content.

[Christopherdj]

Please read this through and let me know if it is possible or too big an ask.
I understand if it’s too much for you. If you can do something like it, please let me know you’re working on it. Thanks.

Yes I think you've got the concept, but after a long walk and thinking about my final outcome I think I may have made it too complicated. So, if we don’t consider merging sheets as per the last message from you, and consider the following, then merging may not be required. I sincerely apologise for this.

The desired outcome is in "XYZ12345" sheet which is based on what is entered at $A$3 "Master” sheet. Except for the blank columns at rows 204 to 219 inclusive, they could be removed/deleted – see flexibility option at end of this.

The "FORCED PAGE BREAKS" in the "Master” sheet and copied to “XYZ12345” must remain and not moved from the rows applied.

My thoughts are to run the macro you created, populating the “Master” Sheet from the “Raw Data” sheet. The previous code you provided works brilliantly extracting the correct data and is exactly what I want.

NOTE: I have not added or updated the very first macro you supplied, not sure if it’s permitted or recommended for file uploads so I didn’t just to be safe. It still looks at $A$3 “Master sheet”, obtaining data from “Raw Data” sheet.

I am suggesting a new sheet be created based on the data as populated in $A$3 “Master sheet”, in this case it is XYZ12345.

In the "XYZ12345" sheet I created you can see what would be the ideal outcome.
After the code is run and it populates, you can see we can't foretell where a page break will occur when the code imports the data into the "Master" sheet, then creates the new “XYZ12345” sheet.
The raw data can contain up to at guess 100 lines or as little as 8. The data populating the cells from $B73 down (Full Title becomes wrapped text) and can force cell height, so there is no consistent line for a system forced page break. A Header is not an option as one is already used for other data.
So - my problem is created after the "XYZ12345" sheet is created:
When the new "XYZ12345" sheet is created or copied it must keep my "Forced Page Breaks".
Is there a way to automate/force a page break just short of the system generated page break. And if so, also copy a range of cells (it is the Grey shaded area Cells $A$63 to $M$68) and insert that copied range just after the FORCED Page Break. It would appear at the top (start) of any new page created in the “XYZ12345” after Page 4. So for any other pages i.e. Page 5, 6, 7 and so on within the range of $A$70 to $M$186 this would occur, (similar as the copy and insert copied cells function).
If no system breaks occur, then it is not required.

There is some flexibility, if Column A is empty after the first macro is run and within the range of $A$73 to $M$179, then the blank rows can be deleted/removed.

When I create the file, I would like it to save (prompt) with the created sheet name, in this case XYX12345.

I really don't know the answer to this issue apart from doing it manually.

From my perspective, I think this is a big ask of anyone to undertake, so I really thank you for your time and efforts. Kindest regards, Chris

[jindon]

Don't understand the logic.
How do you determine the number of rows to copy from raw data to each sheet?

[Christopherdj]

It is ok, I have managed to play with your original code at the top of this and also researched the rest.
I don't believe it is possible to remove blank rows from an empty array between the specified range, so I'm doing it manually, removing unwanted rows after the copy process has run. It would need to look at the first row from $A73 continuing down until an empty row is found stopping at, but including $A179 (leaving $A180 in the sheet).
If this is possible, I can add it to what code I have already.
As I've stated before, it seems easier to break this up into little sections, then I add the code where it's required.

thanks for your time - you did start me on a very interesting journey which has been really enlightening.
Thank you so much for your code and time spent on it. Just let me know if it can or can't be done. If it can't, then I'll make this issue solved.
kindest regards, Chris

[jindon]

OK, if you find a way for yourself then no problem.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.