Copy and paste from multiple worksheets to another (combine results into a table)

Bamerand · Oct 26, 2013

[FONT=&quot]Dear all,[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I need some help in writing a code for the following:[/FONT]
[FONT=&quot]I have a workbook with 11 worksheets in it. Each worksheet has a unique name (such as 167L, 167S, 170S, 170L, 173S, 173L, 175A, 183S, 183L and etc.). Each of the worksheet is updated daily and I need to extract data from specific columns and according to specific time.[/FONT]
[FONT=&quot] [/FONT]

*[FONT=&quot]A[/FONT]*	*[FONT=&quot]B[/FONT]*	*[FONT=&quot]C[/FONT]*	*[FONT=&quot]D[/FONT]*	*[FONT=&quot]E[/FONT]*	*[FONT=&quot]F[/FONT]*	*[FONT=&quot]G[/FONT]*	*[FONT=&quot]H[/FONT]*	*[FONT=&quot]I[/FONT]*
[FONT=&quot]173L[/FONT]
[FONT=&quot]DATE[/FONT]	[FONT=&quot]TIME[/FONT]	[FONT=&quot]W[/FONT]	[FONT=&quot]DS[/FONT]	[FONT=&quot]WH[/FONT]	[FONT=&quot]S[/FONT]	[FONT=&quot]WC[/FONT]	[FONT=&quot]E[/FONT]	[FONT=&quot]C[/FONT]
[FONT=&quot]bars[/FONT]	[FONT=&quot]bars[/FONT]	[FONT=&quot]C⁰[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]0/64"[/FONT]
[FONT=&quot]27.10.13[/FONT]	[FONT=&quot]00:00[/FONT]	[FONT=&quot]95[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]02:00[/FONT]	[FONT=&quot]91[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]2[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]89[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]06:00[/FONT]	[FONT=&quot]93[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]16[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]08:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]95[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]16[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]3[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]10:00[/FONT]	[FONT=&quot]90[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]16[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]12[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]12:00[/FONT]	[FONT=&quot]89[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]16[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]14:00[/FONT]	[FONT=&quot]92[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]5[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]16:00[/FONT]	[FONT=&quot]95[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]7[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]18:00[/FONT]	[FONT=&quot]92[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]8[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]20:00[/FONT]	[FONT=&quot]93[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]12[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]22:00[/FONT]	[FONT=&quot]90[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]6[/FONT]	[FONT=&quot]24/64"[/FONT]
[FONT=&quot]28.10.13[/FONT]	[FONT=&quot]00:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]98[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]24/64"[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]Basically, the code I have used before I was assisted by the www.mrexcel.com/forum before, however, I need to have it devised in a bit different way (pls visit the www….. for details). The code I need should (a) select values at column “B” (the corresponding values are 04:00, 08:00, 12:00, 16:00, 20:00 and 00:00), copy value in column “G” (0, 0, 0, 0, 0, 0) and sum up with values from column “H”, which before being added should be divided by a factor of “2” (0/2=0, 3/2=1.5, 16/2=8, 7/2=3.5, 12/2=6, 0/2=0). The summed values should be copied and pasted into a separate worksheet and it needs to look like this:[/FONT]
[FONT=&quot] [/FONT]

