PDA

View Full Version : insert six rows at specific points



joky
07-30-2017, 02:14 PM
Hi everyone
I have searched a lot, but with any luck .. I wish Success and good fortune be with you
I have posted the same request at this link but didn't work for me
http://www.vbaexpress.com/forum/showthread.php?60104-Transfering-data-from-one-main-sheet-to-multiple-worksheets-based-on-multiple-conditi&p=365061#post365061

I have data starting at row 8
The attachment is simply to transfer the data from the main sheet to two sheets ( Yes & no ) depending on the two condition in columns 31 and 32 and the code is working well ... and 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 C and in column E: "Signature" and in column H: "Signature"
in the third inserted row type : "Auditor" in column C and in column E: "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
- Note For the four footer rows that Between the tables, I need to insert rows without borders
- Important Note : the original data is about 10,000 rows so I am searching for faster way for this task


How can I modify my code to proceed with this?


I hope the idea is clear ... Please have a look at the example
Your help is greatly appreciated
Thanks advanced for help

p45cal
07-30-2017, 02:55 PM
What are the differences between this thread and the thread at http://www.vbaexpress.com/forum/showthread.php?60104-Transfering-data-from-one-main-sheet-to-multiple-worksheets-based-on-multiple-conditi&highlight=

If there are no differences, what was inadequate about the solution I gave in msg#13 (http://www.vbaexpress.com/forum/showthread.php?60104-Transfering-data-from-one-main-sheet-to-multiple-worksheets-based-on-multiple-conditi&p=365292&viewfull=1#post365292) (in the linked file therein)?

edit post posting: I see you've edited your question while I was responding. The question remains though.

joky
07-30-2017, 05:02 PM
Hello Mr. p45cal
Thanks a lot for your patience in this issue
In fact your solution is very very excellent and big step towards the ultimate aim
https://app.box.com/s/sz4ni8w1567877mdw0y3tyi78szv2j67
But not yet completed for the following reasons
- i treid running the macro but it came up with run-time error "438"
- the data starts from row 8
- the data is not transferred according to the two conditions, but the data is transferred according to one condition only
- when you execute the code A new worksheet is created
The data is required to be transferred directly to the two sheets ( Yes & No ) Without creating a new worksheet
- The last point is the addition a line to not copy and transfer data if one of the two conditions is not met or the two conditions are not met
So I need to deal with arrays
Please execute the code twice and note the result in creating new worksheets
Your help is greatly appreciated
Best and kind regards

mdmackillop
07-31-2017, 03:09 AM
I've looked at the data and results of p45cal's code. In your data, you have 26,751 of YES and 34,077 of NO 16,761 of YES & NO and 22,533 of Blank.
The macro returns 26,751 on the new YES sheet and 34,077 on the new NO sheet; Should it not do this?

With regard to the creation of additional sheets, I suggest you record a macro to delete the old YES & NO sheets and rename the new sheets accordingly. This can be run from the main code

I didn't encounter any errors.

p45cal
07-31-2017, 03:22 AM
But not yet completed for the following reasons
- i treid running the macro but it came up with run-time error "438"You will know that I tested the code thoroughly before presenting it.
1. Presumably you clicked the button on the sheet in the file I supplied to run the macro?
2. When the error occurs, if you click Debug, what line of code is highlighted in yellow?
3. What version of Excel are you using?




- the data starts from row 8
I'm not sure if you're talking about the source sheet or the destination sheets; if it's the source sheet then it doesn't matter.
If it's the destination sheets then I need to know: row 8 on every sheet or just on the first?





- the data is not transferred according to the two conditions, but the data is transferred according to one condition only
That may depend on how far the code gets before it encounters your error; it was designed to produce both sheets at once.






- when you execute the code A new worksheet is created
The data is required to be transferred directly to the two sheets ( Yes & No ) Without creating a new worksheet
With your very specific requirements, especially regarding page breaks (and the huge number of them), if you alter the Yes and No destination sheets, say by changing font sizes, the heights of some rows (even accidentally), it could mess up the page breaks, especially if those changes occur after the first sheet.
I chose to add new sheets because new sheets are a constant thing, and I don't need to erase old data from them.
Could I ask why the sheets need to be updated?
Would deleting them and creating new ones called Yes and No do?






- The last point is the addition a line to not copy and transfer data if one of the two conditions is not met or the two conditions are not met
This sounds like nonsense to me (why write a line to do nothing - it may as well not be written) but in case I'm not seeing something, can you confirm whether Option 1, Option 2 or Option 3 below, and if Option 3 could you tell me what should happen?
19946
Note that Option 2 will result in the Yes and No sheets containing exactly the same data as each other!





So I need to deal with arraysWe are dealing with arrays, pretty exclusively with arrays. Again, in case I'm missing something, what do you mean by arrays?





Please execute the code twice and note the result in creating new worksheetsI got 4 new sheets, as expected.

joky
07-31-2017, 10:34 AM
The problem is that the original file contains many interconnected papers,So we should not create a new worksheets
A specific question for not wasting time ... How can I modify my code to do this task?
Thanks in advance

p45cal
07-31-2017, 11:04 AM
The problem is that the original file contains many interconnected papers,So we should not create a new worksheets
A specific question for not wasting time ... How can I modify my code to do this task?
Thanks in advanceWith that sort of response, I think I'll stop wasting your time (and my time) and let someone else take over.

joky
07-31-2017, 01:23 PM
Thanks a lot for reply Mr. p45cal
I do not think I have any errors the response
I really appreciate you taking the time to help me on this.
I have abbreviated my request in just a few words so as not to waste your time ... that's what I mean
It's my great pleasure to know a person like you
My apologies to you and Thank you very very much
Best Regards

joky
07-31-2017, 11:07 PM
Your help is greatly appreciated

mdmackillop
08-01-2017, 03:25 AM
Don't add a sheet! Hardly rocket science.

If i > 1 Then MoreInMemory sn, i - 1, "Yes"
If j > 1 Then MoreInMemory sn2, j - 1, "No"

Set NewSht = Sheets(ShtNameSuffix)
With NewSht
.UsedRange.Delete