VBA copying data from multiple worksheets to a master file

[nikolacm]

Date	Close	Adj Close	Returns
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,

 

[Larry Haydn]

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]

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]

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.

 

[Larry Haydn]

I have worked out the codes.
You can download the complete file here:

CountingDates.xlsm

drive.google.com