I need to copy specific information across several sheets in one workbook into one sheet in a seprate workbook

[Kermodae]

Hello, I am trying to rebuild and hopefully automate a very manual report that pulls specific sets of data that's housed in several different sheets in one workbook, onto a corresponding community sheet in a separate workbook. The community data I need is currently pulled by one report and breaks the data up into 56 sheets, 2 sheets per community, for each of 28 communities. I would ideally like a VBA that takes a single communities data from the 2 sheets it's housed in on the bulky report, and paste it into one community sheet with all the data needed in a separate workbook.
Should I try to consolidate the data from ComunitiesReport from 56 back to just the 28, then VBA the data I need from the consolidated sheets into the New workbook, or is there a way to VBA just the data I need from each of the 56 sheets onto the one sheet per community, 28 sheets total, in the new workbook.

 

[alansidman]

Have you considered using Power Query/Get and Transform to append your data? Seems like this is something that PQ could do easily, however, I can provide no further info as you have not provided any sample data to experiment with.

 

[Kermodae]

Hello, In this scenario, it'd be easier for me to go through each sheet and copy the information over into the hew workbook than try to get the PowerQuerey to upload and append the 56 sheets.

Is there an easier way to upload the example files to show you or is the XL2BB the only way to share data?

 

[Kermodae]

This is one tab's data:

MRE EX Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	Supporting Detail for A@AP
2
3
4										EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA			I ONLY NEED THIS DATA
5										ALZ	7/1/2019	4	3	0	1	0	0	0	72	72			EX DATA	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
6	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA	EX DATA		ALZ	8/1/2019	3	7	1	0	0	0	0	72	68			a		1				1				1
7	ALZ	7/1/2019	7/1/2019	72	1	0	0	0		ALZ	9/1/2019	2	5	0	0	0	0	0	67	65			b		2				1	2
8	ALZ	7/1/2019	7/2/2019	73	1	0	0	0		ALZ	10/1/2019	3	1	0	0	0	0	0	65	67			c	1
9	ALZ	7/1/2019	7/3/2019	73	0	0	0	0		ALZ	11/1/2019	2	7	0	0	0	0	0	67	62			d			1
10	ALZ	7/1/2019	7/4/2019	73	0	0	0	0		ALZ	12/1/2019	2	5	0	0	3	0	0	62	59			e	1	4	3	1	5	1	3	2	3	1	5	2
11	ALZ	7/1/2019	7/5/2019	73	0	0	0	0		ALZ	1/1/2020	4	8	0	0	0	0	0	59	55			f						1
12	ALZ	7/1/2019	7/6/2019	73	0	0	0	0		ALZ	2/1/2020	1	3	0	0	0	0	0	54	53			g			1				1	1
13	ALZ	7/1/2019	7/7/2019	73	0	0	0	0		ALZ	3/1/2020	3	3	0	0	0	0	0	53	53			h						1	2
14	ALZ	7/1/2019	7/8/2019	73	0	0	0	0		ALZ	4/1/2020	0	2	0	0	0	0	0	53	51			I	1
15	ALZ	7/1/2019	7/9/2019	73	0	0	0	0		ALZ	5/1/2020	0	5	0	0	0	0	0	51	46			j					1
16	ALZ	7/1/2019	7/10/2019	73	0	0	0	0		ALZ	6/1/2020	1	2	0	0	0	0	0	46	45			k					1
17	ALZ	7/1/2019	7/11/2019	73	0	0	0	0
18	ALZ	7/1/2019	7/12/2019	72	0	1	0	0
19	ALZ	7/1/2019	7/13/2019	72	0	0	0	0
20	ALZ	7/1/2019	7/14/2019	72	0	0	0	0
21	ALZ	7/1/2019	7/15/2019	72	1	1	0	0
22	ALZ	7/1/2019	7/16/2019	73	1	0	0	0
23	ALZ	7/1/2019	7/17/2019	73	0	0	0	0
24	ALZ	7/1/2019	7/18/2019	73	0	0	0	0
25	ALZ	7/1/2019	7/19/2019	73	0	0	0	0
26	ALZ	7/1/2019	7/20/2019	73	0	0	0	0
27	ALZ	7/1/2019	7/21/2019	73	0	0	0	0
28	ALZ	7/1/2019	7/22/2019	73	0	0	0	0
29	ALZ	7/1/2019	7/23/2019	73	0	0	0	0
30	ALZ	7/1/2019	7/24/2019	73	0	0	0	0
31	ALZ	7/1/2019	7/25/2019	72	0	1	0	0
32	ALZ	7/1/2019	7/26/2019	72	0	0	0	0
33	ALZ	7/1/2019	7/27/2019	72	0	0	0	0
34	ALZ	7/1/2019	7/28/2019	72	0	0	0	0
A@AP-SupportDetail

 

