Help copying and pasting worksheet names to another WS

Hello,

I am working on an Excel workbook where I enter data for the company I work for. Now this file compiles monthly data and each WS represents a work day. On each of these WSs I fill about 50/60 rows in 25 columns. Now I want to compile all of this data in a single worksheet. I have managed to do that using a simple copy and paste macro.

My next goal is to have a new column (the 26th) in this compiled Worksheet representing the day from which this data originates. Now, given that I always name the worksheets according to the day they represent I should only need to copy the WS name and paste it in the appropriate place. Unfortunately I can't get this done. I have managed to copy the WS name to their own worksheets but when I try to copy it to the compiled worksheet I only get a consecutive list of the worksheet numbers.

For instance, it shows something like this:

2
3
4
5
6
9
10
11
12

Where I want it to show something like this:

2
2
2
2
2
2
2
2
3
3
3
3
3

Depending on the worksheet where my data originates from.

I can fairly manage around a macro but I am not an expert in code writing or reading.

Thank you in advance for your time.

Thank you, I'll try this code as soon as possible and let you know!