VBA copying data from multiple worksheets to a master file

nikolacm

New Member
Joined
Mar 31, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
DateCloseAdj CloseReturns
2019-02-25
43.7425​
42.845234​
2019-03-04
43.227501​
42.340797​
-1.18%​
2019-03-11
46.529999​
45.57555​
7.64%​
2019-03-18
47.762501​
46.782772​
2.65%​
2019-03-25
47.487499​
46.513416​
-0.58%​
2019-04-01
49.25​
48.239758​
3.71%​
2019-04-08
49.717499​
48.697666​
0.95%​
2019-04-15
50.965​
49.919586​
2.51%​
2019-04-22
51.075001​
50.027325​
0.22%​
2019-04-29
52.9375​
51.85162​
3.65%​
2019-05-06
49.294998​
48.283844​
-6.88%​
2019-05-13
47.25​
46.459011​
-3.78%​
2019-05-20
44.7425​
43.993488​
-5.31%​
2019-05-27
43.767502​
43.034817​
-2.18%​
2019-06-03
47.537498​
46.741699​
8.61%​
2019-06-10
48.185001​
47.378368​
1.36%​
2019-06-17
49.695​
48.863079​
3.13%​
2019-06-24
49.48​
48.65168​
-0.43%​
2019-07-01
51.057499​
50.202774​
3.19%​
2019-07-08
50.825001​
49.974163​
-0.46%​
2019-07-15
50.647499​
49.799641​
-0.35%​
2019-07-22
51.935001​
51.065586​
2.54%​
2019-07-29
51.005001​
50.151157​
-1.79%​
2019-08-05
50.247501​
49.40633​
-1.49%​
2019-08-12
51.625​
50.953636​
3.13%​
2019-08-19
50.66​
50.001186​
-1.87%​
2019-08-26
52.185001​
51.506355​
3.01%​
2019-09-02
53.314999​
52.621658​
2.17%​
2019-09-09
54.6875​
53.976303​
2.57%​
2019-09-16
54.432499​
53.724625​
-0.47%​
2019-09-23
54.705002​
53.993587​
0.50%​
2019-09-30
56.752499​
56.014454​
3.74%​
2019-10-07
59.052502​
58.284546​
4.05%​
2019-10-14
59.102501​
58.3339​
0.08%​
2019-10-21
61.645​
60.843334​
4.30%​
2019-10-28
63.955002​
63.123295​
3.75%​
2019-11-04
65.035004​
64.189255​
1.69%​
2019-11-11
66.440002​
65.77285​
2.47%​
2019-11-18
65.445​
64.787834​
-1.50%​
2019-11-25
66.8125​
66.141602​
2.09%​
2019-12-02
67.677498​
66.997925​
1.29%​
2019-12-09
68.787498​
68.096771​
1.64%​
2019-12-16
69.860001​
69.158508​
1.56%​
2019-12-23
72.449997​
71.722488​
3.71%​
2019-12-30
74.357498​
73.61084​
2.63%​
2020-01-06
77.582497​
76.803459​
4.34%​
2020-01-13
79.682503​
78.88237​
2.71%​
2020-01-20
79.577499​
78.778435​
-0.13%​
2020-01-27
77.377502​
76.600517​
-2.76%​
2020-02-03
80.0075​
79.204117​
3.40%​
2020-02-10
81.237503​
80.612625​
1.78%​
2020-02-17
78.262497​
77.660507​
-3.66%​
2020-02-24
68.339996​
67.814331​
-12.68%​
2020-03-02
72.2575​
71.701706​
5.73%​
2020-03-09
69.4925​
68.95797​
-3.83%​
2020-03-16
57.310001​
56.869179​
-17.53%​
2020-03-23
61.935001​
61.458603​
8.07%​
2020-03-30
60.352501​
59.888275​
-2.56%​
2020-04-06
66.997498​
66.482155​
11.01%​
2020-04-13
70.699997​
70.156174​
5.53%​
2020-04-20
70.7425​
70.198349​
0.06%​
2020-04-27
72.267502​
71.711632​
2.16%​
2020-05-04
77.532501​
76.936127​
7.29%​
2020-05-11
76.927498​
76.542412​
-0.51%​
2020-05-18
79.722504​
79.323433​
3.63%​
2020-05-25
79.485001​
79.08712​
-0.30%​
2020-06-01
82.875​
82.460152​
4.26%​
2020-06-08
84.699997​
84.276009​
2.20%​
2020-06-15
87.43​
86.992348​
3.22%​
2020-06-22
88.407501​
87.964951​
1.12%​
2020-06-29
91.027496​
90.571838​
2.96%​
2020-07-06
95.919998​
95.439842​
5.37%​
2020-07-13
96.327499​
95.845299​
0.42%​
2020-07-20
92.614998​
92.151382​
-3.85%​
2020-07-27
106.260002​
105.728088​
14.73%​
2020-08-03
111.112503​
110.556305​
4.57%​
2020-08-10
114.907501​
114.538445​
3.60%​
2020-08-17
124.370003​
123.970551​
8.23%​
2020-08-24
124.807503​
124.406647​
0.35%​
2020-08-31
120.959999​
120.571503​
-3.08%​
2020-09-07
112​
111.640282​
-7.41%​
2020-09-14
106.839996​
106.496849​
-4.61%​
2020-09-21
112.279999​
111.919373​
5.09%​
2020-09-28
113.019997​
112.657005​
0.66%​
2020-10-05
116.970001​
116.594315​
3.49%​
2020-10-12
119.019997​
118.637726​
1.75%​
2020-10-19
115.040001​
114.670517​
-3.34%​
2020-10-26
108.860001​
108.510361​
-5.37%​
2020-11-02
118.690002​
118.3088​
9.03%​
2020-11-09
119.260002​
119.082054​
0.65%​
2020-11-16
117.339996​
117.164917​
-1.61%​
2020-11-23
116.589996​
116.416031​
-0.64%​
2020-11-30
122.25​
122.067596​
4.85%​
2020-12-07
122.410004​
122.227356​
0.13%​
2020-12-14
126.660004​
126.471016​
3.47%​
2020-12-21
131.970001​
131.773087​
4.19%​
2020-12-28
132.690002​
132.49202​
0.55%​
2021-01-04
132.050003​
131.852966​
-0.48%​
2021-01-11
127.139999​
126.950294​
-3.72%​
2021-01-18
139.070007​
138.862503​
9.38%​
2021-01-25
131.960007​
131.763107​
-5.11%​
2021-02-01
136.759995​
136.555939​
3.64%​
2021-02-08
135.369995​
135.369995​
-0.87%​
2021-02-15
129.869995​
129.869995​
-4.06%​
2021-02-22
121.260002​
121.260002​
-6.63%​
2021-03-01
120.129997​
120.129997​
-0.93%​

