VB to copy rows under different headers from multiple sheets to a master sheet.

[aryansaran2008]

hi,
I need help in copying data from multiple sheets into one master sheet but the problem is that the data in one sheet is further divided into 4 sections.

Details:
I have a workbook having 3 sheets
1) May Forecast-Rel Team
2)May Forecasr-RR team
3) May forecast Build team

Each of these sheets has 4 sections and each section has employees under it.

2015 022838 Forecast with I&O Costs
2015 External Forecast
2015 ADMIN Forecast
2015 022838 Forecast without I&O


There is a 4th master sheet "May Forecast" that consolidates data from these 3 sheets.
This has again same 4 sections as above.

I want to write a macro that copies the employee data under each header and copy to master sheet under the same header.

Note: A new employee can be added and deleted and the final sheet should reflect that too.
Need to copy the format too!

Please find the sheet in the attachment.
inputs: sheets 2,3,4
Expected output: sheet1 named "May Forecast " sheet!

[Doc.AElstein]

. Hi ( again!!! ) aryansaran
. Welcome to this board as well!!!
. I had already struggled with and answered your shortened with missing info version of this post here
http://www.mrexcel.com/forum/excel-q...ter-sheet.html
at MrExcel before I stumbled on this unanswered Thread here. As I had already done part of the code necessary for this complete version of your requirement I decided to continue. As this is your first time here and at mrexcel, you can maybe be forgiven for duplicate posting. But there are rules about it. ( I expect a Moderator will come in and tell you about them. ) . This is a classic example of why you should take note of those rules. I have wasted a considerable amount of time and made the code unnecessarily detailed due to the differing info you have given in the two Threads.


. And Again I think you have not quite got your example output right!!! – For example the name Lewis, David T. seems to be missing everywhere in you example output!!! . I expect my final output, a small part of which I present here is correct.
. Please advise.

. Small part of my output obtained with your given input data. ( In the actual sheet All Formatting is preserved: )

Using Excel 2007

-	A	B	C
1	2015 022838 Forecast with I&O Costs
2	Resource	Resource Type	Rate
3	Barker, Amber	A-Tech	$64.00
4	Agarwal, Shashank	C-On	$83.50
5	Bommidi, Venkata	C-On	$89.50
6	Cai, Xiaohua	A-Tech	$64.00
7	Buckendahl, Theresa A	A-Manager	$83.75
8	Lewis, David T.	A-Tech	$64.00
9	2015 External Forecast
10	Resource	Rate	Resource Type
11	Barker, Amber	A-Tech	$64.00
12	Agarwal, Shashank	C-On	$83.50
13	Bommidi, Venkata	C-On	$89.50
14	Cai, Xiaohua	A-Tech	$64.00
15	Buckendahl, Theresa A	A-Manager	$83.75
16	Lewis, David T.	A-Tech	$64.00

May Forecast

Here is my final Code version which comes closer , if not exactly meets, your actual requirements…:

Please Login or Register  to view this content.

Please let me know ( Preferably in both Threads how you get on )


Alan Elston

[Karise]

Kopi af Bunzl Pipeline 11 juni 2015.xlsm

Hello Alan , im not sure if its right to add me in here but i found your code fantastic and just what i have bene looking for very long time .
I have same situation as requester . I have 6 sheets with one sales person (in future could bee more) I also want to do a summery sheet (Total).
Can you help med make this coding ?? Attached is my working file

[Doc.AElstein]

Hi Karise,

…not sure if its right to add me in ….

.. For a follow up / similar requirement seems appropriate to tack on here.. But: You have been at ExcelForum a bit longer than me, you probably know they prefer you to start a new Thread and reference this one at the start. Otherwise it is sometimes described as “Hijacking a Thread”
……………………………………………………..

. Anyways: To your request…
. 1) very important as always is that you include an AFTER sheet ( your “Total” sheet ) showing exactly how your final output should look based on the sample data you gave in the other sheets. ( You left this sheet empty!! )
.
. 2) As with the code I did for aryansaran, there is no great technical challenge here, rather a case of just carefully following through the codes of mine, and applying them to your particular case. A bit time consuming!!

