Need Some Finesse Combining Sheets

[DJAnthonyMark]

So I got assigned the project of the year for the company. I need to combine about 15 years of inventory into a searchable database so that I can import it into our new software.

I managed to normalize the data from its multiple "index cards" to lists on worksheets. Now I need to combine those worksheets into one and than all of them into one complete list.

Here is the the Macro I currently use to combine sheets. I've used this before to combine multiple inventories for importing. It combines fine and then I manually cleanup the data little to make it look good. The problem is, I don't know how to make the Macro independent of each sheet name, this way it runs on all sheets, regardless of the name. Also, the data needs to be combined using a paste values, because of the way the functions were setup, i don't want the formulas to carry over.

Can someone help me? Where am I going wrong?
==========================

Sub Collate_Sheets()

'Put Data in NEW Combined Inventory
Sheets.Add After:=Sheets(Sheets.Count)
Dim wks As Worksheet
Set wks = Sheets(Sheets.Count)
wks.Name = "CombinedInventory"

'Grabbing CDA911.250DROPSPBXXT
With Sheets("CDA911.250DROPSPBXXT")
Dim lastrow As Long
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A1:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp)
End With

'Grabbing CDA911.375DROPSPBXXT
With Sheets("CDA911.375DROPSPBXXT")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A2:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With

'CDA911.500DROPSPBXXT
With Sheets("CDA911.500DROPSPBXXT")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A2:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With


'CDA911.625THICKDROPPBXXT
With Sheets("CDA911.625THICKDROPPBXXT")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A2:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With


'CDA911.750DROPSPBXXT
With Sheets("CDA911.750DROPSPBXXT")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A2:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With


'CDA911.875DROPSPBXXT
With Sheets("CDA911.875DROPSPBXXT")
lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A2:ET" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
End With

End Sub