Copy and transfer data from a Main sheet to two sheets based on two conditions

Hi everyone
I have attached the following example " TEST" to make it more clear
It is excellent but very slow in dealing with large data so I need a faster solution using arrays If possible
I have data starting at row 8
I need to Copy and transfer data from a Main sheet to two sheets based on two conditions
For example
In Main sheet the column No. 31 the word "Yes" I want to copy columns from 1 : 5 with columns from 33 : 36
depending on the 31 column and transferred them to the " yes" sheet
and In Main sheet the column No. 32 the word "no" I want to copy columns from 1 : 5 with columns from 37 : 40
depending on the 32 column and transferred them to the " no" sheet
This has been achieved in the attached file "TEST2"
I need to insert six rows after each 30 names in two sheets (yes & no)
These rows will serve as totals and footer.
I have highlighted the points where I would like to insert the rows
if the remain items is less than 30 then to insert rows directly after them
During that process I would like to add some strings in these inserted lines
in the first inserted row To create the totals formulas and the formatting
in the second inserted row type : "Signature" in column B and in column D: "Signature" and in column H: "Signature"
in the third inserted row type : "Auditor" in column B and in column D: "Head of Accounts" and in column H: "General Manager"
in the six inserted row To create the previous totals and the formatting
With adjust the vertical and horizontal page breaks automatically
I hope the idea is clear and I hope a solution using arrays
The expected result in the two sheets result1 and result2
Please have a look at the examples
Your help is greatly appreciated
Thanks in advance

A faster alternative without using arrays:
- I have not used arrays but I expect that you may find that this solution is quicker than your current one
- You can test in the attached workbook with {CTRL} q
- I added lots of duplicate rows to Data Source to allow fuller testing
- headers not added in row 1
- total font has not been made bold
- no merged cells - avoid them when you want to use VBA - they cause too many problems
- TestNo duplicated from TestYes (I was too lazy to write a code to avoid the duplication )

How it works:
- DataFilter used on sheet source - filter for YES or NO
- copy all filtered rows together to other sheet (copy ALL columns)
- then delete columns not required
- insert the 6 rows
- add totals etc

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

@ Kev - I'm especially interested in the page breaks - have you already addressed them in your code?

@Xladept - my solution has not yet gone that far. Wanted to offer OP alternative to arrays ..

@Joky

Please consider these suggestions for your page layout
1 Do you need the empty rows at top of page 1
2 Signatures better laid out one under the other (see picture) - can double row height to provide more space
3 You do not need to use merged cells to get the look you want
- they are best avoided because they are very inflexible and are a nuisance if you want to alter things later


Layout.jpg

@xladept

@ Kev - I'm especially interested in the page breaks

Generic code for page breaks every "X" rows (- here set to 36 as per OP example)

Please Login or Register  to view this content.

Hi Kev,

Thanks for that, I tried something similar but it didn't work on my 2010 - do you have code for the vertical break??

@xladept

This works with both 2010 and 2016

Please Login or Register  to view this content.

This is what I had to do before it worked:

Please Login or Register  to view this content.

And, I had to be in page break view to get the HBreaks

That was your second "bump" posted within a few hours of your own previous post
bump 1 = 14 hours (post#02)
bump 2 = 10.5 hours (post#14)

bump2 puts you at the bottom of the queue

Thanks a lot for reply Mr. kev
The problem is that the original file contains many interconnected papers so it took a lot of time until I finished it
Anyway You should be honest with you... your solution is very very excellent
I need to adjust the points referred in your code only
Thanks advanced for help
Regards

I will try to get back to your thread later today

What does this mean?

Add a line to not copy and transfer data if one of the two conditions is not met or the two conditions are not met

Does it mean copy only if column 30 = YES AND column 31 = NO


31 = YES
32 = NO
COPY to sheet YES and sheet NO

31 = (empty)
32 = NO
DO NOT COPY

31 = YES
32 = (empty)
DO NOT COPY


31 = (empty)
32 = (empty)
DO NOT COPY

Is that not the same as....?

If 31 = YES copy to sheet YES

IF 32 = NO copy to sheet NO

Thanks a lot for reply Mr. kev
If cells the column number 31 and the column number 32 = empty do not copy
If any cell in column 31 = yes Copy to sheet YES
If any cell in column 31 = empty no Copy in sheet YES
If any cell in column 32 = no Copy to sheet NO
If any cell in column 32 = empty no Copy in sheet NO
Regards

Again I repeat. These are the only conditions required:
IF 31 = YES copy to sheet YES
IF 32 = NO copy to sheet NO
Everything else is irrelevant
If I am incorrect, you need to amend data filter conditions

- If you choose to amend anything you will need to do the same for both TestYes and TestNo
- Row 1 is the header row repeated at top of every sheet
- I was not sure what to do with first 5 rows so I ignored this and left top of sheet without blank rows - you can amend code to suit- yourself (- but remember Row 1 is the header row repeated at top of every sheet)
- Total rows row height now 30 and font is bold
- Borders put around around all values
- Borders removed in signature area - you may want to increase the height of these rows
- Page breaks inserted every 36 rows
- No merged cells introduced - create them yourself if you want them
- You need to amend the array containing Headers1 to 9 - simply type the headers in " " separated with a comma (both macros)

Attached is PDF showing results
Attached is workbook with working code
Run macro Test which runs TestYes and TestNo

I am away from my computer for 7 days - so I cannot help you until then

This is TestYes - TestNo is exactly the same except where references are to sheet "NO" and column 30

Please Login or Register  to view this content.

Headers at top of page

If you want row 6 to be the headers and rows 1 to 5 to contain other information printed on every page then (for both macros):
1 delete this line

Please Login or Register  to view this content.

2 add code for what you need in rows 1 to 5

3 change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

4 change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

The code to print automatically

Please Login or Register  to view this content.

Thanks a lot for reply Mr. kev
I have tested this great code ... That's really wonderful solution
In fact I've learned a lot from you and will do my best to study this great code
The last point is now for me
During data copy, the data copy range remains selected
Other than that I can work quietly
To clarify .... Please have a look at the examples
Regards

During data copy, the data copy range remains selected

add this line after the paste:

Please Login or Register  to view this content.

Thanks a lot Mr. kev for your patience and for great help all the time
Thank you very very much for your great support in this issue
I am just a learner and I have learned a lot and a lot from you
The words are not enough to say thank you for you Mr. kev
I also thank Mr. xladept for his great efforts from which I learned a lot
Best Regards

Mr. kev
Thank you very very much for this great help

Could do this at end of macro

yes.Activate
Range("A1").Select

Just leaves cell A1 selected
Same for other sheets

This look like a new requirement

It would be better to start a new thread with a new title "Excel database of Hotel Issues"
Attach a sample file showing what is typed into main sheet and what you would like to see in corresponding room sheet