[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]08:00[/FONT]	[FONT=&quot]12:00[/FONT]	[FONT=&quot]16:00[/FONT]	[FONT=&quot]20:00[/FONT]	[FONT=&quot]00:00[/FONT]
[FONT=&quot]173L[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1.5[/FONT]	[FONT=&quot]8[/FONT]	[FONT=&quot]3.5[/FONT]	[FONT=&quot]6[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]

[FONT=&quot]The things get a bit complicated as different worksheet in a workbook have slightly different table compare with the one in above. [/FONT]

*[FONT=&quot]A[/FONT]*	*[FONT=&quot]B[/FONT]*	*[FONT=&quot]C[/FONT]*	*[FONT=&quot]D[/FONT]*	*[FONT=&quot]E[/FONT]*	*[FONT=&quot]F[/FONT]*	*[FONT=&quot]G[/FONT]*	*[FONT=&quot]H[/FONT]*
[FONT=&quot]183L[/FONT]
[FONT=&quot]DATE[/FONT]	[FONT=&quot]TIME[/FONT]	[FONT=&quot]W[/FONT]	[FONT=&quot]DS[/FONT]	[FONT=&quot]WH[/FONT]	[FONT=&quot]S[/FONT]	[FONT=&quot]WC[/FONT]	[FONT=&quot]C[/FONT]
[FONT=&quot]bars[/FONT]	[FONT=&quot]bars[/FONT]	[FONT=&quot]C⁰[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]0/64"[/FONT]
[FONT=&quot]27.10.13[/FONT]	[FONT=&quot]00:00[/FONT]	[FONT=&quot]30[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]3[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]02:00[/FONT]	[FONT=&quot]34[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]38[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]06:00[/FONT]	[FONT=&quot]38[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]2[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]08:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]37[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]10:00[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]12:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0.5[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]14:00[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]16:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]25[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]18:00[/FONT]	[FONT=&quot]21[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]4[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]20:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]33[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]2[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]22:00[/FONT]	[FONT=&quot]35[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1[/FONT]	[FONT=&quot]20/64"[/FONT]
[FONT=&quot]28.10.13[/FONT]	[FONT=&quot]00:00[/FONT][FONT=&quot][/FONT]	[FONT=&quot]35[/FONT]	[FONT=&quot]11.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0[/FONT][FONT=&quot][/FONT]	[FONT=&quot]20/64"[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]So, the final output from two worksheets should look like:[/FONT]
[FONT=&quot] [/FONT]

[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]08:00[/FONT]	[FONT=&quot]12:00[/FONT]	[FONT=&quot]16:00[/FONT]	[FONT=&quot]20:00[/FONT]	[FONT=&quot]00:00[/FONT]
[FONT=&quot]173L[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1.5[/FONT]	[FONT=&quot]8[/FONT]	[FONT=&quot]3.5[/FONT]	[FONT=&quot]6[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]183L[/FONT]	[FONT=&quot]1[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0.5[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]2[/FONT]	[FONT=&quot]1[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]And finally, to make things more complicated, the monitoring hours vary, like in the table below:[/FONT]
[FONT=&quot] [/FONT]

*[FONT=&quot]A[/FONT]*	*[FONT=&quot]B[/FONT]*	*[FONT=&quot]C[/FONT]*	*[FONT=&quot]D[/FONT]*	*[FONT=&quot]E[/FONT]*	*[FONT=&quot]F[/FONT]*	*[FONT=&quot]G[/FONT]*	*[FONT=&quot]H[/FONT]*	*[FONT=&quot]I[/FONT]*
[FONT=&quot]175A[/FONT][FONT=&quot][/FONT]
[FONT=&quot]DATE[/FONT]	[FONT=&quot]TIME[/FONT]	[FONT=&quot]W[/FONT]	[FONT=&quot]DS[/FONT]	[FONT=&quot]WH[/FONT]	[FONT=&quot]S[/FONT]	[FONT=&quot]WC[/FONT]	[FONT=&quot]E[/FONT]	[FONT=&quot]C[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]bar[/FONT]	[FONT=&quot]bar[/FONT]	[FONT=&quot]C^o[/FONT]	[FONT=&quot]22/64"[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]%[/FONT]	[FONT=&quot]%[/FONT]
[FONT=&quot]27.10.13[/FONT]	[FONT=&quot]00:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]00:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]32[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]01:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]37[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]01:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]02:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]39[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]02:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]03:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]03:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]33[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]35[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]04:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]38[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]05:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]05:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]37[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]06:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]33[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]06:30[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]20[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]07:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]27[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]08:00[/FONT]	[FONT=&quot]40.5[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]29[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]09:00[/FONT]	[FONT=&quot]40.5[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]35[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]10:00[/FONT]	[FONT=&quot]40.5[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]26[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]11:00[/FONT]	[FONT=&quot]40.5[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]29[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]12:00[/FONT]	[FONT=&quot]40.5[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]32[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]13:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]14:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]28[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]15:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]42[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]16:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]17:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]27[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]18:00[/FONT]	[FONT=&quot]41[/FONT]	[FONT=&quot]10[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]32[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]19:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]20:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]21:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]34[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]22:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]29[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot] [/FONT]	[FONT=&quot]23:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]33[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]
[FONT=&quot]28.10.13[/FONT]	[FONT=&quot]00:00[/FONT]	[FONT=&quot]40[/FONT]	[FONT=&quot]9.5[/FONT]	[FONT=&quot]18[/FONT]	[FONT=&quot]16/64"[/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot] [/FONT]	[FONT=&quot]trace[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]With this worksheet (175A), my final output should look like this:[/FONT]

