Making code dynamic

[jayymehta]

Hi, I have this code which seems not proper when there's any change in input file. The code I made is Static which I want to convert into dynamic. The problem is I don't know the way. Here's the code I've made which I want as dynamic as possible:

Private Sub OutputBttn_Click()

  Dim fName As String
  Dim sName As String
  Dim srcWbk As Workbook
  Dim wksht As Worksheet
  Dim iRow As Long
  Dim nRowVisa As Double
  Dim nRowMC As Double, nRowAMEX As Double
  Dim nRowDisc As Double
  
  nRowVisa = 5
  nRowMC = 6
  nRowAMEX = 7
  nRowDisc = 8
  
  Set tgtWbk = ThisWorkbook
  
  fName = UserForm5.TextBox1.Value
  
  With UserForm5.ComboBox1
     If .ListIndex >= 0 Then
        sName = .Text
     End If
  End With
  
For Each wksht In ThisWorkbook.Worksheets
    Application.DisplayAlerts = False
    If wksht.Name = "OP" Then
        wksht.Delete
    End If
Next wksht


With ThisWorkbook
    Worksheets.Add.Name = "OP"
End With
   
With ThisWorkbook.Worksheets("OP")
    Range("A1:L1") = Array("!TRNS", "TRNSID", "TRNSTYPE", "DATE", "ACCNT", "NAME", "PAYMETH", "CLASS", "AMOUNT", "MEMO", "DOCNUM", "CLEAR")
    Range("A2:L2") = Array("!SPL", "SPLID", "TRNSTYPE", "DATE", "ACCNT", "NAME", "PAYMETH", "CLASS", "AMOUNT", "MEMO", "DOCNUM", "CLEAR")
    Range("A3") = "!ENDTRNS"
    
    For iRow = 0 To 14
    
        .Cells(nRowVisa + iRow, 7).Value = "Visa"
        nRowVisa = nRowVisa + 5
        .Cells(nRowMC + iRow, 7).Value = "Master Card"
        nRowMC = nRowMC + 5
        .Cells(nRowAMEX + iRow, 7).Value = "American Express"
        nRowAMEX = nRowAMEX + 5
        .Cells(nRowDisc + iRow, 7).Value = "Discover"
        nRowDisc = nRowDisc + 5


    Next iRow
    
    Range("J4,J10,J16,J22,J28,J34,J40,J46,J52,J58,J64,J70,J76,J82").Value = "WEBSTER TNF-IS"
    Range("L4:L8, L10:L14, L16:L20, L22:L26, L28:L32, L34:L38, L40:L44, L46:L50, L52:L56, L58:L62, L64:L68, L70:L74, L76:L80, L82:L86").Value = "N"
    Range("C4:C8, C10:C14, C16:C20, C22:C26, C28:C32, C34:C38, C40:C44, C46:C50, C52:C56, C58:C62, C64:C68, C70:C74, C76:C80, C82:C86").Value = "DEPOSIT"
    Range("E5:E8, E11:E14, E17:E20, E23:E26, E29:E32, E35:E38, E41:E44, E47:E50, E53:E56, E59:E62, E65:E68, E71:E74, E77:E80, E83:E86").Value = "400"
    Range("H5:H8, H11:H14, H17:H20, H23:H26, H29:H32, H35:H38, H41:H44, H47:H50, H53:H56, H59:H62, H65:H68, H71:H74, H77:H80, H83:H86").Value = "Inc.:TNF"
    Range("A5:A8, A11:A14, A17:A20, A23:A26, A29:A32, A35:A38, A41:A44, A47:A50, A53:A56, A59:A62, A65:A68, A71:A74, A77:A80, A83:A86").Value = "SPL"
    
End With
    
   Set srcWbk = Workbooks.Open(fName)






Workbooks("Test Project").Worksheets(sName).Range("C5:F5").Copy
ThisWorkbook.Worksheets("OP").Range("I5:I8").PasteSpecial Transpose:=True
ThisWorkbook.Worksheets("OP").Range("I5:I8").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Workbooks("Test Project").Worksheets(sName).Range("C6:F6").Copy
ThisWorkbook.Worksheets("OP").Range("I11:I14").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C7:F7").Copy
ThisWorkbook.Worksheets("OP").Range("I17:I20").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C8:F8").Copy
ThisWorkbook.Worksheets("OP").Range("I23:I26").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C9:F9").Copy
ThisWorkbook.Worksheets("OP").Range("I29:I32").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C10:F10").Copy
ThisWorkbook.Worksheets("OP").Range("I35:I38").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C11:F11").Copy
ThisWorkbook.Worksheets("OP").Range("I41:I44").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C12:F12").Copy
ThisWorkbook.Worksheets("OP").Range("I47:I50").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C13:F13").Copy
ThisWorkbook.Worksheets("OP").Range("I53:I56").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C14:F14").Copy
ThisWorkbook.Worksheets("OP").Range("I59:I62").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C15:F15").Copy
ThisWorkbook.Worksheets("OP").Range("I65:I68").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C16:F16").Copy
ThisWorkbook.Worksheets("OP").Range("I71:I74").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C17:F17").Copy
ThisWorkbook.Worksheets("OP").Range("I77:I80").PasteSpecial Transpose:=True
Workbooks("Test Project").Worksheets(sName).Range("C18:F18").Copy
ThisWorkbook.Worksheets("OP").Range("I83:I86").PasteSpecial Transpose:=True


With ThisWorkbook.Worksheets("OP")
    .Cells(4, 9).Value = "=Sum(I5:I8)"
    .Cells(10, 9).Value = "=Sum(I11:I14)"
    .Cells(16, 9).Value = "=Sum(I17:I20)"
    .Cells(22, 9).Value = "=Sum(I23:I26)"
    .Cells(28, 9).Value = "=Sum(I29:I32)"
    .Cells(34, 9).Value = "=Sum(I35:I38)"
    .Cells(40, 9).Value = "=Sum(I41:I44)"
    .Cells(46, 9).Value = "=Sum(I47:I50)"
    .Cells(52, 9).Value = "=Sum(I53:I56)"
    .Cells(58, 9).Value = "=Sum(I59:I62)"
    .Cells(64, 9).Value = "=Sum(I65:I68)"
    .Cells(70, 9).Value = "=Sum(I71:I74)"
    .Cells(76, 9).Value = "=Sum(I77:I80)"
    .Cells(82, 9).Value = "=Sum(I83:I86)"
End With
End Sub