( In fact the other codes and formulas in the file you attached are more professional than mine – you may be a lot further in understanding VBA than me. – My codes in this Thread are based on very primitive Looping!! )

. 3) As a very crude start point, I have done the following for you. I strongly suspect it does not do the full job, but I am just “guessing” the form of the output you may need.

. 3a) For the following code to work, I have an extra sheet in your file, looking like this: ( In the actual sheet all formatting is preserved as you gave it )

Using Excel 2007

-	C	D	E	F	G	H	I	J	K	L
1
2
3	PIPELINE	PIPELINE							TIDSFRIST	30
4
5	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato	**** dato	Bemærkning	Status	Days
6									Ongoing
8
9	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato	**** dato	Bemærkning	Status	Days
10

Total (2)

……………………….

. 3 b) After running the code your output in sheet “Total” will look like this

Using Excel 2007

-	C	D	E	F	G	H	I	J	K	L
5	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato	**** dato	Bemærkning	Status	Days
6	Rene	Vestas	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
7	Ole	Vestas	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
8	Lars	Vestas	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
9	Jens	Vestas	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
10	Dennis	Vestas	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
11	Brian	customer 1	150000	250000	90	13.05.2015	01.06.2015	Test 2	Ongoing	19
12									Ongoing

Total

……………………………………….

. The code produces these results I give in the next Post #5, because of the Post size limitations….
. Let me know here how you wish to proceed further. And consider in parallel starting a new Thread giving in particular a good “AFTER” Picture. IMPORTANT: - If you do start a new Thread, then reference the link to this Thread in the new Thread, and reference the link to that new Thread in This Thread. Otherwise I may not catch your reply and further request.

. Alan

[Doc.AElstein]

Code for Post #4:

Please Login or Register  to view this content.

[Doc.AElstein]

Hi,
. Here is a copy of part of a PM which Karise sent me and my reply, which relates to further info in this Thread. So I am reposting here to keep the Thread "up to date"

........

Re http://www.excelforum.com/excel-prog...ml#post4098792


Hi Karise,
. I will try to post a very basic starter code for you some time later today, and then we can take it from there.
. In the meantime you might like to think about exactly how you want your summery sheet (Total) to look like, based on your sample data that you gave..

Alan

Hello Alan

I tried your code and cat get it to Work. On my allready attached file the sheet "Total" is filled with the design I want ( same as in each salesman ).
The two samples you provided, the irst sample was correct im notr looking for summary sheet as sample 2.
I want to have records for first section under each salesmen listed in first section on Total sheet and offcourse 2 section under each salesmen listed under 2 section in total sheet.
Guess this was also what you did in your first sample you presented , i just cant get it to Work.

I would also like to have the Total sheet and each section sorted by salesman ID and listed with "**** dato" as descending

Thanks in advance for all your Work its really appriciated .

Rgd Peter

.......................

…
Hi Peter,
. I am really struggling to understand what you want? In your supplied “Total” sheet and my first sample screen shot in Post #4 there is no summary info whatsoever. No Code has done anything. There is nothing to be done. You have Just Headings. If this is what you want, then you already have it. I am sure you want something else?? But I have no idea what. I am really confused.!!!
. Please read again what I posted in Post #4, especially the last bit, about replying to me in that Thread or by starting a new one. Please feel free to contact me here with short messages, but further details to progress to the Thread / request should always be kept in the Thread. That way everyone can follow and maybe someone else can also help.

….my last note in that Thread:..

…,……….
. Let me know here how you wish to proceed further. And consider in parallel starting a new Thread giving in particular a good “AFTER” Picture. IMPORTANT: - If you do start a new Thread, then reference the link to this Thread in the new Thread, and reference the link to that new Thread in This Thread. Otherwise I may not catch your reply and further request…….