[FONT=&quot] [/FONT]	[FONT=&quot]04:00[/FONT]	[FONT=&quot]08:00[/FONT]	[FONT=&quot]12:00[/FONT]	[FONT=&quot]16:00[/FONT]	[FONT=&quot]20:00[/FONT]	[FONT=&quot]00:00[/FONT]
[FONT=&quot]173L[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]1.5[/FONT]	[FONT=&quot]8[/FONT]	[FONT=&quot]3.5[/FONT]	[FONT=&quot]6[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]183L[/FONT]	[FONT=&quot]1[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]0.5[/FONT]	[FONT=&quot]0[/FONT]	[FONT=&quot]2[/FONT]	[FONT=&quot]1[/FONT]
[FONT=&quot]175A[/FONT]	[FONT=&quot]35[/FONT]	[FONT=&quot]29[/FONT]	[FONT=&quot]32[/FONT]	[FONT=&quot]36[/FONT]	[FONT=&quot]31[/FONT]	[FONT=&quot]31[/FONT]

[FONT=&quot] [/FONT]
[FONT=&quot]Since there are no value at column “H”, divided by a factor of “2” is skipped.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Here is a code I used before, and it executes part of the required job:[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Dim myrow As Long[/FONT]
[FONT=&quot]Dim wksTo As Worksheet[/FONT]
[FONT=&quot]Dim j, s As Long[/FONT]
[FONT=&quot]Dim lastRow, lastsheet As Long[/FONT]
[FONT=&quot]Dim wks As Worksheet[/FONT]
[FONT=&quot]Dim sn As String[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Sheets(1).Select[/FONT]
[FONT=&quot] Sheets.Add.Name = "wksTo"[/FONT]
[FONT=&quot] lastRow = 2 ' set the first row to use, set to 2 so you can add labels in row 1[/FONT]
[FONT=&quot] lastsheet = Sheets.Count[/FONT]
[FONT=&quot] For s = 2 To lastsheet ' Loop thru sheets[/FONT]
[FONT=&quot] Worksheets(s).Activate[/FONT]
[FONT=&quot] With Worksheets(s) ' sheet(s) changes sheets[/FONT]
[FONT=&quot] On Error Resume Next[/FONT]
[FONT=&quot] myrow = .Columns("A").Find(Date).Row[/FONT]
[FONT=&quot] Application.Goto .Range("A" & myrow), True[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] With Worksheets(s)[/FONT]
[FONT=&quot] ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] With Application.ActiveWindow.ActiveCell[/FONT]
[FONT=&quot] dc = 0[/FONT]
[FONT=&quot] sn = Sheets(s).Name[/FONT]
[FONT=&quot] Worksheets("wksTo").Range("G" & lastRow).Formula = sn[/FONT]
[FONT=&quot] For j = 1 To -10 Step -2[/FONT]
[FONT=&quot] Range(.Cells(j, 6), .Cells(j, 6)).Copy Sheets("wksTo").Cells(lastRow, 8 - dc)[/FONT]
[FONT=&quot] dc = dc + 1[/FONT]
[FONT=&quot] Next j[/FONT]
[FONT=&quot] lastRow = lastRow + 1[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] Next s[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Dim x As Integer[/FONT]
[FONT=&quot] Worksheets("wksTo").Activate[/FONT]
[FONT=&quot] For x = 1 To Worksheets.Count[/FONT]
[FONT=&quot] Cells(x, 1).Value = Worksheets(x).Name[/FONT]
[FONT=&quot] Next x[/FONT]
Dear all,

I need some help in writing a code for the following:
I have a workbook with 11 worksheets in it. Each worksheet has a unique name (such as 167L, 167S, 170S, 170L, 173S, 173L, 175A, 183S, 183L and etc.). Each of the worksheet is updated daily and I need to extract data from specific columns and according to specific time.

A	B	C	D	E	F	G	H	I
173L
DATE	TIME	W	DS	WH	S	WC	E	C
bars	bars	C⁰	%	%	%	0/64"
27.10.13	00:00	95	28	18	0	0	0	24/64"
	02:00	91	28	18	0	0	2	24/64"
	04:00	89	28	17	0	0	0	24/64"
	06:00	93	28	16	0	0	1	24/64"
	08:00	95	28	16	0	0	3	24/64"
	10:00	90	28	16	0	0	12	24/64"
	12:00	89	28	14	0	0	16	24/64"
	14:00	92	28	14	0	0	5	24/64"
	16:00	95	28	15	0	0	7	24/64"
	18:00	92	28	14	0	0	8	24/64"
	20:00	93	28	14	0	0	12	24/64"
	22:00	90	28	14	0	0	6	24/64"
28.10.13	00:00	98	28	14	0	0	0	24/64"

Basically, the code I have used before I was assisted by the www.mrexcel.com/forum before, however, I need to have it devised in a bit different way (pls visit the www….. for details). The code I need should (a) select values at column “B” (the corresponding values are 04:00, 08:00, 12:00, 16:00, 20:00 and 00:00), copy value in column “G” (0, 0, 0, 0, 0, 0) and sum up with values from column “H”, which before being added should be divided by a factor of “2” (0/2=0, 3/2=1.5, 16/2=8, 7/2=3.5, 12/2=6, 0/2=0). The summed values should be copied and pasted into a separate worksheet and it needs to look like this:

	04:00	08:00	12:00	16:00	20:00	00:00
173L	0	1.5	8	3.5	6	0

The things get a bit complicated as different worksheet in a workbook have slightly different table compare with the one in above.

A	B	C	D	E	F	G	H
183L
DATE	TIME	W	DS	WH	S	WC	C
bars	bars	C⁰	%	%	0/64"
27.10.13	00:00	30	11.5	17	0	3	20/64"
	02:00	34	11.5	17	0	1	20/64"
	04:00	38	11.5	17	0	1	20/64"
	06:00	38	11.5	17	0	2	20/64"
	08:00	37	11.5	15	0	0	20/64"
	10:00	36	11.5	15	0	0	20/64"
	12:00	31	11.5	15	0	0.5	20/64"
	14:00	28	11.5	15	0	0	20/64"
	16:00	25	11.5	15	0	0	20/64"
	18:00	21	11.5	17	0	4	20/64"
	20:00	33	11.5	17	0	2	20/64"
	22:00	35	11.5	18	0	1	20/64"
28.10.13	00:00	35	11.5	18	0	0	20/64"

So, the final output from two worksheets should look like:

	04:00	08:00	12:00	16:00	20:00	00:00
173L	0	1.5	8	3.5	6	0
183L	1	0	0.5	0	2	1

And finally, to make things more complicated, the monitoring hours vary, like in the table below:

A	B	C	D	E	F	G	H	I
175A
DATE	TIME	W	DS	WH	S	WC	E	C
		bar	bar	C^o	22/64"	%	%	%
27.10.13	00:00	40	9	20	16/64"	31		trace
	00:30	40	9	20	16/64"	32		trace
	01:00	40	9	20	16/64"	37		trace
	01:30	40	9	20	16/64"	40		trace
	02:00	40	9	20	16/64"	39		trace
	02:30	40	9	20	16/64"	41		trace
	03:00	40	9	20	16/64"	36		trace
	03:30	40	9	20	16/64"	33		trace
	04:00	40	9	20	16/64"	35		trace
	04:30	40	9	20	16/64"	38		trace
	05:00	40	9	20	16/64"	40		trace
	05:30	40	9	20	16/64"	37		trace
	06:00	40	9	20	16/64"	33		trace
	06:30	40	9	20	16/64"	36		trace
	07:00	40	9	18	16/64"	27		trace
	08:00	40.5	10	18	16/64"	29		trace
	09:00	40.5	10	18	16/64"	35		trace
	10:00	40.5	10	18	16/64"	26		trace
	11:00	40.5	10	18	16/64"	29		trace
	12:00	40.5	10	18	16/64"	32		trace
	13:00	41	10	18	16/64"	31		trace
	14:00	41	10	18	16/64"	28		trace
	15:00	41	10	18	16/64"	42		trace
	16:00	41	10	18	16/64"	36		trace
	17:00	41	10	18	16/64"	27		trace
	18:00	41	10	18	16/64"	32		trace
	19:00	40	9.5	18	16/64"	36		trace
	20:00	40	9.5	18	16/64"	31		trace
	21:00	40	9.5	18	16/64"	34		trace
	22:00	40	9.5	18	16/64"	29		trace
	23:00	40	9.5	18	16/64"	33		trace
28.10.13	00:00	40	9.5	18	16/64"	31		trace

With this worksheet (175A), my final output should look like this:

	04:00	08:00	12:00	16:00	20:00	00:00
173L	0	1.5	8	3.5	6	0
183L	1	0	0.5	0	2	1
175A	35	29	32	36	31	31

Since there are no value at column “H”, divided by a factor of “2” is skipped.

Here is a code I used before, and it executes part of the required job:

Dim myrow As Long
Dim wksTo As Worksheet
Dim j, s As Long
Dim lastRow, lastsheet As Long
Dim wks As Worksheet
Dim sn As String

Sheets(1).Select
Sheets.Add.Name = "wksTo"
lastRow = 2 ' set the first row to use, set to 2 so you can add labels in row 1
lastsheet = Sheets.Count
For s = 2 To lastsheet ' Loop thru sheets
Worksheets(s).Activate
With Worksheets(s) ' sheet(s) changes sheets
On Error Resume Next
myrow = .Columns("A").Find(Date).Row
Application.Goto .Range("A" & myrow), True
End With

With Worksheets(s)
ActiveCell.Offset(rowoffset:=0, columnoffset:=1).Activate
End With

With Application.ActiveWindow.ActiveCell
dc = 0
sn = Sheets(s).Name
Worksheets("wksTo").Range("G" & lastRow).Formula = sn
For j = 1 To -10 Step -2
Range(.Cells(j, 6), .Cells(j, 6)).Copy Sheets("wksTo").Cells(lastRow, 8 - dc)
dc = dc + 1
Next j
lastRow = lastRow + 1
End With
Next s

Dim x As Integer
Worksheets("wksTo").Activate
For x = 1 To Worksheets.Count
Cells(x, 1).Value = Worksheets(x).Name
Next x

Copy and paste from multiple worksheets to another (combine results into a table)

Bamerand

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

Copy and paste from multiple worksheets to another (combine results into a table)

Bamerand

Board Regular

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock