VBA ERROR- Procedure too large

Th_B

New Member
Joined
Jul 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I am new to here. I got an error when I run my macro saying that " procedure too large". My code is very lengthy. Can you help me to solve this issue.
This code is to copy excel data from a table and fill it into pdf form. Each row data will go to a single pdf form then save and reset. Then go to the next ROW, and do the same.

Here is the link for the code


Thanks in advance.

regards
Thusha
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

This could be an option.
Move a part of your code to another module.

Changes in your code:
- Remove the With reference.
- Select your sheet.
- Replace all your .Range code with Range

VBA Code:
Sub test1()
  Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName, FirstName As String
  Dim SignedDate As Date
  Dim CustRow, Lastrow As Long
  Dim Sheet11 As Worksheet
  Dim strFilePath As String
  
  Sheets("VBA-21-686c-ARE").Select
  Lastrow = Range("A9999").End(xlUp).Row             'Define Last row
  
  PDFTemplateFile = Range("E2").Value          'Template file Name
  SavePDFFolder = Range("E3").Value                  'Save PDF Folder
  
  strFilePath = "C:\Users\Star\Desktop\VA Forms\VBA-21-686c-ARE.PDF"
  ThisWorkbook.FollowHyperlink (strFilePath)
  
  Application.Wait Now + 0.00006                      ' wait 6 seconds
  
  For CustRow = 5 To 5 'Lastrow
    LastName = Range("A" & CustRow).Value              'Last name
    FirstName = Range("B" & CustRow).Value             'Fisrt name
    
    Call block1(CustRow)
    '
    '-------------------------------------
    
    '---15A.1 TO WHOM MARRIED--
    Application.SendKeys Range("DS" & CustRow).Value, True   '15A.1 TO WHOM MARRIED Last NAME
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True
    
    Application.SendKeys Range("DR" & CustRow).Value, True   '15A.1 TO WHOM MARRIED Middle NAME
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True
    
    Application.SendKeys Range("DQ" & CustRow).Value, True   '15A.1 TO WHOM MARRIED First NAME
    Application.Wait Now + 0.00001
    Application.SendKeys "{Tab}", True
    '----------------------------
    '-----------15A2---------

'Here continues your code

In other module:

VBA Code:
Sub block1(CustRow)
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("I" & CustRow).Value, True   '4.3 Date of Birth Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("H" & CustRow).Value, True   '4.2 Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("G" & CustRow).Value, True   '4.1 Date of Birth Month
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("F" & CustRow).Value, True   ' 3. VA file number
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("A" & CustRow).Value, True   '1.2 Lastname
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("B" & CustRow).Value, True   '1.2 Firstname
  Application.Wait Now + 0.00001
'
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("X" & CustRow).Value, True   '10.4 CurrentMailingAddress_ZIPOrPostalCode_FirstFiveNumbers[0]
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("W" & CustRow).Value, True   '10.3 CurrentMailingAddress_StateOrProvince[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("V" & CustRow).Value, True   '10.2 Currentcity[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("U" & CustRow).Value, True   '10.1 CurrentMailingAddress_NumberAndStreet[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AA" & CustRow).Value, True   '11A.3 CLAIMANT'S Last NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Z" & CustRow).Value, True   '11A.2 CLAIMANT'S Middle NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Y" & CustRow).Value, True   '11A.1  CLAIMANT'S First NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("L" & CustRow).Value, True   '5.3 CLAIMANT'S Last NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("K" & CustRow).Value, True   '5.2 CLAIMANT'S Middle NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("J" & CustRow).Value, True   '5.1  CLAIMANT'S First NAME
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("O" & CustRow).Value, True   '6.3.SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("N" & CustRow).Value, True   '6.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("M" & CustRow).Value, True   '6.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("P" & CustRow).Value, True   ' 5.Service Number
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AD" & CustRow).Value, True   '11B. Spouse's Date of Birth Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AC" & CustRow).Value, True   '11B. Spouse's Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AB" & CustRow).Value, True   '11B. Spouse's Date of Birth Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AG" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AF" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AE" & CustRow).Value, True   '11C. Spouce's SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AJ" & CustRow).Value, True   '11D. Date of Marriage Year
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AI" & CustRow).Value, True   '11D. Date of Marriage Day
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AH" & CustRow).Value, True   '11D. Date of Marriage Month
  Application.Wait Now + 0.00001
