Need help with vba code

[evelynbonto12]

Hi everyone, this is my first time with VBA. I need help extracting data. This is what I want to do:

1. Open the source workbook then search column A with labels from P1-P168 (there are other labels in this column like B1, T2 etc.)
2. From the multiple P1 found, I want to find the minimum value (usually 0) and maximum value in column B.
3.When found, I want to copy the corresponding values of V2 and V3 for the minimum and maximum values and paste in on my destination workbook. (minimum is start and maximum is end)

Im not good at English so I hope this gets understood very well.

 

[kevin9999]

Could you post a copy of your 2 sheets using the XL2BB add in, or alternatively, share your file via Dropbox, Google Drive or similar file sharing platform.

 

[evelynbonto12]

Loading Google Sheets

Loading Google Sheets

hey these are the link

 

[kevin9999]

Thanks for that I note that your 'source' file has a different format in column A than what you showed us in post #1. Will the data have a "P" prefix before the number or not? Also, do you expect to run the code with both the source and destination files already open, or should both sheets be in the same workbook, and if they are in separate workbooks, which one do you want to house the code in?

 

[evelynbonto12]

In the source file column A, there are a lot of labels (even with just numbers), I just want the ones with the prefix P. Also, I want to run the code in the destination files only if that's ok. The source file is also not opened initially. Thank you so much. This is for my thesis and I just want to automate extracting data since I will have 20 source files.

 

[kevin9999]

Thank you. Leave it with me.

 

[kevin9999]

Try the following on a copy of your workbook. The code sits in a standard module in the destination file, and assumes your layouts are exactly as you've indicated in your shared files. The code will ask you to locate & select the source file first. A link to the destination file is also provided here.

destination macro.xlsm

Option Explicit
Sub evelynbonto12()
    Application.ScreenUpdating = False
    Dim wb1 As Workbook, wb2 As Workbook
    Dim FileName As String
    Set wb1 = ThisWorkbook
    
    FileName = Application.GetOpenFilename _
    (filefilter:="Excel files (*.xlsx),*.xlsx", MultiSelect:=False)
    If Dir(FileName) <> "" Then
        Set wb2 = Workbooks.Open(FileName)
        
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = wb1.Worksheets(1)
        Set ws2 = wb2.Worksheets(1)
        
        With ws2.Range("A3:G" & ws2.Cells(Rows.Count, "G").End(xlUp).Row)
            .AutoFilter 1, "P*"
            .Offset(1).Copy ws1.Range("H5")
        End With
        wb2.Close 0
        
        Dim LR As Long
        LR = ws1.Range("H:N").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        
        With ws1.Range("C5:C" & ws1.Cells(Rows.Count, "B").End(xlUp).Row)
            .FormulaR1C1 = "=SUMPRODUCT(--(R5C9:R" & LR & "C9=MINIFS(R5C9:R" & LR & "C9,R5C8:R" & LR & _
            "C8,RC2))*(R5C8:R" & LR & "C8=RC2),R5C13:R" & LR & "C13)"
        End With
        With ws1.Range("D5:D" & ws1.Cells(Rows.Count, "B").End(xlUp).Row)
            .FormulaR1C1 = "=SUMPRODUCT(--(R5C9:R" & LR & "C9=MAXIFS(R5C9:R" & LR & "C9,R5C8:R" & LR & _
            "C8,RC2))*(R5C8:R" & LR & "C8=RC2),R5C13:R" & LR & "C13)"
        End With
        With ws1.Range("E5:E" & ws1.Cells(Rows.Count, "B").End(xlUp).Row)
            .FormulaR1C1 = "=SUMPRODUCT(--(R5C9:R" & LR & "C9=MINIFS(R5C9:R" & LR & "C9,R5C8:R" & LR & _
            "C8,RC2))*(R5C8:R" & LR & "C8=RC2),R5C14:R" & LR & "C14)"
        End With
        With ws1.Range("F5:F" & ws1.Cells(Rows.Count, "B").End(xlUp).Row)
            .FormulaR1C1 = "=SUMPRODUCT(--(R5C9:R" & LR & "C9=MAXIFS(R5C9:R" & LR & "C9,R5C8:R" & LR & _
            "C8,RC2))*(R5C8:R" & LR & "C8=RC2),R5C14:R" & LR & "C14)"
        End With
        
        With ws1
            With .Range("C5:F" & LR)
                .Value2 = .Value2
            End With
            .Range("H:N").EntireColumn.Delete
        End With
    End If
    Application.ScreenUpdating = True
