Excel Autofilling copied data into a new column

[Jackb1]

Hi everyone,

Interesting question for everyone here, I was wondering if someone could assist me with it.

I have a situation where I have forms I need to be filled out every month using the same cells. I want to take these cells copy them into a new sheet. Which will show a running progression throughout the lifecycle of that task. I would like system to identify the next blank column on the new table and copy new information for the month into the cell to the right of it then date the top of the new table with the month. This automates most of the admin tasks and provides data that should be Power BI friendly (I Hope!).

For ease I have listed the tasks below:

1. Select data that's in a range in a sheet (Call the sheet X1)

2. Change to the new sheet (Call it Y1)

3. Search for the next blank column in Y1 table

4. Paste the numbers into Y1 blank column

5. Add month to top of table of the month the data was entered

Admittedly, I was once good at Macros, however it has been years since I last wrote one, therefore I have made little progress into this approach. If anyone could provide some assistance into this I would be most grateful, Maybe macros are not the right approach and I am just missing something.

Thank you in advance.

[#2]

Can u pls share sample data format in sheet X1?
Is it just one column?

[#3]

@bhushan_z 

Thank you for you reply.

Please see attached image.

Note: The tables have been put into one sheet for ease however for the final document they will be in different sheets.

In Y1 I have manually entered information to show how I would like the information to be displayed

[#4]

To seek further clarification,
Every time you run a macro, it needs to copy from X1's column C and paste it to the right hand side of the table, in blank column.
is that correct?

[#5]

@bhushan_z 

Thank you for your response,

Exactly that! Where X1 will be in a different sheet and it will copy the data in the blank column to the right into Y1.

You explained it much better than myself!

[#6 · Accepted solution]

@Jackb1 file attached. Should work.

[#7]

@bhushan_z 

That works incredibly well. Thank you.

Amazing! :)