automatic data updation from user workbooks to master workbook?

[punna111]

Hi,

I have one excel file i am maintaining data on date wise.
we have another masterfile in master file one tab is representing my excel file data.
daily i am copying data from my excel, and pasting into master file.

is there any way/macro to automate it.

if i click a button which is on my excel file that data should be update in masterfile.
two files are on network drive only.
but, the condition is without opening the masterfile that data should be saved in masterfile.

can anyone help on this..........

[arlu1201]

Without opening the masterfile, the data cant be updated. However, the macro can open the masterfile for you, copy the contents, save and close it again.

You will need to upload samples of your files so we can understand what needs to be copied.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[punna111]

i have attached two excel sheets.
one use user tracker.
second is master file

i need a button on user tracker.
when he clicks on that the data which is in user tracker should be updated to master file

[punna111]

Thank you very much for replying this thread.....

[punna111]

no one is replying on this........?
please help me excel heroes(ninjas)............

[arlu1201]

So if the master file already has data, should the new data be copied to the next available row?

Or should the existing data in the master file be deleted and then fresh data updated?

[punna111]

i need in two ways

[arlu1201]

What do you mean by 2 ways?

[punna111]

2 ways are

>if the master file already has data, should the new data be copied to the next available row

>existing data in the master file be deleted and then fresh data updated

[arlu1201]

You will require 2 macros for that - one which deletes the other and the other which keeps it.

[punna111]

yes i need 2 macros please mention...

[arlu1201]

Try this code which clears the master file from column B onwards. Change the bold part to match your system.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[arlu1201]

For your 2nd macro where you do NOT want to clear the Masterfile, remove this part of the code-

Please Login or Register  to view this content.

[punna111]

i am getting the following error. It is asking for open files.

error.JPG

[arlu1201]

Oops i didnt mention it to you - since you will have multiple user files which are present in one folder, you cannot keep the macro in the user files. Instead you need to keep the macro in an independent file. It will ask you to open the master file and then it will open up the user files one by one from the path mentioned.

[punna111]

Thanks a lot for this macro. but,

what i am expecting is not this.
Just i want write opened workbook data in a closed workbook.

[arlu1201]

But you cannot do anything with a closed workbook. You will need to open it, do the actions required and then close it.