VBA code needed: How to copy from a sheet into an existent workbook adding data

[Veyron]

Hi Mr.excelsiors,

I have 3 workbooks with the same table that users need to fill with their own information. I would like to centralize all answers in a different workbook (master workbook) that already exists.

So the idea is as follows: User 1 opens "workbook_user1" and fills the information. Once he is done, he presses the macro button and sends the information which is copied into the "master_workbook". User 2 opens "workbook_user2" and doest exactly the same and the information is sent to the master workbook and pasted below user1 information. Each time the copy&paste is done, it should save the master workbook without prompting.

I have been struggling how to approach this problem as it should not be difficult to solve. At the beginning I tried saving the information in a different sheet in each workbook and using the option "consolidate" in the masterbook, trying to put all the information together, but it did not work.

After that, I tried to use a macro to send the information from the users workbook into the masters workbook, by copying some people ideas to a different problem, but I ended up with a Frankenstein macro that does not work as it should.


Can you give me a helpful hand?

 

[Veyron]

What I have been able to do so far is storing the historical information in each workbook but in a different worksheet and creating a master workbook linking it to those worksheets.
It kinda works but I would like to know how to do it via VBA, as it should be the "cleanest" option.

 

[dmt32]

Hi,
Have a look here:https://www.mrexcel.com/forum/excel-questions/1005007-standard-entry-another-worksheet.html

#Post 10 is code I helped another with similar requirement to yours which you may be able to adapt to match your specific need.

Dave

 

[My Aswer Is This]

Look at this script and modify if needed.

The script copies your Table named "Table1"
To a workbook named "MasterTable"

The script copies the Table into Column(1)
The script assumes you have data in column (1) of each Table.

Both Workbooks must be open.
I did not include the save portion you wanted.
If you get this script to work we can work on the save option later.
Saving without knowing for sure things worked the way you wanted can be dangerous.

Sub Copy_My_Table()
'Modified 6/24/18 5:25 AM EDT
Dim Lastrow As Long
Lastrow = Workbooks("MasterTable.xlsm").Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 2
ThisWorkbook.Sheets(1).ListObjects("Table1").Range.Copy Workbooks("MasterTable.xlsm").Sheets(1).Cells(Lastrow, 1)
End Sub

You will need to put this script into each Users Workbook

 

[Veyron]

This is exactly what I was looking for.
Tested and running smoothly!

Thanks Dave!

 

[Veyron]

I will use the code above yours as it includes the save portion, and the master workbook can be closed.
I definitely appreciate your reply too.

 

[My Aswer Is This]

I will use the code above yours as it includes the save portion, and the master workbook can be closed.
I definitely appreciate your reply too.

The Master Workbook is not really closed. The script opens the workbook and then closes it.

 

[dmt32]

Hi,
glad solution helped. Just be aware I was bit lazy when coded this & just place the file path in last cell column 1 of the worksheet.

file path Dialog only appears when workbook is opened & if this cell is empty.

If you need to change the file path to the master workbook at any time, then just delete the contents of the cell & when workbook opens, you get the dialog appear.

Better still, add another worksheet where can display the File &b path together with button that calls the function - this to allow users to do it themselves.

Dave

 

[Veyron]

The Master Workbook is not really closed. The script opens the workbook and then closes it.

That's correct.

I have set up everything using Dave's codes, but I have noticed that the information is pasted in the masterbook all in one row (transpose?).
How can it be fixed?

 

[My Aswer Is This]

That's correct.

I have set up everything using Dave's codes, but I have noticed that the information is pasted in the masterbook all in one row (transpose?).
How can it be fixed?

Dave will have to answer that question.
His code is something I do not understand.
I'm sure he will back here to help you.