lkumar
06-26-2019, 06:18 AM
Hi All
I need help urgently on VBA coding to fill a sheet based on column mapping.
I have two sheets Mapping and Destination with different fields. Mapping sheet has data (Header starts from row #15) and update into destination sheet with fields mapping (different header name (header start from row #1)).
Is there any way to customize this below code through loop/LBound/UBound:
Dim wb1sh2 As Worksheet, wb1sh1 As Worksheet
Dim i As Long, j As Variant
Dim lRowP As Long
Application.ScreenUpdating = False
'destination Fields in match function
Set wb1 = ActiveWorkbook
For Each sheet In wb1.Worksheets
If sheet.Name = "Mapping" Then
Set wb1sh1 = wb1.Sheets("Mapping")
ret = True
Exit For
End If
Next
If ret = False Then
MsgBox ("'Sheet1' sheets not found in source file.")
End If
For Each sheet In wb1.Worksheets
If sheet.Name = "Destination" Then
Set wb1sh2 = wb1.Sheets("Destination")
met = True
Exit For
End If
Next
If met = False Then
MsgBox ("Sheet2' sheets not found in source file.")
End If
lRowP = wb1sh1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 16
With Application.WorksheetFunction
a1 = .Match("Policy No.", wb1sh2.Rows(15), 0)
a2 = .Match("Month", wb1sh2.Rows(15), 0)
a3 = .Match("Year", wb1sh2.Rows(15), 0)
a5 = .Match("Policy", wb1sh2.Rows(15), 0)
a6 = .Match("Assured #", wb1sh2.Rows(15), 0)
a7 = .Match("AccountID", wb1sh2.Rows(15), 0)
a8 = .Match("InceptionDate", wb1sh2.Rows(15), 0)
a9 = .Match("ExpiryDate", wb1sh2.Rows(15), 0)
a10 = .Match("Premium", wb1sh2.Rows(15), 0)
a11 = .Match("Participation", wb1sh2.Rows(15), 0)
a12 = .Match("Currency", wb1sh2.Rows(15), 0)
a13 = .Match("AOP Limit", wb1sh2.Rows(15), 0)
a14 = .Match("AOP Excess", wb1sh2.Rows(15), 0)
a15 = .Match("AOP DD $", wb1sh2.Rows(15), 0)
a16 = .Match("Min DD $", wb1sh2.Rows(15), 0)
a17 = .Match("Max DD $", wb1sh2.Rows(15), 0)
a18 = .Match("EQ Limit", wb1sh2.Rows(15), 0)
a19 = .Match("EQ Excess", wb1sh2.Rows(15), 0)
End With
With Application.WorksheetFunction
b1 = .Match("Policy", wb1sh1.Rows(15), 0)
b2 = .Match("Month", wb1sh1.Rows(15), 0)
b3 = .Match("Year", wb1sh1.Rows(15), 0)
b4 = .Match("OLD/NEW", wb1sh1.Rows(15), 0)
b5 = .Match("Policy Number", wb1sh1.Rows(15), 0)
b6 = .Match("Assured", wb1sh1.Rows(15), 0)
b7 = .Match("Account ID", wb1sh1.Rows(15), 0)
b8 = .Match("Inception Date", wb1sh1.Rows(15), 0)
b9 = .Match("Expiry Date", wb1sh1.Rows(15), 0)
b10 = .Match("Premium (100%)", wb1sh1.Rows(15), 0)
b11 = .Match("Participation (%)", wb1sh1.Rows(15), 0)
b12 = .Match("Currency Code", wb1sh1.Rows(15), 0)
b13 = .Match("AOP Limit", wb1sh1.Rows(15), 0)
b14 = .Match("AOP Excess", wb1sh1.Rows(15), 0)
b15 = .Match("AOP DD $", wb1sh1.Rows(15), 0)
b16 = .Match("Min DD $", wb1sh1.Rows(15), 0)
b17 = .Match("Max DD $", wb1sh1.Rows(15), 0)
b18 = .Match("EQ Limit", wb1sh1.Rows(15), 0)
b19 = .Match("EQ Excess", wb1sh1.Rows(15), 0)
b20 = .Match("EQ DD $", wb1sh1.Rows(15), 0)
End With
With wb1sh2
For i = 16 To lRowP
wb1sh2.Cells(i, a1).Value = wb1sh1.Cells(i, b1).Value
wb1sh2.Cells(i, a2).Value = wb1sh1.Cells(i, b2).Value
wb1sh2.Cells(i, a3).Value = wb1sh1.Cells(i, b3).Value
wb1sh2.Cells(i, a5).Value = wb1sh1.Cells(i, b5).Value
wb1sh2.Cells(i, a6).Value = wb1sh1.Cells(i, b6).Value
wb1sh2.Cells(i, a7).Value = wb1sh1.Cells(i, b7).Value
wb1sh2.Cells(i, a8).Value = wb1sh1.Cells(i, b8).Value
wb1sh2.Cells(i, a9).Value = wb1sh1.Cells(i, b9).Value
wb1sh2.Cells(i, a10).Value = wb1sh1.Cells(i, b10).Value
wb1sh2.Cells(i, a11).Value = wb1sh1.Cells(i, b11).Value
wb1sh2.Cells(i, a12).Value = wb1sh1.Cells(i, b12).Value
wb1sh2.Cells(i, a13).Value = wb1sh1.Cells(i, b13).Value
wb1sh2.Cells(i, a14).Value = wb1sh1.Cells(i, b14).Value
wb1sh2.Cells(i, a15).Value = wb1sh1.Cells(i, b15).Value
wb1sh2.Cells(i, a16).Value = wb1sh1.Cells(i, b16).Value
wb1sh2.Cells(i, a17).Value = wb1sh1.Cells(i, b17).Value
wb1sh2.Cells(i, a18).Value = wb1sh1.Cells(i, b18).Value
wb1sh2.Cells(i, a19).Value = wb1sh1.Cells(i, b19).Value
wb1sh2.Cells(i, a20).Value = wb1sh1.Cells(i, b20).Value
Next i
End With
I need help urgently on VBA coding to fill a sheet based on column mapping.
I have two sheets Mapping and Destination with different fields. Mapping sheet has data (Header starts from row #15) and update into destination sheet with fields mapping (different header name (header start from row #1)).
Is there any way to customize this below code through loop/LBound/UBound:
Dim wb1sh2 As Worksheet, wb1sh1 As Worksheet
Dim i As Long, j As Variant
Dim lRowP As Long
Application.ScreenUpdating = False
'destination Fields in match function
Set wb1 = ActiveWorkbook
For Each sheet In wb1.Worksheets
If sheet.Name = "Mapping" Then
Set wb1sh1 = wb1.Sheets("Mapping")
ret = True
Exit For
End If
Next
If ret = False Then
MsgBox ("'Sheet1' sheets not found in source file.")
End If
For Each sheet In wb1.Worksheets
If sheet.Name = "Destination" Then
Set wb1sh2 = wb1.Sheets("Destination")
met = True
Exit For
End If
Next
If met = False Then
MsgBox ("Sheet2' sheets not found in source file.")
End If
lRowP = wb1sh1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 16
With Application.WorksheetFunction
a1 = .Match("Policy No.", wb1sh2.Rows(15), 0)
a2 = .Match("Month", wb1sh2.Rows(15), 0)
a3 = .Match("Year", wb1sh2.Rows(15), 0)
a5 = .Match("Policy", wb1sh2.Rows(15), 0)
a6 = .Match("Assured #", wb1sh2.Rows(15), 0)
a7 = .Match("AccountID", wb1sh2.Rows(15), 0)
a8 = .Match("InceptionDate", wb1sh2.Rows(15), 0)
a9 = .Match("ExpiryDate", wb1sh2.Rows(15), 0)
a10 = .Match("Premium", wb1sh2.Rows(15), 0)
a11 = .Match("Participation", wb1sh2.Rows(15), 0)
a12 = .Match("Currency", wb1sh2.Rows(15), 0)
a13 = .Match("AOP Limit", wb1sh2.Rows(15), 0)
a14 = .Match("AOP Excess", wb1sh2.Rows(15), 0)
a15 = .Match("AOP DD $", wb1sh2.Rows(15), 0)
a16 = .Match("Min DD $", wb1sh2.Rows(15), 0)
a17 = .Match("Max DD $", wb1sh2.Rows(15), 0)
a18 = .Match("EQ Limit", wb1sh2.Rows(15), 0)
a19 = .Match("EQ Excess", wb1sh2.Rows(15), 0)
End With
With Application.WorksheetFunction
b1 = .Match("Policy", wb1sh1.Rows(15), 0)
b2 = .Match("Month", wb1sh1.Rows(15), 0)
b3 = .Match("Year", wb1sh1.Rows(15), 0)
b4 = .Match("OLD/NEW", wb1sh1.Rows(15), 0)
b5 = .Match("Policy Number", wb1sh1.Rows(15), 0)
b6 = .Match("Assured", wb1sh1.Rows(15), 0)
b7 = .Match("Account ID", wb1sh1.Rows(15), 0)
b8 = .Match("Inception Date", wb1sh1.Rows(15), 0)
b9 = .Match("Expiry Date", wb1sh1.Rows(15), 0)
b10 = .Match("Premium (100%)", wb1sh1.Rows(15), 0)
b11 = .Match("Participation (%)", wb1sh1.Rows(15), 0)
b12 = .Match("Currency Code", wb1sh1.Rows(15), 0)
b13 = .Match("AOP Limit", wb1sh1.Rows(15), 0)
b14 = .Match("AOP Excess", wb1sh1.Rows(15), 0)
b15 = .Match("AOP DD $", wb1sh1.Rows(15), 0)
b16 = .Match("Min DD $", wb1sh1.Rows(15), 0)
b17 = .Match("Max DD $", wb1sh1.Rows(15), 0)
b18 = .Match("EQ Limit", wb1sh1.Rows(15), 0)
b19 = .Match("EQ Excess", wb1sh1.Rows(15), 0)
b20 = .Match("EQ DD $", wb1sh1.Rows(15), 0)
End With
With wb1sh2
For i = 16 To lRowP
wb1sh2.Cells(i, a1).Value = wb1sh1.Cells(i, b1).Value
wb1sh2.Cells(i, a2).Value = wb1sh1.Cells(i, b2).Value
wb1sh2.Cells(i, a3).Value = wb1sh1.Cells(i, b3).Value
wb1sh2.Cells(i, a5).Value = wb1sh1.Cells(i, b5).Value
wb1sh2.Cells(i, a6).Value = wb1sh1.Cells(i, b6).Value
wb1sh2.Cells(i, a7).Value = wb1sh1.Cells(i, b7).Value
wb1sh2.Cells(i, a8).Value = wb1sh1.Cells(i, b8).Value
wb1sh2.Cells(i, a9).Value = wb1sh1.Cells(i, b9).Value
wb1sh2.Cells(i, a10).Value = wb1sh1.Cells(i, b10).Value
wb1sh2.Cells(i, a11).Value = wb1sh1.Cells(i, b11).Value
wb1sh2.Cells(i, a12).Value = wb1sh1.Cells(i, b12).Value
wb1sh2.Cells(i, a13).Value = wb1sh1.Cells(i, b13).Value
wb1sh2.Cells(i, a14).Value = wb1sh1.Cells(i, b14).Value
wb1sh2.Cells(i, a15).Value = wb1sh1.Cells(i, b15).Value
wb1sh2.Cells(i, a16).Value = wb1sh1.Cells(i, b16).Value
wb1sh2.Cells(i, a17).Value = wb1sh1.Cells(i, b17).Value
wb1sh2.Cells(i, a18).Value = wb1sh1.Cells(i, b18).Value
wb1sh2.Cells(i, a19).Value = wb1sh1.Cells(i, b19).Value
wb1sh2.Cells(i, a20).Value = wb1sh1.Cells(i, b20).Value
Next i
End With