want to continuously combine 2 worksheets with identical columns

cliffmwsj · Sep 7th 2018

Hello everyone. I've got two separate worksheets "eric's jobs" and "john's jobs" that have identical columns "date awarded", "invoice number", "date work started" etc... New data is added to rows as work proceeds, and new rows are added at the bottom of each of these worksheets as new jobs are obtained. I want to have both of these worksheets combined into a master "all jobs" sheet as new stuff is added. I don't want to have to run a new combine command, but rather want stuff to automatically update.

I've looked into it a bit and found older examples that might work, but not sure whether they are still valid.
https://www.ozgrid.com/forum/f…-summary-master-worksheet
https://www.ozgrid.com/forum/f…worksheets-into-one-sheet

It looks like the second link above is more in line with what i need, but it's over 10 years old. Before i put a bunch of effort in adding this to my workbook, will it still work?

Thanks for your help.
Cliff

vcoolio · Sep 8th 2018

Hello Cliff,

If I've understood you correctly, you would like the main sheet to update as you are typing new information into any cell of the data set for Eric's sheet or John's sheet without having to use a button.
If this is correct, then the following Workbook_SheetChange event code may do the task for you:-

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Intersect(Target, Sh.Columns("A:G")) Is Nothing Then Exit Sub '---->Change the range A:G to suit.
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub


Dim ws As Worksheet: Set ws = Sheets("All Jobs")


Application.ScreenUpdating = False


ws.UsedRange.Offset(1).ClearContents


For Each Sh In Worksheets
    If Sh.Name <> "All Jobs" Then
        If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy
                ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
              End If
        End If
Next Sh


ws.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Display More

If you haven't already, open a new sheet and name it All Jobs, then, to implement this code:-

- Press Alt + F11 to open the VB Editor.
- Over to the left in the Project Explorer, double click on ThisWorkbook.
- In the big white code field, paste the above code.
- Save your workbook with the .xlsm file extension.

Each time that you change a cell or introduce new rows of data in any worksheet (other than All Jobs) the "All Jobs" worksheet will update with the new information.

I've assumed that in each worksheet the data starts in row2 with headings in row1.

You'll need to change the Column range (A:G in the code above) to suit yourself.

I hope that this helps.

Cheerio,
vcoolio.

cliffmwsj · Sep 11th 2018

That looks like it's doing exactly what I need, Thanks!

cliffmwsj · Sep 11th 2018

OK, well...

Some more testing revealed some interesting buggy action. An entry on the "All jobs" worksheet caused the "All jobs" worksheet to be blanked. Data was kept in the other two worksheets. Entering new data on either of the other two worksheets reactivated your code and caused data from those sheets to be copied onto the "All jobs" sheet again, but formatting was very weird some really tall rows with text at the bottom and lots of whitespace...

We shouldn't need the "All jobs" tab to be a place where data can be entered, but if someone using the workbook forgets which sheet they're on, I'd like it to not come back to me with a panicked "all the data disappeared" ticket.

What would really be ideal is if data is entered on a row on the "All jobs" tab, if that data was then synced to the appropriate worksheet (either eric's or john's jobs sheets). I realize that might actually be asking a lot.

Thanks again for all your help.

vcoolio · Sep 12th 2018

Hello Cliff,

Quote

We shouldn't need the "All jobs" tab to be a place where data can be entered

