Multiple Workbooks Merging into 1 Spreadsheet

[NFIREPROTECTION]

Good Morning All,

My company is working on upgrading their system for tracking their change orders. We have it currently being tracked on paper and then input into a spreadsheet.


We need all the data that goes into the tabs RS, DW, and GM TO automatically input into the Sheet1 spreadsheet with no spaces between the lines of data.
Is this possible? Is there a way to do it? or Is there an easier way to make this happen?

Any Info you can give would be greatly appreciated!

 

[Comfy]

Also, are they likely to amend lines (If so a VBA solution could get quite messy)?

For Power Query option:


Do the following for each of the sheets (RS, DW and GM).

Select the data and ensure that it is an Excel Table (Insert Tab > Table), give each table a better name (tRS, tDW, tGM)

Make sure that all the column headers are the same (same number and same name)

Save the workbook.

Create another workbook.

In the PowerQuery menu (unfortunately I only have 2010 so mine will be different) Get External Data > From File > From Excel

Select your Source Workbook (IT doesn't have to be closed).

In the Navigator tick the "Select multiple items", select the three tables we named earlier and press edit.

With the first tables active (probably tDW as alphabetic) change the Query name to something more appropriate ("Merged_Tables")

Within the Home Tab there should be a "Combine" Section, click "Append Queries"

Tick the "Three or more tables" radio button and make sure all three tables are in the right box, "Tables to Append", click OK

Then Click "Close and Load To" and choose "Only Create Connection"

Hopefully, you will have a "Workbook Queries" Pane.

Right click the "Merged_Tables" Query and Select "Load To"

Select Table and Choose the location.


-----------------------------------

You can now do several things but have a play to see what would be best.

You could goto the Data Tab > Connections > Connections and edit the properties of the "Merged_Tables" Query to refesh every 1/5/10/15 minutes etc....

Or you could add some VBA to refresh the connection everytime the sheet is selected.


Somethings to Note:

Data will only be returned once the source workbook is saved.
Only users with Power Query will be able to update the Workbook that contains these queries.

 

[NFIREPROTECTION]

OK, so try out the following steps in a COPY of your workbook.

1. Make sure that you have run my original code from yesterday to initially populate Sheet1.

2. Copy and paste this code into any standard module within your workbook in the VBA Developer window:



3. Once you have that in place then for each sheet (RS, DW and GM) right-click on their tab name in the main workbook and select View Code. In the new window that opens just copy and paste the following code into each of them:


The above codes will check as you update one of the 3 sheet. If you are updating column E of the first blank row on any of those sheets it checks to make sure you have completed the rest of the range A:E of that row and gives the user an error message, otherwise if range A:E of that row is complete then it copies the data over to the next row of Sheet1, then calls the PlaceBorders macro to draw the borders around the new entry on Sheet1. The same Worksheet_Change can be inserted into each worksheet module without needing to be amended.

Test it out and let me know how you get on.

Also, are they likely to amend lines (If so a VBA solution could get quite messy)?


For Power Query option:




Do the following for each of the sheets (RS, DW and GM).


Select the data and ensure that it is an Excel Table (Insert Tab > Table), give each table a better name (tRS, tDW, tGM)


Make sure that all the column headers are the same (same number and same name)


Save the workbook.


Create another workbook.


In the PowerQuery menu (unfortunately I only have 2010 so mine will be different) Get External Data > From File > From Excel


Select your Source Workbook (IT doesn't have to be closed).


In the Navigator tick the "Select multiple items", select the three tables we named earlier and press edit.


With the first tables active (probably tDW as alphabetic) change the Query name to something more appropriate ("Merged_Tables")


Within the Home Tab there should be a "Combine" Section, click "Append Queries"


Tick the "Three or more tables" radio button and make sure all three tables are in the right box, "Tables to Append", click OK


Then Click "Close and Load To" and choose "Only Create Connection"


Hopefully, you will have a "Workbook Queries" Pane.


Right click the "Merged_Tables" Query and Select "Load To"


Select Table and Choose the location.




-----------------------------------


You can now do several things but have a play to see what would be best.


You could goto the Data Tab > Connections > Connections and edit the properties of the "Merged_Tables" Query to refesh every 1/5/10/15 minutes etc....


Or you could add some VBA to refresh the connection everytime the sheet is selected.




Somethings to Note:


Data will only be returned once the source workbook is saved.
Only users with Power Query will be able to update the Workbook that contains these queries.

FISHBOY & COMFY: I cannot begin to thank the both of you enough! I was able to get both your methods to work, and will be testing them both out to see which one will work best for us. I believe we will be using both methods regardless, we believe that we could find these methods valuable for other spreadsheets we use within our company. I could not have done this without the perfect walkthroughs I was given. Fingers crossed but I believe we have solved this issue. If I find I have any questions or issues after testing I will try and ask before the week is up. Once again, thank you very much.

If you have any further questions or comments about the results or anything, please let me know.

 

[Fishboy]

FISHBOY & COMFY: I cannot begin to thank the both of you enough! I was able to get both your methods to work, and will be testing them both out to see which one will work best for us. I believe we will be using both methods regardless, we believe that we could find these methods valuable for other spreadsheets we use within our company. I could not have done this without the perfect walkthroughs I was given. Fingers crossed but I believe we have solved this issue. If I find I have any questions or issues after testing I will try and ask before the week is up. Once again, thank you very much.

If you have any further questions or comments about the results or anything, please let me know.

Happy to help!

 

[NFIREPROTECTION]

Happy to help!

FISHBOY:

If you happen to see this, there was a slight change with out spreadsheet. I tried tweaking the code but am having no luck.

The columns are now:
EST / DESIGNER / PROJECT / DATE RECEIVED / DATE SENT / DESCRIPTION / NOTES / FOLLOW UP

I need the EST / PROJECT / DATE SENT / DESCRIPTION / NOTES to transfer over to the sheet 1 spreadsheet.

This is how i made the code look, but i am getting an error saying data is missing:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long, LastRow2 As Long, Rng As Range




If Target.Cells.Count > 1 Then Exit Sub




LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row




If Not Intersect(Target, Range("H2:H" & LastRow)) Is Nothing And Target.Value <> "" Then
If Application.WorksheetFunction.CountIf(Range("A" & Target.Row, "H" & Target.Row), "<>") = 5 Then
LastRow2 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LastRow, "H" & LastRow).Copy
Range("A" & Target.Row).Copy Sheets("Sheet1").Range("B" & LastRow2)
Range("B" & Target.Row).Copy Sheets("Sheet1").Range("A" & LastRow2)
Range("D" & Target.Row).Copy Sheets("Sheet1").Range("D" & LastRow2)
Range("F" & Target.Row).Copy Sheets("Sheet1").Range("F" & LastRow2)
Range("G" & Target.Row).Copy Sheets("Sheet1").Range("E" & LastRow2)
Range("H" & Target.Row).Copy Sheets("Sheet1").Range("K" & LastRow2)
Call PlaceBorders
MsgBox "Sheet1 Updated"
Else
MsgBox "There is missing data on the row you have just updated"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If




End Sub


I do not know much about VBN but am trying to learn and understand it better, thank you for your time!

 

[Fishboy]

FISHBOY:

If you happen to see this, there was a slight change with out spreadsheet. I tried tweaking the code but am having no luck.

The columns are now:
EST / DESIGNER / PROJECT / DATE RECEIVED / DATE SENT / DESCRIPTION / NOTES / FOLLOW UP

I need the EST / PROJECT / DATE SENT / DESCRIPTION / NOTES to transfer over to the sheet 1 spreadsheet.

This is how i made the code look, but i am getting an error saying data is missing:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long, LastRow2 As Long, Rng As Range




If Target.Cells.Count > 1 Then Exit Sub




LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row




If Not Intersect(Target, Range("H2:H" & LastRow)) Is Nothing And Target.Value <> "" Then
If Application.WorksheetFunction.CountIf(Range("A" & Target.Row, "H" & Target.Row), "<>") = 5 Then
LastRow2 = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LastRow, "H" & LastRow).Copy
Range("A" & Target.Row).Copy Sheets("Sheet1").Range("B" & LastRow2)
Range("B" & Target.Row).Copy Sheets("Sheet1").Range("A" & LastRow2)
Range("D" & Target.Row).Copy Sheets("Sheet1").Range("D" & LastRow2)
Range("F" & Target.Row).Copy Sheets("Sheet1").Range("F" & LastRow2)
Range("G" & Target.Row).Copy Sheets("Sheet1").Range("E" & LastRow2)
Range("H" & Target.Row).Copy Sheets("Sheet1").Range("K" & LastRow2)
Call PlaceBorders
MsgBox "Sheet1 Updated"
Else
MsgBox "There is missing data on the row you have just updated"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End If




End Sub


I do not know much about VBN but am trying to learn and understand it better, thank you for your time!

Hi mate,

I have dropped you a private message. Check your inbox and I will see if I can help.