Copy from multiple sheets but not duplicates

[jolivanes]

In a workbook with possibly up to 40+ sheets I want to copy all values (strings), starting at sheet 4 row 13 to a new sheet that will be called Total. However, the majority of entries from the 5th sheet on are duplicates and I don't want to copy them and there are also empty rows on the sheets. All sheets start in cell A13 and do not go past cell A55 but could go past that in the sheet Total if there are a lot of different entries.
I have tried some of the VBA I found through searching o.a.
http://www.mrexcel.com/forum/showthread.php?t=328407
http://www.mrexcel.com/forum/showthread.php?t=328113
but I am unable to adapt the macros to make it work for me.
Could someone help me with this please.

Thanks and regards.

John

 

[xenou]

When you say duplicates do you mean individual cells are duplicates, entire rows are duplicates, or all the rows on the sheet are duplicates of another sheet?

When you say copy to Totals, do you mean adding the new rows to the bottom ("stacking them")?



Overall, I'd think it would be easiest to copy everything, duplicates and all: deal with the duplicates and blank rows after you have it all on one sheet.

AB

 

[jolivanes]

Alexander Barnes.
Thank you very much for looking into this and helping.
Duplicates is the same string in some of the sheets in cells only
Copy to Totals is in sheet Totals having one of each only. The corresponding cell values
in Column B will be added for each desription (would that be another thread?)
As you say, it might be easier to just copy all sheets into Total but I really don't know
how to proceed and how to finish it so that it works properly and that the answers are
correct.

The sheets are setup like this:
Column A has a desription, Column B an amount, Column C has cost per and Column D is
the multiplication of corresponding cell B times corresponding cell C

Sheetname1 (Starts at row 13)
A13 = Package 1, B13 = 2, C13 = 25, D13 = 50
A14 = Package 2, B14 = 1, C14 = 20, D14 = 20
A15 = Electrician 1, B15 = 1, C15 = 100, D15 = 100
A16 = Electrician 2, B16 = 1, C16 = 95, D16 = 95
A17 = Mileage Electrician 1, B17 = 60, C17 = 1.10, D17 = 66
A18 = Mileage Electrician 2, B18 = 18, C18 = 1.25, D18 = 22.5
A19 = Lunch Electrician 1, B19 = 1, C19 = 15, D19 = 15
A20 = Dinner Electrician 2, B20 = 1, C20 = 25, D20 = 25


Sheetname2 (Starts at row 13)
A13 = Package 1, B13 = 1, C13 = 25, D13 = 25
A14 = Electrician 1, B14 = 2, C14 = 100, D14 = 200
A15 = Mileage Electrician 1, B15 = 90, C15 = 1.10, D15 = 99
A16 = Lunch Electrician 1, B16 = 2, C16 = 15, D16 = 30

Sheetname3 (Starts at row 13)
A13 = Package 2, B13 = 6, C13 = 20, D13 = 120
A14 = Electrician 2, B14 = 1, C14 = 95, D14 = 95
A15 = Dinner Electrician 2, B15 = 4, C15 = 25, D15 = 100


Sheet Total should look like this (Does not have to be in any particular order):
A13 = Package 1, B13 = 3, C13 = 25, D13 = 75
A14 = Package 2, B14 = 7, C14 = 20, D14 = 140
A15 = Electrician 1, B15 = 3, C15 = 100, D15 = 300
A16 = Electrician 2, B16 = 2, C16 = 95, D16 = 190
A17 = Mileage Electrician 1, B17 = 150, C17 = 1.10, D17 = 165
A18 = Mileage Electrician 2, B18 = 18, C18 = 1.25, D18 = 22.5
A19 = Lunch Electrician 1, B19 = 3, C19 = 15, D19 = 45
A20 = Dinner Electrician 2, B20 = 5, C20 = 25, D20 = 125

There might be empty rows anywhere between the first and last entries in the sheets.
There would be a maximum of 40 entries in the individual sheets but the Total sheet could
have considerable more if there are many different entries in the sheets and the amount
of sheets to copy from could be as many as 60.

While working on it, trying all kind of different things that so far have not worked, for the
last days, I now think it is probably easier to have the Total sheet as the 4th sheet and all
the sheets where it takes it information starting as 5th and on.
Sorry for the poor explanation but I don't know how else to explain it.
I would gladly attach the workbook but that is impossible.

Again, thank you very much for helping.

John

 

[xenou]

I haven't fully tested this but give it a whirl.

It will just copy the rows, never going past row 55 and starting with the 5th sheet. I've assumed you have a sheet called "Totals".

**It will clear everything on that totals sheets except the headers in row 1!!

I think what you could do after running this is just sort by column A and subtotal. If you want, you can record that code and post it here - we can clean it up and add it as a final step to this routine.

AB

Sub Test()
Dim ws As Worksheet
Dim wsTotals As Worksheet
Dim LRowTotals As Long
Dim x As Long
Dim LRow As Long

Set wsTotals = Worksheets("Totals") 'Change Name as needed
With wsTotals
    .Range(.Cells(2, 1), .Cells(Rows.Count, Columns.Count)).ClearContents 'Clears everything on the Totals Sheet Except Row 1 headers!
End With

For x = 5 To Worksheets.Count 'Will tally from the fifth sheet and onwards
    Set ws = Worksheets(x)
    
    With ws
        LRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Last Row
        If LRow > 55 Then LRow = 55 'Never greater than row 55
            
        LRowTotals = wsTotals.Cells(Rows.Count, 1).End(xlUp).Row 'Get Next Row on totals sheet
            
        .Range(.Cells(13, 1), .Cells(LRow, 1)).EntireRow.Copy _
            Destination:=wsTotals.Cells(LRowTotals + 1, 1) 'Add rows to totals sheet
    End With

Next x

End Sub

 

[jolivanes]

Alexander Barnes.
Thank you so much. I will try it soon and let you know.
Regards.
John

 

[jolivanes]

Alexander Barnes.
That is very fast. I ended up with 250+ entries, excluding empty rows in no time flat.
I now have to see how I can add the offset cell value of each unique entry and delete all but the first unique entry after it finished adding. That will be a new thread after I work on it for a while.
Thanks again

Regards

John