Using Multiple Drop down List to populate Multiple Ranges

Hello All,

With Help of several here i have the following code Working Great.

If possible, i would like to eliminate several VLookup formula's i have and go with VBA code so i dont have to go in and Change Multiple formulas anymore.

I have Drop down boxes from "B2:B37" that populate "D:R" of respective lines
.. Even lines look at "Combined Data" Tab
.. Odd Lines look at "Offset" Tab


Ive tried and can get it to copy over the format "boarder's only" but not the Data itself.

Is there a way to use the Copy & paste part of this code multiple times?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim lr As Long
Application.EnableEvents = False
If Not Intersect(Target, Range("B42")) Is Nothing Then
lr = Sheets("Main Gear").Columns("B:R").Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row + 1
If lr < 44 Then lr = 44
Sheets("Main Gear").Range("B44:R" & lr).Clear
    With Sheets("Combined Data")
        .AutoFilterMode = False
        With .Range("A1").CurrentRegion
            .AutoFilter Field:=3, Criteria1:=Sheets("Main Gear").Range("B42").Value
            .Offset(1).Columns("A:A").Copy Destination:=Sheets("Main Gear").Range("B44")
            .Offset(1).Columns("B:P").Copy Destination:=Sheets("Main Gear").Range("D44")
        End With
        .AutoFilterMode = False
    End With
End If
Application.EnableEvents = True
End Sub

Thanks Again
Aeneren