Update data from one sheet to another sheet with header mapping

[lkumar]

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

[Bob Phillips]

I am not sure what exactly you are looking to achieve. It looked to me that you wanted to copy the data from mapping to destination, but in a different column order, but the code in the attached workbook does not reference any sheets called mapping or destination.


Does your code work and you want it modified in some way, or are you looking for another solution?

[lkumar]

Thanks for your response.

The above code is not working properly and looking for another solution .

I need to populate value as per below table headers mapping where in mapping sheet Header will start from row # 15 and in Destination sheet Header will start from row #1.



Mapping Sheet Header
Destination sheet Header


Policy No.
Unique ID


Month
USERTXT1


Year
USERTXT2


OLD/NEW
Talbot ID


Sheet Name
USERID2


Tab Name
EQSITEDED


Section #
Top_20_Locations


New or Renewal
EQCOMBINEDDED


Endorsement Notes
WSSITEDED


Endorsement Effective Date
WSCOMBINEDDED


Building Number
TOSITEDED


Country
TOCOMBINEDDED


Policy Number
FLSITEDED


Assured
FLCOMBINEDDED


Account ID
FRSITEDED


Inception Date
FRCOMBINEDDED


Expiry Date
ACCNTNUM


Premium (100%)
LOCNUM


Participation (%)
LOCNAME


Currency Code
Schedule of Locations


AOP Limit
ADDRESSNUM


AOP Excess
STREETNAME


AOP DD $
Address


EQ Limit
City #


EQ Excess
County#


EQ DD $
State#


WS Limt
ZIP#


WS Excess
POSTALCODE


WS DD $
CITYCODE


TO Limt
CITY


TO Excess
COUNTYCODE


TO DD $
COUNTY


FL Limit
STATECODE


FL Excess
STATE


FL DD $
CRESTA


TR Limit
CNTRYSCHEME


TR Excess
CNTRYCODE


TR DD $
COUNTRYNAME


Min DD $
BLDGSCHEME


Max DD $
Construction Description


Notes
BLDGSCHEME_Ref


BLANLIM AMT
BLDGCLASS_Ref


PARTOF
Constr_Exception


UNDCOV AMT
Construction_Identifier


BLANDED AMT
BLDGCLASS


MINDED AMT
YEARBUILT


MAXDED AMT
YEARUPGRAD