Copy paste automation

[sms2luv]

Hi,
I have got superb help from here several times.
I want to do a small task.
I have data in on file and I have to update data everyday with new data.

Everyday, I receive a excel sheet with new data and I have to add the data to the file.
To make it easily, I will give a file name.

Copycat is the original file, which I have to keep on updating with new data.
Copyfrom is the file which I receive on daily basis with new data.
I want a macro which will paste copyfrom data to copycat file without opening it.

So for example I opened the copyfrom file, it should find the last used cell in copycat and add the data to copycat file,

I hope you understood

 

[Nebu]

Hi:

Looks like you want to build a database based on the data you receive on a daily basis. What you asked above can be achieved through some simple codes. However, since you receive data on a daily basis your original file is going to grow in size and will be difficult to handle in future. I would recommend you to use Access instead of excel for building a data base its a fairly simple exercise.

• Create a blank database.
• Link your original file to the data base.
• Build a make table query using wizard to convert the linked file into a table.
• Link your daily file to the database
• Build an append query using wizard and append your daily data to the table you created .

Note: you have to save the daily data in the linked excel file replacing the existing data everyday, since you append the daily data everyday to your master table you will not loose any data in this process.

For further details Google how to build database in access table using linked file.

Thanks

 

[sms2luv]

Thanks for your response, I don't have access to ms access due to policy.
I guess we can make a macro that will find the last cell and add data

 

[Marc L]

Hi !

So attach original workbook …
And explain where workbooks are saved on hard disk …
​

 

[sms2luv]

I have attached an example workbook earlier.
Files are saved in My Documents.

 

[Marc L]

Sorry but that does not match with your initial post !

And what is the path of « My Documents » ?‼
​

 

[sms2luv]

Hi Team,
I was just wondering if I can use the below command.

Sub copycat
Sheets("sheet1").activecell.currentregion.copy
Destination :- workbook("C:/Users/Xyz/Copycat.xlxs").range(a5080)
End sub