Hi there, I would like to create a master worksheet which will contain information from 25 several worksheets which have the same format as indicated below. More specifically I would like to create a table which will contain first column as indicated in the above table, the first row should contain the name of each worksheet and beneath them the values indicated in the last column of the above table. How should I prepare it with the assistance of VBA ?
Attached image presents the format of the final (master) table.

Thank you in advance,
 

Attachments

  • querry.JPG
    querry.JPG
    20.6 KB · Views: 3

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
115
Office Version
  1. 365
Platform
  1. Windows
Your desired format does not tell us very much about what you need to do.
Do you wish to count or aggregate the information by date?
 

nikolacm

New Member
Joined
Mar 31, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Your desired format does not tell us very much about what you need to do.
Do you wish to count or aggregate the information by date?
Hi Larry,

I wish to count the information date. More specifically the new table will comprise of n columns which will indicate the name of sheet and n rows which are the data per date. First column will include the dates.
 

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
115
Office Version
  1. 365
Platform
  1. Windows
Sorry I was out of action for quite a while.

OK, so lets us look at your requirements.
You want a table to...
...count the number of dates
...from each worksheet

Assume that the worksheets are all within your current workbook.

How would I approach this problem?

1. Create a worksheet to store the resulting table.
Lets call it RESULT.

2. Create a list in another worksheet
to tell me which worksheet's data goes into which column.

3. Create a list of all the possible dates that will appear within all those worksheets.
...loop through all the worksheets (except RESULT, and any other worksheets you want to exclude)
...copy and paste/append the dates into column A of RESULT
...sort the complete date list in column A
...remove duplicate dates in column A

4. Loop through the required worksheets (exclude RESULT)
...write the worksheet name in row 1 of RESULT
...copy the first column (Dates) from this worksheet
...paste the dates to row 2 in RESULT
...sort and remove duplicates
...insert a COUNT or COUNTA formula to count each date

I'll come back with the codes in a few days.
Meanwhile, if you can work through it yourself, do post an update here.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,594
Messages
5,637,299
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top