'
  Application.SendKeys "{Tab}", True
  Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "OTHER" Then     '11.F "Other"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "TRIBAL" Then     'TRIBAL
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "PROXY" Then     'PROXY
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "COMMON LAW" Then     'COMMON LAW
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
       Application.Wait Now + 0.00002
      If Range("AN" & CustRow).Value = "RELIGIOUS CEREMONY" Then     'RELIGIOUS CEREMONY
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
          
      
      
  '12A---------------------------------------------------
     Application.Wait Now + 0.00002
      If Range("AP" & CustRow).Value = "NO" Then     '
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{~}"
              Application.Wait Now + 0.00002
              Application.SendKeys "{Tab}", True
      End If
  
  '----------------------------------
  'Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AT" & CustRow).Value, True   '12C. SPOUSE'S SERVICE NUMBE
  Application.Wait Now + 0.00001
   Application.SendKeys "{Tab}", True
  '13A-------------------------------------
   Application.Wait Now + 0.00002
      If Range("AU" & CustRow).Value = "NO" Then     '
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{~}"
              Application.Wait Now + 0.00002
              Application.SendKeys "{Tab}", True
      End If
  '--13-----------------------------------------
  'Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AW" & CustRow).Value, True   '13C.1 CurrentMailingAddress_NumberAndStreet[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AZ" & CustRow).Value, True   '13C.4 CurrentMailingAddress_ZIPOrPostalCode_FirstFiveNumbers[0]
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AY" & CustRow).Value, True   '13C.3 CurrentMailingAddress_StateOrProvince[0]
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("AX" & CustRow).Value, True   '13C.2 Currentcity[0]
  Application.Wait Now + 0.00001
  '
  Application.SendKeys "{Tab}", True
  
  '------------------------
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("S" & CustRow).Value, True   '8.3 Telephone No
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("R" & CustRow).Value, True   '8.2 Telephone No
  Application.Wait Now + 0.00001
  
  Application.SendKeys "{Tab}", True
  Application.SendKeys Range("Q" & CustRow).Value, True   '8.1 Telephone No
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("T" & CustRow).Value, True   ' 9.email Address
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AO" & CustRow).Value, True   ' 11F.email oTHER
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AM" & CustRow).Value, True   '11E. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AL" & CustRow).Value, True   '11E. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AK" & CustRow).Value, True   '11E. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AV" & CustRow).Value, True   '13B. REASON FOR SEPARATION
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AS" & CustRow).Value, True   '12B.3 SPOUSE'S VA FILE NUMBER_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AR" & CustRow).Value, True   '12B.2 SPOUSE'S VA FILE NUMBER_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("AQ" & CustRow).Value, True   '12B.1 SPOUSE'S VA FILE NUMBER_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A1---------
  Application.SendKeys Range("BC" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BB" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BA" & CustRow).Value, True   '14A.1 TO WHOM MARRIED  Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A2---------
  Application.SendKeys Range("BF" & CustRow).Value, True   '14A2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BE" & CustRow).Value, True   '14A2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BD" & CustRow).Value, True   '14A2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("BI" & CustRow).Value, True   '14A2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BH" & CustRow).Value, True   '14A2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BG" & CustRow).Value, True   '14A2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '-----------14A3---------
  
  Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Annulment" Then     '14.3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("BJ" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("BK" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
      End If
      
   '--------------------------------------
   '-----------14A4---------
  Application.SendKeys Range("BQ" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BP" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BO" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BN" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BM" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BL" & CustRow).Value, True   '14A.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '--------------------------------------------
  '14B.1 TO WHOM MARRIED
  
  
  Application.SendKeys Range("BT" & CustRow).Value, True   '14B.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BS" & CustRow).Value, True   '14B.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BR" & CustRow).Value, True   '14B.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '--------------------------------------------------
  '-----------14B2---------
  Application.SendKeys Range("BW" & CustRow).Value, True   '14B2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BV" & CustRow).Value, True   '14B2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BU" & CustRow).Value, True   '14B2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  Application.SendKeys Range("BZ" & CustRow).Value, True   '14B2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BY" & CustRow).Value, True   '14B2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("BX" & CustRow).Value, True   '14B2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '-----------------------------------------
  '---14B.3 REASON FOR TERMINATION''
  Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Annulment" Then     '14.3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CA" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("CB" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
       
      End If
      
  '---------------------
  '---14B.4 DATE AND PLACE MARRIAGE TERMINATED---
  Application.SendKeys Range("CH" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CG" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CF" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CE" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CD" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CC" & CustRow).Value, True   '14B.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  '--------------------
  '---14C.1 TO WHOM MARRIED--
  Application.SendKeys Range("CK" & CustRow).Value, True   '14C.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CJ" & CustRow).Value, True   '14C.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CI" & CustRow).Value, True   '14C.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------
  
  '---14C.2 DATE AND PLACE OF MARRIAGE
  Application.SendKeys Range("CN" & CustRow).Value, True   '14C2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CM" & CustRow).Value, True   '14C2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CL" & CustRow).Value, True   '14C2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CQ" & CustRow).Value, True   '14C2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CP" & CustRow).Value, True   '14C2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CO" & CustRow).Value, True   '14C2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '-------------------------------
  '--14C.3 REASON FOR TERMINATION
  Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Annulment" Then     '14.C3 "Annulment"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("CR" & CustRow).Value = "Other" Then     'Other
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
              Application.SendKeys Range("CS" & CustRow).Value, True
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
              Application.SendKeys "{Tab}", True
      End If
  
  '------------------------
  '---14C.4 DATE AND PLACE MARRIAGE TERMINATED
  Application.SendKeys Range("CY" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CX" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CW" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CV" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CU" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CT" & CustRow).Value, True   '14C.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------------
  '-----------14D2---------
  
  Application.SendKeys Range("DH" & CustRow).Value, True   '14D2. Place of Marriage country
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DG" & CustRow).Value, True   '14D2. Place of Marriage State
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DF" & CustRow).Value, True   '14D2. Place of Marriage city
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DE" & CustRow).Value, True   '14D2. Date of Marriage Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DD" & CustRow).Value, True   '14D2. Date of Marriage Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DC" & CustRow).Value, True   '14D2. Date of Marriage Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  '---14D.1 TO WHOM MARRIED--
  Application.SendKeys Range("DB" & CustRow).Value, True   '14D.1 TO WHOM MARRIED Last NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DA" & CustRow).Value, True   '14D.1 TO WHOM MARRIED Middle NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("CZ" & CustRow).Value, True   '14D.1 TO WHOM MARRIED First NAME
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '----------------------------
  '---14D.3 REASON FOR TERMINATION ( OTHER)
  Application.SendKeys Range("DJ" & CustRow).Value, True   '14D.3 REASON FOR TERMINATION ( OTHER)
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  '--------------------------------
  '--14D.3 REASON FOR TERMINATION
  Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Other" Then     '14.C3 "Other"
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Annulment" Then     'Annulment
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Divorce" Then     'Divorce
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
      
      Application.Wait Now + 0.00002
      If Range("DI" & CustRow).Value = "Death" Then     'Death
              Application.SendKeys "{~}"
              Application.SendKeys "{Tab}", True
       Else: Application.SendKeys "{Tab}", True
       
      End If
  
  '----14D.4 DATE AND PLACE MARRIAGE TERMINATED ---
  Application.SendKeys Range("DM" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Year
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DL" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Day
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("DK" & CustRow).Value, True   '14D.4 DATE AND PLACE MARRIAGE TERMINATED Month
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  
  '---VET SSNUMBER
  Application.SendKeys Range("E" & CustRow).Value, True   '2.3 SocialSecurityNumber_Last 4
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("D" & CustRow).Value, True   '2.2 SocialSecurityNumber_Middle 2
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
  
  Application.SendKeys Range("C" & CustRow).Value, True   '2.1 SocialSecurityNumber_First 3
  Application.Wait Now + 0.00001
  Application.SendKeys "{Tab}", True
End Sub
 

Th_B

New Member
Joined
Jul 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi DanteAmor, Thank you so much for the help and the code, I will try it and update.
Have a great day ahead.

Regards
Thusha
 

Th_B

New Member
Joined
Jul 16, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi @DanteAmor, That method works. I can continue my task and complete it soon. Yesterday about 8 hours I tried to find a solution and finally found this site and then posted this. I don't know how to thank you. Really appreciate your support. You saved my life. God Bless You

Thank you Sir.

Regards
Thusha
 
Last edited by a moderator:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,521
Messages
5,625,294
Members
416,087
Latest member
drth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top