[Solved] Pull or Push data from one workbook to another

Hello,

• Attached is an edited version of your Personen-text.xls file. In this file, I built a macro that might work for you.
• I also edited Personen per Maand.xls as I added data to all three Blad sheets for test purposes. These sheet additions (to Blad2 and Blad3) are the only changes I did on this file.

1. Open "Personen-text.xls". Then, open the VB Editor and see the new macro in module z_CopyMacro. The macro is called: CopyToPersonen.
2. Look thru details of this macro. Change the file names so to exactly match your names. Read top comments that explain how to use the macro. If you don't like what I have, make the changes necessary to get it closer to what you had in mind.
3. Go to the "Personen per Maad.xls" file and input dummy values in the cells (all 3 sheets if you want). Then, go back to "Personen.xls" and RUN the macro. I also setup the macro so you can use the shortcut keys "Cntrl + Shift + P". It should copy the cells values from "Personen per Maad.xls" to "Personen.xls" (or whatever names you have).
4. Edit to suit your needs. NOTE: Some of the forum gurus might have much better ways of doing this, depending on your exact needs. But, I hope I helped you get started in some way.
5. Good luck and best regards.

... sauerj

This is actually not that hard. Just open both workbooks.

1. Click in cell B2 on sheet "Aantal personen per maand"
2. press the = button on your keyboard
3. then while the formula bar is still waiting for the formula, click on the other workbook and click in the cell that contains the source data.
4. Make sure you remove the absolute references (ie delete the $ signs from $B$3 so you are left with B3)
5. Then copy the formula down to complete the 5 people.

Save the sheet and close. Next time you open it, Excel will prompt you if you want to update the links, or you can do this manually. I don't have Excel 2003 anymore but I think it is in the file menu. When you find it, there will be an option to change the source file. Just select the new file with the new name.

I have a short video I can send you if you send me your email address. don't post it here, send me a private message.

Hello, Mallycat's approach is definitely the most straightforward. If the "Personen per maad.xls" files never change name or location, and the employee's always use the same "Personen per maad.xls" files, then Mallycat's approach will ALWAYS work great everytime you open "Personen.xls" and update links.
But, as a follow-up to the macro idea, I thought of a few tweaks to what I initially sent you. So, I'm sending updated versions of both files. The main change was saving all of the important variables at the top of the macro making it easier to change the variables in 1 place.
This macro approach is deficient (compared to Mallycat's better link approach) in that you HAVE to open the "Personen per maand.xls" files for this macro to work. For Mallycat's idea, you don't. Just open "Personen.xls" ONLY and it will automatically get the input data from all of the "Personen per maand.xls" files even if they are closed (once you select UPDATE LINKS) and "POOF" you are done!
... Take care, sauerj

Hi Mallycat and Sauerj,

I tried the codes, but this didn't work out for me on the right way...

I was trying something else (simply by recording a macro) and I think I am on the right track by now. But the macro is not acting like the way I expected it to act. It gives an error, which causes that just "imported" sheets are not filled with data from the opened file. I do not understand why this does not work ...
Can someone help me with this?

In this macro I opted for some names, but it is intended that the files may have different names, is here existing a simple code for?

Attached files will make clear what I'm trying to write down, I expect.
The point is that I work from one of the various files (eg "Personen X - test.xls" or "Personen Y - test.xls"), subsequently add the sheet of "personen per maand - test" and should the data from the opened file (personen X or Y or Z or etc.) automatically be inserted, due to links between cells. This could very easily, since these are in the same workmap, I thought. But here is the macro down ...
The last document you can see on the attached files-list is "totaal" and that's what I intended to make... (the total-file)

Do you have tips / ideas?

Greetings Brigitte

Brie, I have studied your files & macro in detail. I am confused. It is not clear what you are after. Your code seems to do exactly opposite what I originally thought you were wanting. I thought you were trying to copy data from the broken-up tables to the single unbroken table. Your macro does the opposite. The only way I can be of any help is if you re-post with clearer instructions, such as ...
1) Start by showing the INPUT file BEFORE making any changes,
2) Then, show the file you want to copy data TO, BEFORE making any changes.
3) Explain the SOURCE location of data to be copied FROM, and TARGET location where data is to be copied TO,
4) Then, include a FINAL file or files showing correct formulas in their proper place that give you the correct results. Manually put these formulas in these FINAL file(s), so they are EXACTLY as they should be, not by a macro that may be not working correctly.

With these instructions, someone should be able to get you code to do this job, or at least get you better started.... Best Regards, sauerj

Sorry, English is not my native language, perhaps causing some confusion ...
I find it quite difficult to indicate how I try to write the macro. The way you like it to show is a lot clearer, so here we go:

1) open the excel file (file is always another - each with a different name, eg "personen X - test" or "personen Y - test)
2) press the macro button (I alreaddy made this, don't know if it's possible to see it in the files I already attached):
3) The macro copies the parent-file ( "personen per maand - test") to the already opened file (depending on which file you've already opened - "personen X - test" or "personen Y - test).
4) Close the parent-file
5) the list per month divided as shown in "Personen per maand - test" is filled with data from the previously opened file ( "personen X - test" or "personen Y - test") - where for example "Personen X - test" should be linked to cell B2 in cell B3 "Personen per maand - test".
6) by copying the reference down the line disappears, that I'll draw through the line style back again.
7) Save file as ... (I prefer doing this by using a pop-up screen)


This way I have the macro actually in my mind.

Hopefully I have something like this clearly able to explain.
If that is not the case let me know.

Thanks so much for your time.
best regards,

Hi Sauerj,

This works perfect! Thanx! I've even expanded the macro, making it possible to convert the csv-file to the list.

The only problem I have to solve now is that the macro is used only for this file. Actually I need to change anything, so it does not matter which file I open. So the macro works in all cases.

My macro code (by now) is:

Please Login or Register  to view this content.

The line where it goes wrong is:
Sheets("Personen X - test1").Select
But I think this will give some problems too:
Sheets("Personen X - test1").Name = "CSV"
Sheets("Blad1").Copy Before:=Workbooks("Personen X - test1.csv").Sheets(1)
cause for now the Workbook is called for example "Personen Y - test1.csv"

Is the solution to this problem easy to write?
Or can someone explain to me what terms are used for this? I've already searched around but I think I'm looking at the wrong names ...

I attached the files - usefull for trying my code I think
Two files are zipped, because it wasn't possible to upload CSV-files.

Best regards,

Brie

Yes! I've found it!

Saurj sorry, I've looked at your solution, but while I had two files open per se, one copy to the clipboard and the other I would again use the clipboard to fill it. Stubborn maybe, but I'm once again went looking for what I had in mind ... I have some rules changed and now it works for any document with the same format. Great!

HTML Code: 

For some reason I wasn't able to mark the lines in blue (or other color), but the lines are shown at the first part;

Dim myOriginalFileName As String
myOriginalFileName = ActiveWorkbook.Name

Dim myOriginalSheetName As String
myOriginalSheetName = ActiveSheet.Name

And then its possible to use the name of the workbook/sheet everywhere.

I now have some nice things to expand, I'm starting to really like it, haha!

Best Regards,

Brigitte

Brie, Good to hear that it is coming together for you. If this Post is generally solved, then please EDIT 1st post to CHANGE Prefix to SOLVED. Read FAQs for further instructions. ... Take Care, sauerj