Hello all-
I have been asked to update my code that I am using to now do the following:

Read data from the tab called 2016 rejects. copy the contents into an existing page that will tabulate the totals from that specific page, AND NOW keep the original data on the input page (called 2016 Rejects).

Thanks in advance for your assistance.

I know that the .clearcontents command will need to be taken off each to leave original input data.

BUT, what code will need to be added, changed, to read the newly input data, and copy it --- NOT the data from before? I have searched and think it will be with my "FOR r= last row of old data +1" will do it. But I am not sure how to store the last line of data read so I know where to pick up with each time.

Here is the code:

Sub Macro2()
Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, lr5 As Long, lr6 As Long, lr7 As Long, lr8 As Long, lr9 As Long, lr10 As Long, lr11 As Long, lr12 As Long, lr13 As Long, lr14 As Long, lr15 As Long, lr16 As Long, lr17 As Long, lr18 As Long, lr19 As Long, lr20 As Long, lr21 As Long, lr22 As Long, lr23 As Long, lr24 As Long, lr25 As Long, lr26 As Long, lr27 As Long, lr28 As Long, lr29 As Long, lr30 As Long, lr31 As Long, lr32 As Long, lr33 As Long, lr34 As Long, lr35 As Long, lr36 As Long, r As Long

lr = Sheets("2016 Rejects").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("0088140").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("0088165").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("0088250").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("0040562-01").Cells(Rows.Count, "A").End(xlUp).Row
lr6 = Sheets("0042298-02").Cells(Rows.Count, "A").End(xlUp).Row
lr7 = Sheets("0042326-01").Cells(Rows.Count, "A").End(xlUp).Row
lr8 = Sheets("0042328-01").Cells(Rows.Count, "A").End(xlUp).Row
lr9 = Sheets("0042335-02").Cells(Rows.Count, "A").End(xlUp).Row
lr10 = Sheets("0050613-01").Cells(Rows.Count, "A").End(xlUp).Row
lr11 = Sheets("0053405-01").Cells(Rows.Count, "A").End(xlUp).Row
lr12 = Sheets("0070885-01").Cells(Rows.Count, "A").End(xlUp).Row
lr13 = Sheets("RV2032").Cells(Rows.Count, "A").End(xlUp).Row
lr14 = Sheets("ICHShippers").Cells(Rows.Count, "A").End(xlUp).Row
lr15 = Sheets("41198").Cells(Rows.Count, "A").End(xlUp).Row
lr16 = Sheets("41280").Cells(Rows.Count, "A").End(xlUp).Row
lr17 = Sheets("41281").Cells(Rows.Count, "A").End(xlUp).Row
lr18 = Sheets("41306").Cells(Rows.Count, "A").End(xlUp).Row
lr19 = Sheets("41352").Cells(Rows.Count, "A").End(xlUp).Row
lr20 = Sheets("11211-SEG").Cells(Rows.Count, "A").End(xlUp).Row
lr21 = Sheets("8307").Cells(Rows.Count, "A").End(xlUp).Row
lr22 = Sheets("23317").Cells(Rows.Count, "A").End(xlUp).Row
lr23 = Sheets("083-065-100").Cells(Rows.Count, "A").End(xlUp).Row
lr24 = Sheets("W02-000-001").Cells(Rows.Count, "A").End(xlUp).Row
lr25 = Sheets("11211-SEG-120").Cells(Rows.Count, "A").End(xlUp).Row
lr26 = Sheets("Staminia BTL").Cells(Rows.Count, "A").End(xlUp).Row
lr27 = Sheets("KF-Xb1-D40113A-B").Cells(Rows.Count, "A").End(xlUp).Row
lr28 = Sheets("KTL-Xboxone-LH").Cells(Rows.Count, "A").End(xlUp).Row
lr29 = Sheets("KTL-Xboxone-RH").Cells(Rows.Count, "A").End(xlUp).Row
lr30 = Sheets("KTL-PS4-Tall").Cells(Rows.Count, "A").End(xlUp).Row
lr31 = Sheets("KTL-PS4-Short").Cells(Rows.Count, "A").End(xlUp).Row
lr32 = Sheets("1582").Cells(Rows.Count, "A").End(xlUp).Row
lr33 = Sheets("0075688-01").Cells(Rows.Count, "A").End(xlUp).Row
lr34 = Sheets("Sheet34").Cells(Rows.Count, "A").End(xlUp).Row
lr35 = Sheets("Sheet35").Cells(Rows.Count, "A").End(xlUp).Row
lr36 = Sheets("Sheet36").Cells(Rows.Count, "A").End(xlUp).Row

For r = 2 To lr

Select Case Range("D" & r).Value
Case Is = "0088140"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0088140").Range("A" & lr2 + 1)
lr2 = Sheets("0088140").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0088165"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0088165").Range("A" & lr3 + 1)
lr3 = Sheets("0088165").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0088250"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0088250").Range("A" & lr4 + 1)
lr4 = Sheets("0088250").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0040562-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0040562-01").Range("A" & lr5 + 1)
lr5 = Sheets("0040562-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0042298-02"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0042298-02").Range("A" & lr6 + 1)
lr6 = Sheets("0042298-02").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0042326-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0042326-01").Range("A" & lr7 + 1)
lr7 = Sheets("0042326-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0042328-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0042328-01").Range("A" & lr8 + 1)
lr8 = Sheets("0042328-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0042335-02"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0042335-02").Range("A" & lr9 + 1)
lr9 = Sheets("0042335-02").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0050613-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0050613-01").Range("A" & lr10 + 1)
lr10 = Sheets("0050613-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0053405-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0053405-01").Range("A" & lr11 + 1)
lr11 = Sheets("0053405-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "0070885-01"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("0070885-01").Range("A" & lr12 + 1)
lr12 = Sheets("0070885-01").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "RV2032"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("RV2032").Range("A" & lr13 + 1)
lr13 = Sheets("RV2032").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "ICHShippers"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("ICHShippers").Range("A" & lr14 + 1)
lr14 = Sheets("ICHShippers").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "41198"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("41198").Range("A" & lr15 + 1)
lr15 = Sheets("41198").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "41280"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("41280").Range("A" & lr16 + 1)
lr16 = Sheets("41280").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "41281"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("41281").Range("A" & lr17 + 1)
lr17 = Sheets("41281").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "41306"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("41306").Range("A" & lr18 + 1)
lr18 = Sheets("41306").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "41352"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("41352").Range("A" & lr19 + 1)
lr19 = Sheets("41352").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 Rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents

Case Is = "11211-SEG"
Range(Cells(r, 1), Cells(r, 14)).Copy Destination:=Sheets("11211-SEG").Range("A" & lr20 + 1)
lr20 = Sheets("11211-SEG").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("2016 rejects").Range(CStr(r) & ":" & CStr(r)).SpecialCells(xlCellTypeConstants).ClearContents


End Select
Next r
End Sub