I couldn't agree more as this would defeat the purpose of a summary sheet. So, lets try and keep users out of the "All Jobs" sheet with a pop up warning rather than complicate things. Try the following amended code (in a copy of your workbook first):-

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Intersect(Target, Sh.Columns("A:G")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub


If Sh.Name = "All Jobs" Then
MsgBox "No entries are to be made in this sheet. Please clear any inputs that you have made and go to the correct source sheet to make your entries.", vbExclamation, "WARNING"
Range("A1").Select
Exit Sub
End If


Dim ws As Worksheet: Set ws = Sheets("All Jobs")


Application.ScreenUpdating = False


Sheet1.UsedRange.Offset(1).ClearContents


For Each Sh In Worksheets
        If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        End If
Next Sh


ws.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Display More

They'll only be able to make one single cell entry before being told to clear the entry and go to the relevant source sheet.

I've changed the PasteSpecial xlPasteAll to PasteSpecial xlValues as this may have been the source of the weird formatting. Only values will now be transferred across.

Let me know what you think.

Cheerio,
vcoolio.

vcoolio · Sep 12th 2018

Cliff(Workbook_SheetChange event).xlsm

That was supposed to go below my last post! Anyway....................

cliffmwsj · Sep 18th 2018

Awesome! I really appreciate your help. That all works great. The formatting errors are fixed too. thanks.

One last, i hope, request for this job. It has been determined that in addition to "John's Jobs" and "Eric's Jobs" there will also be a "POs" worksheet in this workbook. (don't ask me why it can't be in a different book) Is there a line of code that can exclude the "POs" sheet from being synced to the overview sheet?

cliffmwsj · Sep 18th 2018

oh, and another issue, when something is deleted on a source sheet, it does not delete on the summary sheet. specifically a cell, I haven't checked a whole row...

for instance, if I put a date of NTP of 9/18 on a job for john, it copies to the summary sheet as it should, but if I then blank that date on john's jobs sheet, the date does not blank on the summary sheet.

vcoolio · Sep 19th 2018

Hello Cliff,

Try the code amended as follows:-

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


If Intersect(Target, Sh.Columns("A:G")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
[COLOR=#FF0000]If Sh.Name = "POs" Then Exit Sub[/COLOR]


If Sh.Name = "All Jobs" Then
MsgBox "No entries are to be made in this sheet. Please clear any inputs that you have made and go to the correct source sheet to make your entries.", vbExclamation, "WARNING"
Range("A1").Select
Exit Sub
End If


Dim ws As Worksheet: Set ws = Sheets("All Jobs")


Application.ScreenUpdating = False


Sheet1.UsedRange.Offset(1).ClearContents


[COLOR=#FF0000]For Each Sh In Worksheets
If Sh.Name <> "POs" Then[/COLOR]
        If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
        ElseIf Target.Value = "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
            End If
       End If
Next Sh


ws.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Display More

Again, test it in a copy of your workbook.

Cheerio,
vcoolio.

reading · Sep 29th 2018

hi, I am trying to make a new project template for my work so when I add information / data into one sheet it can be linked to other sheets so I don’t have to type the same information in different folders or sheet multiple times.
The issue is once i rename the new project template folder all the links are broken. Can anyone help me with this?

any suggestions or examples added would be appreciated

vcoolio · Sep 29th 2018

Hello Reading,

Please start your own thread which fully describes your issue. Give a clear and concise explanation of inputs/expected results and attach a sample of your workbook. Make sure that the sample is an exact replica of your workbook and ensure that you use dummy data in the sample.

Cheerio,
vcoolio.

cliffmwsj · Oct 5th 2018

Vcoolio,

Thanks again for all your help. I have some questions about the code that i hope you'll be able to help me with.

Am i right that the following line limits the effective range of your code to columns A through G?

Code

If Intersect(Target, Sh.Columns("A:G")) Is Nothing Then Exit Sub

If our workbook uses columns all the way to BZ, should i amend that code like this?

Code

If Intersect(Target, Sh.Columns("A:BZ")) Is Nothing Then Exit Sub

Secondly, If I needed to add another worksheet "Bids" that is exempted from the code, should i just add the line below in the appropriate place in the code?

Code

If Sh.Name = "Bids" Then Exit Sub

I see that sheet "POs" is mentioned in another block of code too, but i don't know how to amend it appropriately...

Code

If Sh.Name <> "POs" OR "Bids" Then

I'm sure that's not right.

vcoolio · Oct 6th 2018

Hello Cliff,

Quote

Am i right that the following line limits the effective range of your code to columns A through G?

Correct!

Quote

If our workbook uses columns all the way to BZ, should I amend that code like this?
If Intersect(Target, Sh.Columns("A:BZ")) Is Nothing Then Exit Sub

Correct!

Quote

Secondly, If I needed to add another worksheet "Bids" that is exempted from the code, should i just add the line below in the appropriate place in the code?
If Sh.Name = "Bids" Then Exit Sub

Correct! Just add it after this line:-

Code

[COLOR=#FF0000]If Sh.Name = "POs" Then Exit Sub[/COLOR]

Quote

I see that sheet "POs" is mentioned in another block of code too, but i don't know how to amend it appropriately...

Change this line:-

Quote

If Sh.Name <> "POs" Then

to

Code

If Sh.Name <> "POs" And Sh.Name <> "Bids" Then

Cheerio, vcoolio.

cliffmwsj · Oct 8th 2018

Awesome, thanks.

Now that all that is done, Ive been asked to allow cell colors to copy across to the main page as well. Switching to paste values did fix the formatting weirdness, but disallows the cell shading. Any ideas?

vcoolio · Oct 9th 2018

Hello Cliff,

Try one or the other of the following changes to the code:-

Change these lines of code:-

Code

If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
      ElseIf Target.Value = "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues

to

Code

If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)
      ElseIf Target.Value = "" Then
            Sh.UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)

or change it to this:-

Code

If Target.Value <> "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteFormats
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
      ElseIf Target.Value = "" Then
            Sh.UsedRange.Offset(1).Copy
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteFormats
            ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues

Either one should work for you but test them in a copy of your workbook first and decide which one works best for you.

I hope that this helps.

Cheerio,
vcoolio.

want to continuously combine 2 worksheets with identical columns

Participate now!

Share

Users Viewing This Thread