Macro to select all, delete all, paste, insert formula and drag it down

[ricdamiani]

Hi All,

Can someone please help me on writing the macro below? Steps are:
1) Select all
2) Delete
3) Paste
4) Insert in E2 formula =CONCATENATE(YEAR(D2),"/",CHOOSE(MONTH(D2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"))
5) Drag it down

Thanks,

Rick

[Neil_]

Record your steps and you will have your macro. We can't write one based on your list of steps as the list makes no sense.

1) Select all

Please Login or Register  to view this content.

This will select all

2) Delete

Please Login or Register  to view this content.

That's your sheet wiped

3) Paste
Guaranteed to get an error here as you haven't copied anything

4) Insert formula.
D2 is empty as you have deleted everything on your sheet

5) Drag it down
How far? Your sheet is empty.

[ricdamiani]

3) Paste
Guaranteed to get an error here as you haven't copied anything

4) Insert formula.
D2 is empty as you have deleted everything on your sheet

5) Drag it down
How far? Your sheet is empty.

Hi Neil_,

Thanks for your reply.

Ok, will try to make things more clear now:

3) Before running the macro, assume I already have copied something from another sheet, so just need to be pasted in A1;

4) This step is correct. After the step 3 (paste) I have data in the sheet. Column D is the first empty column, and that's where the formula should be pasted;

5) Drag down up to the last populated cell in column C.

Thanks mate!

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[ricdamiani]

Does this help?

Please Login or Register  to view this content.

Hi JOHN H. DAVIS, thanks for your reply.

Ok, I have done the macro below, but it's not working because when it selects and deletes data in the activesheet, it also unselect what I have copied before running the macro in a second workbook.

So there is anyway to add in the macro, after SelectionDelete, to go to the "Received" workbook and select all, and then go back to the "PSI" workbook and paste into A1?

Steps would be:
1) Select all
2) Delete
3) Go to "Received.xls" workbook, select all and copy
4) Go back to "PSI.xlms" workbook and paste it in A1
5) Insert in E2 formula =CONCATENATE(YEAR(D2),"/",CHOOSE(MONTH(D2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"))
5) Drag it down until last cell in column D

Please Login or Register  to view this content.

[ricdamiani]

Up!

Can someone please help me with the above?

Thanks!

[ricdamiani]

Up!

Can someone please help me with the above?

Thanks!

[ricdamiani]

Up. If someone could help me would be great.

Thanks,

[ricdamiani]

Still hoping someone can give me a hand...

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[ricdamiani]

Try:

Please Login or Register  to view this content.

Hi JOHN H. DAVIS,

That's great, thanks for that.

Just wondering one thing:

Trying to replicate this macro to others workbooks, but these workbooks have random names (because they are in a temp location in our email server). So I was wondering if there is a way to access "the other" workbook without actually naming it?

For example, the above macro is copying from "Receiving.xlsx", but what if it was a random name, like "~6547954.xlsx"? Is there a way to copy from a second workbook without actually naming it?

Thanks

[JOHN H. DAVIS]

If you open the first workbook. And next open the second workbook. Then you can either refer to the second workbook as:

Please Login or Register  to view this content.

If you have created a Personal.xlsb

Or

Please Login or Register  to view this content.

If you haven't. Hope that helps.

[ricdamiani]

Beautiful! Thank you very much my friend!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback and the reps.