[Kermodae]

This is the same community's other sheet containing data I need in only one sheet in a separate workbook:

MRE EX Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	TTM OCC A@AP
2
3
4	I need this dataset
5		Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
6	Memory Care	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
7	EX DATA	72	68	65	67	62	59	55	53	53	51	46	45
8	EX DATA	72.0%	68.0%	65.0%	67.0%	62.0%	59.0%	55.0%	53.0%	53.0%	51.0%	46.0%	45.0%
9	EX DATA	73.0%	70.0%	66.0%	67.0%	68.0%	69.0%	63.0%	63.0%	64.0%	64.0%	65.0%	65.0%
10	EX DATA	100	100	100	100	100	100	100	100	100	100	100	100
11	EX DATA	73	70	66	67	68	69	63	63	64	64	65	65
12	EX DATA	4	3	2	3	2	2	4	1	3	0	0	1
13	EX DATA	3	7	5	1	7	5	8	3	3	2	5	2
14	EX DATA	0	0	0	0	0	0	0	0	0	0	0	0
15	EX DATA	0	0	0	0	0	0	0	0	0	0	0	0
16		Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20
17	Total	Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11	Month 12
18	EX DATA	72	68	65	67	62	59	55	53	53	51	46	45
19	EX DATA	72.0%	68.0%	65.0%	67.0%	62.0%	59.0%	55.0%	53.0%	53.0%	51.0%	46.0%	45.0%
20	EX DATA	73.0%	70.0%	66.0%	67.0%	68.0%	69.0%	63.0%	63.0%	64.0%	64.0%	65.0%	65.0%
21	EX DATA	100	100	100	100	100	100	100	100	100	100	100	100
22	EX DATA	73	70	66	67	68	69	63	63	64	64	65	65
23	EX DATA	4	3	2	3	2	2	4	1	3	0	0	1
24	EX DATA	3	7	5	1	7	5	8	3	3	2	5	2
25	EX DATA	0	0	0	0	0	0	0	0	0	0	0	0
26	EX DATA	0	0	0	0	0	0	0	0	0	0	0	0
27
28
A@AP

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M18	Cell Value	<>45	text	NO
L18	Cell Value	<>46	text	NO
K18	Cell Value	<>51	text	NO
J18	Cell Value	<>53	text	NO
I18	Cell Value	<>53	text	NO
H18	Cell Value	<>55	text	NO
G18	Cell Value	<>59	text	NO
F18	Cell Value	<>62	text	NO
E18	Cell Value	<>67	text	NO
D18	Cell Value	<>65	text	NO
C18	Cell Value	<>68	text	NO
M7	Cell Value	<>($L$7+$M$12+$M$14)-($M$13+$M$15)	text	NO
L7	Cell Value	<>($K$7+$L$12+$L$14)-($L$13+$L$15)	text	NO
K7	Cell Value	<>($J$7+$K$12+$K$14)-($K$13+$K$15)	text	NO
J7	Cell Value	<>($I$7+$J$12+$J$14)-($J$13+$J$15)	text	NO
I7	Cell Value	<>($H$7+$I$12+$I$14)-($I$13+$I$15)	text	NO
H7	Cell Value	<>($G$7+$H$12+$H$14)-($H$13+$H$15)	text	NO
G7	Cell Value	<>($F$7+$G$12+$G$14)-($G$13+$G$15)	text	NO
F7	Cell Value	<>($E$7+$F$12+$F$14)-($F$13+$F$15)	text	NO
E7	Cell Value	<>($D$7+$E$12+$E$14)-($E$13+$E$15)	text	NO
D7	Cell Value	<>($C$7+$D$12+$D$14)-($D$13+$D$15)	text	NO
C7	Cell Value	<>($B$7+$C$12+$C$14)-($C$13+$C$15)	text	NO