End Sub

Destination sheet before running code (partial view)

destination macro.xlsm
	A	B	C	D	E	F
1
2
3			V2		V3
4			Start	End	Start	End
5		P1
6		P2
7		P3
8		P4
9		P5
10		P6
11		P7
12		P8
13		P9
14		P10
Sheet1

After running the code:

destination macro.xlsm
	A	B	C	D	E	F
1
2
3			V2		V3
4			Start	End	Start	End
5		P1	-0.58099	0.857178	0.340668	-0.34067
6		P2	-0.83181	0.912045	0.340668	-0.34067
7		P3	-1.20831	1.368795	0.306601	-0.3066
8		P4	-0.91294	0.933518	0.306601	-0.3066
9		P5	-1.43548	1.469584	0.306601	-0.3066
10		P6	-0.94507	0.929274	0.306601	-0.3066
11		P7	-1.51455	1.520728	0.306601	-0.3066
12		P8	-0.85463	0.80679	0.306601	-0.3066
13		P9	-1.51509	1.507044	0.306601	-0.3066
14		P10	-0.68381	0.632595	0.306601	-0.3066
15		P11	-1.4854	1.478714	0.306601	-0.3066
16		P12	-0.55919	0.551132	0.306601	-0.3066
17		P13	-1.479	1.485921	0.306601	-0.3066
18		P14	-0.55196	0.561049	0.306601	-0.3066
19		P15	-1.50839	1.517024	0.306601	-0.3066
20		P16	-0.63973	0.692051	0.306601	-0.3066
21		P17	-1.52395	1.518444	0.306601	-0.3066
22		P18	-0.81546	0.862003	0.306601	-0.3066
23		P19	-1.47553	1.442292	0.306601	-0.3066
24		P20	-0.9338	0.948414	0.306601	-0.3066
25		P21	-1.37703	1.228493	0.306601	-0.3066
26		P22	-0.93551	0.915579	0.306601	-0.3066
27		P23	-0.87008	0.585291	0.340668	-0.34067
28		P24	-0.92104	0.838679	0.340668	-0.34067
29		P25	0.615834	-0.66616	0.511662	-0.51166
30		P26	-0.92144	1.00915	0.511662	-0.51166
31		P27	0.473797	-0.42357	0.460496	-0.4605
32		P28	-0.9918	1.017472	0.460496	-0.4605
33		P29	0.299347	-0.26516	0.460496	-0.4605
34		P30	-1.02576	1.005783	0.460496	-0.4605
35		P31	0.211482	-0.20115	0.460496	-0.4605
36		P32	-0.90027	0.83556	0.460496	-0.4605
37		P33	0.197221	-0.20168	0.460496	-0.4605
38		P34	-0.64223	0.559889	0.460496	-0.4605
39		P35	0.215545	-0.21954	0.460496	-0.4605
40		P36	-0.39265	0.352295	0.460496	-0.4605
41		P37	0.218846	-0.21433	0.460496	-0.4605
42		P38	-0.35482	0.396939	0.460496	-0.4605
43		P39	0.199427	-0.19472	0.460496	-0.4605
44		P40	-0.56728	0.64942	0.460496	-0.4605
45		P41	0.198469	-0.20878	0.460496	-0.4605
46		P42	-0.84049	0.904553	0.460496	-0.4605
47		P43	0.262553	-0.297	0.460496	-0.4605
48		P44	-1.00969	1.029281	0.460496	-0.4605
49		P45	0.42238	-0.47219	0.460496	-0.4605
50		P46	-1.02148	0.996591	0.460496	-0.4605
51		P47	0.666746	-0.61726	0.511662	-0.51166
52		P48	-1.01446	0.926183	0.511662	-0.51166
53		P49	0.696139	-0.81286	0.502662	-0.50266
54		P50	-0.8911	0.975773	0.502662	-0.50266
55		P51	0.676614	-0.63923	0.452396	-0.4524
56		P52	3.446718	-3.52321	2.569183	-2.61884
57		P53	0.564181	-0.54445	0.452396	-0.4524
58		P54	3.441143	-3.51996	2.583987	-2.61225
59		P55	0.519859	-0.51864	0.452396	-0.4524
60		P56	3.263223	-3.34164	2.436417	-2.44154
61		P57	0.530736	-0.53938	0.452396	-0.4524
62		P58	3.047079	-3.05619	2.202013	-2.16381
63		P59	0.559092	-0.564	0.452396	-0.4524
64		P60	2.75031	-2.71305	1.930331	-1.89146
65		P61	0.563414	-0.55803	0.452396	-0.4524
66		P62	2.721343	-2.76229	1.897085	-1.93875
67		P63	0.537437	-0.52859	0.452396	-0.4524
68		P64	3.059261	-3.05338	2.169445	-2.20925
69		P65	0.516266	-0.51746	0.452396	-0.4524
70		P66	3.348563	-3.27279	2.448332	-2.44405
71		P67	0.541993	-0.56176	0.452396	-0.4524
72		P68	3.527689	-3.45191	2.617884	-2.59087
73		P69	0.636661	-0.67426	0.452396	-0.4524
74		P70	3.535471	-3.46064	2.626318	-2.5777
75		P71	0.811176	-0.69707	0.502662	-0.50266
76		P72	-0.9807	0.89537	0.502662	-0.50266
77		P73	1.141251	-1.24773	0.742684	-0.74268
78		P74	4.712274	-4.59971	3.190013	-3.17828
79		P75	1.000253	-0.98387	0.629443	-0.63665
80		P76	-0.97005	0.999781	0.452396	-0.4524
81		P77	0.922692	-0.89363	0.636648	-0.62944
82		P78	-1.01338	0.994183	0.452396	-0.4524
83		P79	0.884273	-0.9046	0.629443	-0.63665
84		P80	-0.88287	0.812595	0.452396	-0.4524
85		P81	0.932687	-0.93226	0.636648	-0.62944
86		P82	-0.59649	0.501501	0.452396	-0.4524
87		P83	0.963365	-0.98599	0.629443	-0.63665
88		P84	-0.30779	0.261112	0.452396	-0.4524
89		P85	0.98526	-0.96211	0.636648	-0.62944
90		P86	-0.26478	0.313767	0.452396	-0.4524
91		P87	0.930125	-0.93036	0.629443	-0.63665
92		P88	-0.51088	0.605933	0.452396	-0.4524
93		P89	0.902294	-0.88202	0.636648	-0.62944
94		P90	-0.82015	0.889405	0.452396	-0.4524
95		P91	0.8913	-0.92041	0.629443	-0.63665
96		P92	-0.99826	1.017052	0.452396	-0.4524
97		P93	0.981621	-0.99811	0.636648	-0.62944
98		P94	-1.00325	0.973999	0.452396	-0.4524
99		P95	1.246145	-1.14259	0.742684	-0.74268
100		P96	4.582884	-4.68585	3.172354	-3.17939
101		P97	0.551211	-0.60538	0.502662	-0.50266
102		P98	-0.86976	0.964986	0.502662	-0.50266
103		P99	0.486538	-0.45627	0.452396	-0.4524
104		P100	-0.96832	1.00036	0.452396	-0.4524
105		P101	0.403036	-0.39364	0.452396	-0.4524
106		P102	-1.01837	1.00082	0.452396	-0.4524
107		P103	0.400135	-0.41345	0.452396	-0.4524
108		P104	-0.8935	0.824062	0.452396	-0.4524
109		P105	0.463653	-0.48755	0.452396	-0.4524
110		P106	-0.60509	0.507469	0.452396	-0.4524
111		P107	0.537438	-0.54955	0.452396	-0.4524
112		P108	-0.30522	0.255983	0.452396	-0.4524
113		P109	0.548474	-0.53564	0.452396	-0.4524
114		P110	-0.25917	0.310665	0.452396	-0.4524
115		P111	0.484539	-0.46049	0.452396	-0.4524
116		P112	-0.51574	0.613331	0.452396	-0.4524
117		P113	0.410523	-0.3974	0.452396	-0.4524
118		P114	-0.82957	0.898105	0.452396	-0.4524
119		P115	0.391548	-0.40109	0.452396	-0.4524
120		P116	-1.00432	1.021661	0.452396	-0.4524
121		P117	0.454775	-0.4851	0.452396	-0.4524
122		P118	-1.00449	0.973155	0.452396	-0.4524
123		P119	0.604201	-0.55069	0.502662	-0.50266
124		P120	-0.97186	0.875133	0.502662	-0.50266
125		P121	3.495531	-3.4547	2.001808	-1.9956
126		P122	3.036375	-2.92313	2.294635	-2.28839
127		P123	0.443363	-0.42449	0.556921	-0.56199
128		P124	-0.56489	0.582607	0.569552	-0.57516
129		P125	0.380395	-0.36641	0.561991	-0.55692
130		P126	-0.59731	0.592082	0.575155	-0.56955
131		P127	0.39304	-0.42759	0.556921	-0.56199
132		P128	-0.49165	0.415893	0.569552	-0.57516
133		P129	0.509142	-0.53595	0.561991	-0.55692
134		P130	-0.21584	0.137791	0.575155	-0.56955
135		P131	0.613951	-0.6436	0.556921	-0.56199
136		P132	0.049078	-0.10642	0.569552	-0.57516
137		P133	0.642363	-0.61179	0.561991	-0.55692
138		P134	0.102875	-0.04333	0.575155	-0.56955
139		P135	0.532477	-0.50557	0.556921	-0.56199
140		P136	-0.14663	0.22466	0.569552	-0.57516
141		P137	0.424807	-0.39053	0.561991	-0.55692
142		P138	-0.42279	0.497594	0.575155	-0.56955
143		P139	0.364181	-0.37827	0.556921	-0.56199
144		P140	-0.59588	0.600756	0.569552	-0.57516
145		P141	0.42217	-0.44099	0.561991	-0.55692
146		P142	-0.58585	0.568469	0.575155	-0.56955
147		P143	3.455806	-3.49354	1.997739	-2.00256
148		P144	2.935016	-3.04638	2.296068	-2.30158
149		P145	-1.5089	1.613309	0.328287	-0.32829
150		P146	-0.44359	0.430591	0.331668	-0.33167
151		P147	-1.52355	1.543447	0.295459	-0.29546
152		P148	-0.43477	0.4557	0.298501	-0.2985
153		P149	-1.56135	1.55594	0.295459	-0.29546
154		P150	-0.46818	0.458239	0.298501	-0.2985
155		P151	-1.51047	1.479377	0.295459	-0.29546
156		P152	-0.3961	0.355785	0.298501	-0.2985
157		P153	-1.38085	1.336686	0.295459	-0.29546
158		P154	-0.2292	0.172163	0.298501	-0.2985
159		P155	-1.24338	1.219977	0.295459	-0.29546
160		P156	-0.05325	0.02458	0.298501	-0.2985
161		P157	-1.22133	1.245652	0.295459	-0.29546
162		P158	-0.0264	0.056616	0.298501	-0.2985
163		P159	-1.3401	1.384246	0.295459	-0.29546
164		P160	-0.17755	0.234538	0.298501	-0.2985
165		P161	-1.48156	1.512275	0.295459	-0.29546
166		P162	-0.35982	0.399543	0.298501	-0.2985
167		P163	-1.55728	1.56258	0.295459	-0.29546
168		P164	-0.46028	0.469996	0.298501	-0.2985
169		P165	-1.54506	1.525357	0.295459	-0.29546
170		P166	-0.45732	0.436569	0.298501	-0.2985
171		P167	-1.61605	1.511313	0.328287	-0.32829
172		P168	-0.43253	0.444872	0.331668	-0.33167
Sheet1

 

[evelynbonto12]

OMG Thank you. Ill have to try this. In what part do you I think I could change to set the file path of the source file?

 

[evelynbonto12]

OK I TRIED IT and it worked THAN YOUUUUUUU

 

[kevin9999]

OK I TRIED IT and it worked THAN YOUUUUUUU

Welcome to the board, and thank you for the feedback