Sub Add_Columns()
imprt.Cells(1, 1).End(xlToRight).Select ' To illustrate what this range refers to
LastColumn = imprt.Cells(1, 1).End(xlToRight).Column
Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 28)).Select ' to illustrate what this range refers to
Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 28)).Value = Array("Cons Code", "Gift Type", "Gift Subtype", "Gift Date", "Gift Campaign", "Gift Fund", "Gift Appeal", "Gift Amount", "Gift Package", "Gift Pay Method", "Gift Card no/exp", "Gift Reference", "Gift Acknowlege", "Gift Letter", "Gift Receipt", "Gift Attr Source Organisation", "Gift Attr Gift Donation Type", "Gift Attr In Memory of:", "Declaration Start Date", "Declaration Made", "Declaration Tax Payer Status", "Declaration Indicator", "Declaration Source", "Motivation_for_Support ID(Individual)", "Email Consent", "Mphone Consent", "SMS Consent", "Gift Attr In Memory of 2")
End Sub
Sub Add_Rows_Data()
LastRow = imprt.Cells(1, 1).End(xlDown).Row
'How to check if a column exists in the sheet, will throw an error if it doesn't exist
Set imprt = ThisWorkbook.Sheets("Sheet5")
LastRow = imprt.Cells(1, 1).End(xlDown).Row
Bank = WorksheetFunction.Match("Gift Bank Name", imprt.Cells(1, 1).EntireRow, 0)
ConsCode = WorksheetFunction.Match("Cons Code", imprt.Cells(1, 1).EntireRow, 0)
GiftType = WorksheetFunction.Match("Gift Type", imprt.Cells(1, 1).EntireRow, 0)
GiftSubType = WorksheetFunction.Match("Gift Subtype", imprt.Cells(1, 1).EntireRow, 0)
GiftDate = WorksheetFunction.Match("Gift Date", imprt.Cells(1, 1).EntireRow, 0)
GiftCampaign = WorksheetFunction.Match("Gift Campaign", imprt.Cells(1, 1).EntireRow, 0)
GiftFund = WorksheetFunction.Match("Gift Fund", imprt.Cells(1, 1).EntireRow, 0)
GiftAppeal = WorksheetFunction.Match("Gift Appeal", imprt.Cells(1, 1).EntireRow, 0)
GiftAmount = WorksheetFunction.Match("Gift Amount", imprt.Cells(1, 1).EntireRow, 0)
GiftPackage = WorksheetFunction.Match("Gift Package", imprt.Cells(1, 1).EntireRow, 0)
GiftPaymethod = WorksheetFunction.Match("Gift Pay method", imprt.Cells(1, 1).EntireRow, 0)
GiftCardNo = WorksheetFunction.Match("Gift Card no/exp", imprt.Cells(1, 1).EntireRow, 0)
GiftReference = WorksheetFunction.Match("Gift Reference", imprt.Cells(1, 1).EntireRow, 0)
GiftKnowledge = WorksheetFunction.Match("Gift Acknowlege", imprt.Cells(1, 1).EntireRow, 0)
GiftLetter = WorksheetFunction.Match("Gift Letter", imprt.Cells(1, 1).EntireRow, 0)
GiftReceipt = WorksheetFunction.Match("Gift Receipt", imprt.Cells(1, 1).EntireRow, 0)
GiftSourceOrganisation = WorksheetFunction.Match("Gift Attr Source Organisation", imprt.Cells(1, 1).EntireRow, 0)
GiftDonationType = WorksheetFunction.Match("Gift Attr Gift Donation Type", imprt.Cells(1, 1).EntireRow, 0)
GiftInMemory = WorksheetFunction.Match("Gift Attr In Memory of:", imprt.Cells(1, 1).EntireRow, 0)
DeclarationStartDate = WorksheetFunction.Match("Declaration Start Date", imprt.Cells(1, 1).EntireRow, 0)
DeclarationMade = WorksheetFunction.Match("Declaration Made", imprt.Cells(1, 1).EntireRow, 0)
DeclarationTaxPayerStatus = WorksheetFunction.Match("Declaration Tax Payer Status", imprt.Cells(1, 1).EntireRow, 0)
DeclarationIndicator = WorksheetFunction.Match("Declaration Indicator", imprt.Cells(1, 1).EntireRow, 0)
DeclarationSource = WorksheetFunction.Match("Declaration Source", imprt.Cells(1, 1).EntireRow, 0)
Motivation = WorksheetFunction.Match("Motivation_for_Support ID(Individual)", imprt.Cells(1, 1).EntireRow, 0)
GiftAttrInMemoryof2 = WorksheetFunction.Match("Gift Attr In Memory of 2", imprt.Cells(1, 1).EntireRow, 0)
CelebrationType = WorksheetFunction.Match("CelebrationType", imprt.Cells(1, 1).EntireRow, 0)
CelebrationTitle = WorksheetFunction.Match("CelebrationTitle", imprt.Cells(1, 1).EntireRow, 0)
CelebrationDate = WorksheetFunction.Match("CelebrationDate", imprt.Cells(1, 1).EntireRow, 0)
GatewayName = WorksheetFunction.Match("GatewayName", imprt.Cells(1, 1).EntireRow, 0)
Instance = WorksheetFunction.Match("Instance", imprt.Cells(1, 1).EntireRow, 0)
OptInSMSNumber = WorksheetFunction.Match("OptInSMSNumber", imprt.Cells(1, 1).EntireRow, 0)
GiftStatus = WorksheetFunction.Match("Gift Status", imprt.Cells(1, 1).EntireRow, 0)
GiftFrequencyOption = WorksheetFunction.Match("Gift Frequency Option", imprt.Cells(1, 1).EntireRow, 0)
GiftDayofMonth = WorksheetFunction.Match("Gift Day of Month", imprt.Cells(1, 1).EntireRow, 0)
GiftScheduleSpacing = WorksheetFunction.Match("Gift Schedule Spacing", imprt.Cells(1, 1).EntireRow, 0)
Motivation = WorksheetFunction.Match("Motivation_for_Support ID (Individual)", imprt.Cells(1, 1).EntireRow, 0)
DateCreated = WorksheetFunction.Match("DateCreated", imprt.Cells(1, 1).EntireRow, 0)
EmailConsent = WorksheetFunction.Match("Email Consent", imprt.Cells(1, 1).EntireRow, 0)
MphoneConsent = WorksheetFunction.Match("Mphone Consent", imprt.Cells(1, 1).EntireRow, 0)
SMSConsent = WorksheetFunction.Match("SMS Consent", imprt.Cells(1, 1).EntireRow, 0)
GiftAid = WorksheetFunction.Match("GiftAid", imprt.Cells(1, 1).EntireRow, 0)
OptInEmail = WorksheetFunction.Match("OptInEmail", imprt.Cells(1, 1).EntireRow, 0)
OptInSMS = WorksheetFunction.Match("OptInSMS", imprt.Cells(1, 1).EntireRow, 0)
OptInPhone = WorksheetFunction.Match("OptInPhone", imprt.Cells(1, 1).EntireRow, 0)
Amount = WorksheetFunction.Match("Amount", imprt.Cells(1, 1).EntireRow, 0)
TransactionID = WorksheetFunction.Match("TransactionID", imprt.Cells(1, 1).EntireRow, 0)
For x = 2 To LastRow
'imprt.Cells(x, Bank).Value = "HSBC"
imprt.Cells(x, ConsCode).Value = "Guy's & St Thomas' Charity Record"
imprt.Cells(x, GiftType).Value = "Cash"
imprt.Cells(x, GiftAmount).Value = Cells(x, Amount).Value
'imprt.Cells(x, GiftDate).Value = Cells(x, DateCreated).Value
imprt.Cells(x, GiftDate).Value = DateValue(Cells(x, DateCreated).Text)
imprt.Cells(x, GiftAppeal).Value = "22UN01"
imprt.Cells(x, GiftCampaign).Value = "DM"
'imprt.Cells(x, GiftFund).Value = "G00504"
imprt.Cells(x, GiftPaymethod).Value = "Credit Card"
'imprt.Cells(x, GiftType).Value = "Recurring Gift"
'imprt.Cells(x, GiftStatus).Value = "Active"
'imprt.Cells(x, GiftFrequencyOption).Value = "Specific day"
'imprt.Cells(x, GiftDayofMonth).Value = "5"
'imprt.Cells(x, GiftScheduleSpacing).Value = "1"
imprt.Cells(x, GiftKnowledge).Value = "Acknowledged"
imprt.Cells(x, GiftLetter).Value = "Electronic Thank You"
imprt.Cells(x, GiftReceipt).Value = "Receipted"
imprt.Cells(x, GiftSourceOrganisation).Value = "Guy's & St Thomas' Trust Charity"
imprt.Cells(x, GiftDonationType).Value = "Philanthropic"
imprt.Cells(x, GiftSubType).Value = "Online Payments"
imprt.Cells(x, DateCreated).Value = DateValue(Cells(x, DateCreated).Text) 'Date format
'imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")
imprt.Cells(x, GiftReference).Value = UCase(Cells(x, GatewayName).Value & "_" & Cells(x, TransactionID).Value) 'Cells(x, TransactionID).Value
imprt.Cells(x, Motivation).Value = "Unknown"
imprt.Cells(x, EmailConsent).Value = "Yes_optin"
imprt.Cells(x, MphoneConsent).Value = "Yes_optin"
imprt.Cells(x, SMSConsent).Value = "No_optin"
'OptInSMSNumber
Imprt.Cells(x, OptInSMSNumber).Value =
'imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")
If Cells(x, CelebrationType).Value <> "" Then
imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")
End If
'Gift Aid Declaration
If UCase(Cells(x, GiftAid)) = "YES" Then
imprt.Cells(x, DeclarationStartDate).Value = "2000/04/06"
imprt.Cells(x, DeclarationMade).Value = Cells(x, DateCreated).Value
imprt.Cells(x, DeclarationTaxPayerStatus).Value = "Active"
imprt.Cells(x, DeclarationIndicator).Value = "Electronic"
imprt.Cells(x, DeclarationSource).Value = "Guy's & St Thomas' Trust Charity"
ElseIf Cells(x, GiftAid) = "Test" Then
imprt.Cells(x, DeclarationStartDate).Value = "2000/04/06"
imprt.Cells(x, DeclarationMade).Value = Cells(x, DateCreated).Value
imprt.Cells(x, DeclarationTaxPayerStatus).Value = "Active"
imprt.Cells(x, DeclarationIndicator).Value = "Electronic"
imprt.Cells(x, DeclarationSource).Value = "ASDF123"
Else
imprt.Cells(x, DeclarationSource).Value = ""
End If
'Donor Email Consent
If (Cells(x, OptInEmail)) = "TRUE" Then
imprt.Cells(x, EmailConsent).Value = "Yes_optin"
Else
imprt.Cells(x, EmailConsent).Value = "No_optin"
End If
'Donor OptInSMS
If (Cells(x, OptInSMS)) = "TRUE" Then
imprt.Cells(x, SMSConsent).Value = "Yes_optin"
Else
imprt.Cells(x, SMSConsent).Value = "No_optin"
End If
'Donor OptinPhone
If (Cells(x, OptInPhone)) = "TRUE" Then
imprt.Cells(x, MphoneConsent).Value = "Yes_optin"
Else
imprt.Cells(x, MphoneConsent).Value = "No_optin"
End If
Next x
End Sub