. Again please try to give more precise info in that Thread , or a new one, of exactly what you want. – Your “AFTER”
. The sample output you give ( Your “Total” sheet ) should look exactly as it should based on all the info in the other sheets. In other words, you write in manually in this “AFTER” exactly what you want the code to do.
. And if any code I, or others give, “does not work”, you must give more details. Simply saying “It don’t work” is…..

[Karise]

Sorry im not very good in explaining.
Your 3a) picture is correct, I want to keep this format in Total , then I want each record from each salesman sheet listed there under both section

[Doc.AElstein]

Hi Peter,

.

Sorry im not very good in explaining…..

. Correct but you are very good at confusing: I got this message in my email notification from ExcelForum-

-- Betreff: Reply to thread 'VB to copy rows under different headers from multiple sheets to a master sheet.'
Datum: Fri, 12 Jun 2015 16:18:52 +0200
Von: Excel Help Forum <[email protected]>
……..
Karise has just replied to a thread you have subscribed to entitled - VB to copy rows under different headers from multiple sheets to a master sheet. - in the Excel Programming / VBA / Macros forum of Excel Help Forum.
……………………………..
Here is the message that has just been posted:
***************
Hi Again , ok i try in visual way to explain what i want :

On below file you can see information i added in row C6 and C10 , this information I want a macro to copy and insert in sheet "Total" in row C6 and 10
Then offcourse when i add new record in sheet "Brian" the macro has to update sheet "total" listing the new records below present records.
Think its me best way to explain…

… I do not however see your message in this Thread.!!! So you deserve “praise” at least for how you managed to fool the ExcelForum Software into doing that!!! ( Or maybe failings in the Edit Post Function at Excel Forum helped you in achieving that by wiping out an original Thread when you attempted to edit it…..TTT ??? etc. etc... )))
………………………………………………………………………………………………….

Anyways… I think I may be getting closer to understand what you want.....


. BTW ( By The Way ) Images are not too good to post for showing spreadsheet content as they cannot be copied easily to into a spreadsheet.
. The Forum Tools referenced in my signature below are not perfect either as they do not show the Format which is actually preserved by all the codes I have given you. Screenshot produced with this tool can however be copied to the clipboard and then pasted into a File. Alternatively of course , you have the option here of uploading a file in which to place an AFTER sheet showing EXACTLY what you want based on all your sample data.
. Here is a screenshot showing a small part of the sheet produced for your “Total” from my latest code which I give in post #9. Maybe this is getting closer to what you want.
.

Using Excel 2007

-	C	D	E	F	G	H
1
2
3	PIPELINE	PIPELINE
4
5	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
6	Brian	customer 1	150000	250000	90	13.05.2015
7			150,000	250,000
8
9	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
10	Brian	customer 2	150000	250000	60	12.05.2015
11
12	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
13	Dennis	Vestas	150000	250000	90	13.05.2015
14			150,000	250,000
15
16	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
17	Dennis	Vestas	150000	250000	90	13.05.2015
18
19	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
20	Jens	Vestas	150000	250000	90	13.05.2015
21			150,000	250,000
22
23	ID	Kundenavn	Oms 2015	Oms 2016	Succes %	Start dato
24	Jens	Vestas	150000	250000	90	13.05.2015

Total

. Note: The code I give you in the next post is extremely simple, and I am a bit surprised that that is all you want. This requirement is somewhat different to that at the outset of this Thread, and much simpler. You would have obtained a much quicker response and saved me unnecessary work if you had simply given at the outset a clear AFTER sheet showing exactly what you required the code to do for you based on all your sample data. But it is all good fun

Alan

[Doc.AElstein]

Code for Post #8

Please Login or Register  to view this content.

[Karise]

Hello Alan

This is a true beauty , this was excatly what i wanted . Im sorry to keept you busy but sometimes its hard for a newbie to explain in simple Words
Appriciate all your help , thank you very much.

[Doc.AElstein]

......, this was excatly what i wanted . Im sorry to keept you busy but sometimes its hard for a newbie to explain in simple Words
Appriciate all your help , thank you very much.

. Yous welcome
. Glad we got there in the end

Alan