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?

 

[dmt32]

Sorry, did not Realize your question was directed at me.

Code builds and array from template input ranges & then outputs array to next blank row in your master (database) workbook.

If you want to change this to enter data to first row then change this part of code

'Next empty range in database
        With wbDatabase.Sheets(1)
            Set DatabaseRange = .Range("A" & .Cells(.Rows.Count, "A").End(xlUp).Row + 1)
        End With

to this

'Next empty range in database
        Dim FirstRow As Long
        FirstRow = 1
        With wbDatabase.Sheets(1)
            .Rows(FirstRow).Insert Shift:=xlDown
            Set DatabaseRange = .Cells(FirstRow, 1)
        End With

Change the FirstRow variable value to the row number you want to insert data to.

If this is not what is required then you will need to unload each element of the array to the required ranges.

Dave

 

[Veyron]

Sorry, did not Realize your question was directed at me...

Thanks for your reply Dave.

What you are explaining in the first part is exactly what I want and I believe it is what the code says.

Let me show you with an image.

Source file (table filled):

However, this is what I get in the master workbook when using your code:

Everything in one row.

This is what I would like to get:

I have attached my files just for clarification purposes:

LINK

Thanks for helping

 

[My Aswer Is This]

In your original post you said:

I have 3 workbooks with the same ​

table

​

Are you referring to A actual Table or just a sheet of data in a range?

So all we need to do is copy all the rows of data in the three Work books into The Master Workbook

Is all this data on Sheet(1) of each Workbook?
If not what is the sheet name on these three workbooks and into what sheet on the Master Workbook do you want this data copied into.
​

​

Do all your workbooks always have date in column A?
So we can determine the last row with data by looking in column A
And do you want row(1) form each sheet copied to the Master Workbook?
​

​

​

 

[dmt32]

Hi,
change this line

'output array to database range
        DatabaseRange.Resize(1, InputCellCount).Value = Data

to this

'output array to database range
        DatabaseRange.Resize(DataEntryRange.Rows.Count, DataEntryRange.Columns.Count).Value = Data

and see if this now does what you want

Dave

 

[Veyron]

In your original post you said:

I have 3 workbooks with the same ​

table

​

Are you referring to A actual Table or just a sheet of data in a range?​

Apologies if I am using a wrong terminology, but I would say it is an actual table (is the one you see in the first picture)

So all we need to do is copy all the rows of data in the three Work books into The Master Workbook

Exactly.

All three workbooks are exactly the same workbook but with different name (WB1, WB2, WB3). All of them have the same table but the data provided is different depending on the user choice.
Something that I did not highlighted is that it is not mandatory to fill all rows. Some users can fill only the first 5 rows and some other users can fill the full table. If a row is filled, 1st column (date) will always have information.

Is all this data on Sheet(1) of each Workbook?
If not what is the sheet name on these three workbooks and into what sheet on the Master Workbook do you want this data copied into.

Yes, all data is on sheet(1) in each workbook which is called "matriz". Sheet(1) in master workbook is called "Hoja1"

​

Do all your workbooks always have date in column A? Yes
So we can determine the last row with data by looking in column A
And do you want row(1) form each sheet copied to the Master Workbook?
​

Row 1 is table header: "Date - TL - TL2 -etc"

So it should start copying from row 2 until the last row with data. In other words, if there are only 4 rows with information, only those 4 rows should be copied.


@dmt32, Dave I have tested your solution and now the information is pasted row per row, which is good. However, it only copies the first row 11 times.

An example:

This is what the user introduced (only 4 rows)

And this is what I get in the master workbook:

The information introduced in the first row 11 times.


Again thank you for your time and apologies for my poor explanation. When you see something clearly in your mind sometimes you think people see the same

 

[dmt32]

Hi,

'output array to database range
        DatabaseRange.Resize(DataEntryRange.Rows.Count, DataEntryRange.Columns.Count).Value = DataEntryRange.Value

Overlooked changing last part of code shown in RED


Dave

 

[Veyron]

Hi,

'output array to database range
        DatabaseRange.Resize(DataEntryRange.Rows.Count, DataEntryRange.Columns.Count).Value = DataEntryRange.Value

Overlooked changing last part of code shown in RED


Dave

Working 100%

Thank you VERY much

 

[dmt32]

Working 100%

Thank you VERY much

Hi,
I overlooked that original code was written for collecting data from templates with non-contiguous ranges & posting to single row.

Amendments make some of existing code redundant but does no harm to leave if all now working as required.


Dave