How to create a new file on first instance and collate data in same file for next attempts

[ThrottleWorks]

Hi,

I need to collate data from a worksheet.

On every day, if the macro is getting executed for the first time, then macro should create a new file, paste data from input sheet in sheet1 of new file, save it and close it.

From 2nd instance onwards, macro should collate the data in new created file.

For example,

today, 1st instance count = 100
today, 2nd instance count = 100
today, 3rd instance count = 100
today, 4th instance count = 100
today, 5th instance count = 100

so the final count from collated file will be 500
How do I do this. Can anyone please help me in this.

 

[PCosta87]

Hi,

I would probably store the new file's ".Full Name" in some way (a cell perhaps) and then use

If Dir("File's full name here") = "" Then

to check if the file already exists. If it does, open the workbook, if it doesn't, create new one.

Also, if the cell where the file's name is to be stored = "", it means it is the first instance and you can skip to "create new file".

 

[vletm]

@ThrottleWorks
Could You name that file daily-based?
Then You'll always knows file's name.
... new day makes new file and so on ...

 

[Monty]

vletm

I would recommend to have two separate command buttons .

1) Command button 1
This is for your first instances to run which will create a new workbook and will paste data from input sheet in sheet1 of new file, save it and close it.

2) Command button 2
This is for your second, third or so on instances which will collate the data in new created file.

Hope this helps!

 

[ThrottleWorks]

Hi @PCosta87 , thanks a lot for the help. This exact idea came to my mind yesterday while going home. Have a nice day ahead.


Hi @vletm , thanks a lot for the help. Have a nice day ahead.

Hi @Monty , thanks a lot for the help. Have a nice day ahead.