Worksheet Change on only visible worksheets

T

Tdorman

Board Regular
Joined
Aug 12, 2021
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
I was wondering if it was possible to make a worksheet change event occur on only visible worksheets? Currently, when a value is changed in a cell, that amount of columns are inserted on every other worksheet. For example, if I enter 10, 10 new columns will be inserted on the other worksheets with specific formulas for each sheet. I am trying to make it so that only certain worksheets have the columns inserted. There are issues where the amount of data and columns being inserted are too large and most of the worksheets aren't required. If I can have it only insert columns on the visible worksheets then I can create macros that I can be clicked to hide and unhide the sheets I need, which will change with each new project.

Essentially, I would have only my main page in the workbook to begin with. Each additional worksheet would be listed with a button to add the other sheet to the workbook. If the sheet is not selected, I need it to not be included in the worksheet change event code listed below.

This is the main worksheet change code with each sheet listed

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    Const SOMESHEETS As String = "*MemberInfo*C-Proposal*Schedule J*NOL*DivExl*NOL-P*SchA-3*Schedule H*NOL-PA*Schedule A*Schedule A-5*"      ' <<< change / append sheet names to suit
                                                                                                                                             '     be sure each sheet name is between * characters
    Dim KeyCells As Range, ColNum As Long
    Dim ws As Worksheet
    
    Set KeyCells = Range("B30")
    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        If IsNumeric(KeyCells.Value) Then
            ColNum = KeyCells.Value
            If ColNum > 0 Then
                For Each ws In ThisWorkbook.Worksheets
                    If CBool(InStr(LCase(SOMESHEETS), LCase("*" & ws.Name & "*"))) Then
                        InsertColumnsOnSheet argSheet:=ws, argColNum:=ColNum
                    End If
                Next ws
            End If
        End If
    End If
End Sub

Then in a separate module I have the following

VBA Code: 
Option Explicit
Public Sub InsertColumnsOnSheet(ByVal argSheet As Worksheet, ByVal argColNum As Long)

    Dim Rng As Range, c As Range
    Dim TotalCol As Long, LeftFixedCol As Long
    Dim i As Long
    Dim ws As Worksheet

    Set ws = Worksheets("MemberInfo")
    With argSheet
        Set Rng = .Range(.Cells(4, 1), .Cells(4, .Columns.Count))
        Set c = Rng.Find("END")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 1
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(4, i).Value = "Member" & i - LeftFixedCol
                    .Cells(5, i).Value = "=$B$5"
                    .Cells(6, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C2,COLUMN()-3,0)"
                    .Cells(7, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-3,0)"
                    .Cells(8, i).FormulaR1C1 = "=IF(VLOOKUP(R[-1]C,'DATA Member'!R3C3:R20000C46,2,FALSE)=0,"" - "",VLOOKUP(R[-1]C,'DATA Member'!R3C3:R20000C46,2,FALSE))"
                    .Cells(9, i).FormulaR1C1 = "=IF(VLOOKUP(R[-2]C,'DATA Member'!R3C3:R20000C46,3,FALSE)=0,"" - "",VLOOKUP(R[-2]C,'DATA Member'!R3C3:R20000C46,3,FALSE))"
                    .Cells(10, i).FormulaR1C1 = "=IF(VLOOKUP(R[-3]C,'DATA Member'!R3C3:R20000C46,4,FALSE)=0,"" - "",VLOOKUP(R[-3]C,'DATA Member'!R3C3:R20000C46,4,FALSE))"
                    .Cells(11, i).FormulaR1C1 = "=IF(VLOOKUP(R[-4]C,'DATA Member'!R3C3:R20000C46,5,FALSE)=0,"" - "",VLOOKUP(R[-4]C,'DATA Member'!R3C3:R20000C46,5,FALSE))"
                    .Cells(12, i).FormulaR1C1 = "=IF(VLOOKUP(R[-5]C,'DATA Member'!R3C3:R20000C46,6,FALSE)=0,"" - "",VLOOKUP(R[-5]C,'DATA Member'!R3C3:R20000C46,6,FALSE))"
                    .Cells(13, i).FormulaR1C1 = "=IF(VLOOKUP(R[-6]C,'DATA Member'!R3C3:R20000C46,7,FALSE)=0,"" - "",VLOOKUP(R[-6]C,'DATA Member'!R3C3:R20000C46,7,FALSE))"
                    .Cells(14, i).FormulaR1C1 = "=IF(VLOOKUP(R[-7]C,'DATA Member'!R3C3:R20000C46,8,FALSE)=0,"" - "",VLOOKUP(R[-7]C,'DATA Member'!R3C3:R20000C46,8,FALSE))"
                    .Cells(15, i).FormulaR1C1 = "=IF(VLOOKUP(R[-8]C,'DATA Member'!R3C3:R20000C46,9,FALSE)=0,"" - "",VLOOKUP(R[-8]C,'DATA Member'!R3C3:R20000C46,9,FALSE))"
                    .Cells(16, i).FormulaR1C1 = "=IF(VLOOKUP(R[-9]C,'DATA Member'!R3C3:R20000C46,10,FALSE)=0,"" - "",VLOOKUP(R[-9]C,'DATA Member'!R3C3:R20000C46,10,FALSE))"
                    .Cells(17, i).FormulaR1C1 = "=IF(VLOOKUP(R[-10]C,'DATA Member'!R3C3:R20000C46,11,FALSE)=0,"" - "",VLOOKUP(R[-10]C,'DATA Member'!R3C3:R20000C46,11,FALSE))"
                    .Cells(18, i).FormulaR1C1 = "=IF(GroupInfo!R27C2=""X"",""X"",IF(VLOOKUP(R[-11]C,'DATA Member'!R3C3:R20000C46,12,FALSE)=0,"" - "",VLOOKUP(R[-11]C,'DATA Member'!R3C3:R20000C46,12,FALSE)))"
                    .Cells(19, i).FormulaR1C1 = "=IF(VLOOKUP(R[-12]C,'DATA Member'!R3C3:R20000C46,13,FALSE)=0,"" - "",VLOOKUP(R[-12]C,'DATA Member'!R3C3:R20000C46,13,FALSE))"
                    .Cells(20, i).FormulaR1C1 = "=IF(VLOOKUP(R[-13]C,'DATA Member'!R3C3:R20000C46,15,FALSE)=0,"" - "",VLOOKUP(R[-13]C,'DATA Member'!R3C3:R20000C46,15,FALSE))"
                    .Cells(21, i).FormulaR1C1 = "=IF(VLOOKUP(R[-14]C,'DATA Member'!R3C3:R20000C46,17,FALSE)=0,"" - "",VLOOKUP(R[-14]C,'DATA Member'!R3C3:R20000C46,17,FALSE))"
                    .Cells(22, i).FormulaR1C1 = "=IF(VLOOKUP(R[-15]C,'DATA Member'!R3C3:R20000C46,19,FALSE)=0,"" - "",VLOOKUP(R[-15]C,'DATA Member'!R3C3:R20000C46,19,FALSE))"
                    .Cells(23, i).FormulaR1C1 = "=IF(VLOOKUP(R[-16]C,'DATA Member'!R3C3:R20000C46,21,FALSE)=0,"" - "",VLOOKUP(R[-16]C,'DATA Member'!R3C3:R20000C46,21,FALSE))"
                    .Cells(24, i).FormulaR1C1 = "=IF(VLOOKUP(R[-17]C,'DATA Member'!R3C3:R20000C46,23,FALSE)=0,"" - "",VLOOKUP(R[-17]C,'DATA Member'!R3C3:R20000C46,23,FALSE))"
                    .Cells(25, i).FormulaR1C1 = "=IF(VLOOKUP(R[-18]C,'DATA Member'!R3C3:R20000C46,25,FALSE)=0,"" - "",VLOOKUP(R[-18]C,'DATA Member'!R3C3:R20000C46,25,FALSE))"
                    .Cells(26, i).FormulaR1C1 = "=IF(VLOOKUP(R[-19]C,'DATA Member'!R3C3:R20000C46,27,FALSE)=0,"" - "",VLOOKUP(R[-19]C,'DATA Member'!R3C3:R20000C46,27,FALSE))"
                    .Cells(27, i).FormulaR1C1 = "=IF(VLOOKUP(R[-20]C,'DATA Member'!R3C3:R20000C46,29,FALSE)=0,"" - "",VLOOKUP(R[-20]C,'DATA Member'!R3C3:R20000C46,29,FALSE))"
                    .Cells(28, i).FormulaR1C1 = "=IF(VLOOKUP(R[-21]C,'DATA Member'!R3C3:R20000C46,31,FALSE)=0,"" - "",VLOOKUP(R[-21]C,'DATA Member'!R3C3:R20000C46,31,FALSE))"
                    .Cells(29, i).FormulaR1C1 = "=IF(VLOOKUP(R[-22]C,'DATA Member'!R3C3:R20000C46,33,FALSE)=0,"" - "",VLOOKUP(R[-22]C,'DATA Member'!R3C3:R20000C46,33,FALSE))"
                    .Cells(30, i).FormulaR1C1 = "=IF(VLOOKUP(R[-23]C,'DATA Member'!R3C3:R20000C46,35,FALSE)=0,"" - "",VLOOKUP(R[-23]C,'DATA Member'!R3C3:R20000C46,35,FALSE))"
                    .Cells(31, i).FormulaR1C1 = "=IF(VLOOKUP(R[-24]C,'DATA Member'!R3C3:R20000C46,37,FALSE)=0,"" - "",VLOOKUP(R[-24]C,'DATA Member'!R3C3:R20000C46,37,FALSE))"
                    .Cells(32, i).FormulaR1C1 = "=IF(VLOOKUP(R[-25]C,'DATA Member'!R3C3:R20000C46,39,FALSE)=0,"" - "",VLOOKUP(R[-25]C,'DATA Member'!R3C3:R20000C46,39,FALSE))"
                    .Cells(33, i).FormulaR1C1 = "=IF(VLOOKUP(R[-26]C,'DATA Member'!R3C3:R20000C46,41,FALSE)=0,"" - "",VLOOKUP(R[-26]C,'DATA Member'!R3C3:R20000C46,41,FALSE))"
                    .Cells(34, i).FormulaR1C1 = "=IF(VLOOKUP(R[-27]C,'DATA Member'!R3C3:R20000C46,43,FALSE)=0,"" - "",VLOOKUP(R[-27]C,'DATA Member'!R3C3:R20000C46,43,FALSE))"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("C-Proposal")
    With argSheet
        Set Rng = .Range(.Cells(1, 6), .Cells(1, .Columns.Count))
        Set c = Rng.Find("TOTAL")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 5
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).FormulaR1C1 = "=R2C3"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$7,COLUMN()-7,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-7,0)"
                    .Cells(6, i).FormulaR1C1 = "=IF(R[2]C<>R[6]C,"""",IF(R[13]C<>VLOOKUP(R[-2]C,'DATA Sch A'!R12C2:R20000C81,67,FALSE),"""",IF(R[16]C<>VLOOKUP(R[-2]C,'DATA Sch A'!R12C2:R20000C81,71,FALSE),"""",IF(R[17]C<>VLOOKUP(R[-2]C,'DATA Sch A'!R12C2:R20000C81,72,FALSE),"""",IF(R[18]C<>VLOOKUP(R[-2]C,'DATA Sch A'!R12C2:R20000C81,73,FALSE),"""",""X"")))))"
                    .Cells(8, i).FormulaR1C1 = "=IF(R[-4]C=999999999,VLOOKUP(R[-5]C,'DATA Sch A'!R12C3:R20066C81,58,FALSE),VLOOKUP(R[-4]C,'DATA Sch A'!R12C2:R20066C81,59,FALSE))"
                    .Cells(9, i).Value = "=0"
                    .Cells(10, i).Value = "=0"
                    .Cells(11, i).Value = "=0"
                    .Cells(12, i).FormulaR1C1 = "=SUM(R[-4]C:(OFFSET(R[-1]C,0,0)))"
                    .Cells(19, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-15]C,'DATA Sch A'!R12C2:R20000C81,67,FALSE),0)"
                    .Cells(22, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-18]C,'DATA Sch A'!R12C2:R20000C81,71,FALSE),0)"
                    .Cells(23, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-19]C,'DATA Sch A'!R12C2:R20000C81,72,FALSE),0)"
                    .Cells(24, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-20]C,'DATA Sch A'!R12C2:R20000C81,73,FALSE),0)"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("Schedule J")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("FINISH")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$7,COLUMN()-5,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-5,0)"
                    .Cells(6, i).FormulaR1C1 = "=IF(SUM(R[7]C:R[8]C)<>R[10]C,"""",IF(SUM(R[11]C:R[12]C)<>R[14]C,"""",""X""))"
                    .Cells(8, i).FormulaR1C1 = "=IFERROR(IF(R[-4]C=999999999,VLOOKUP(R[-5]C,'DATA Sch J'!R6C3:R20000C16,2,FALSE),VLOOKUP(R[-4]C,'DATA Sch J'!R6C2:R20000C16,3,FALSE)),0)"
                    .Cells(9, i).FormulaR1C1 = "=IFERROR(IF(R[-5]C=999999999,VLOOKUP(R[-6]C,'DATA Sch J'!R6C3:R20000C16,3,FALSE),VLOOKUP(R[-5]C,'DATA Sch J'!R6C2:R20000C16,4,FALSE)),0)"
                    .Cells(10, i).FormulaR1C1 = "=IFERROR(IF(R[-6]C=999999999,VLOOKUP(R[-7]C,'DATA Sch J'!R6C3:R20000C16,4,FALSE),VLOOKUP(R[-6]C,'DATA Sch J'!R6C2:R20000C16,5,FALSE)),0)"
                    .Cells(11, i).FormulaR1C1 = "=IFERROR(IF(R[-7]C=999999999,VLOOKUP(R[-8]C,'DATA Sch J'!R6C3:R20000C16,5,FALSE),VLOOKUP(R[-7]C,'DATA Sch J'!R6C2:R20000C16,6,FALSE)),0)"
                    .Cells(12, i).FormulaR1C1 = "=IFERROR(IF(R[-8]C=999999999,VLOOKUP(R[-9]C,'DATA Sch J'!R6C3:R20000C16,6,FALSE),VLOOKUP(R[-8]C,'DATA Sch J'!R6C2:R20000C16,7,FALSE)),0)"
                    .Cells(13, i).FormulaR1C1 = "=SUM(R[-5]C:(OFFSET(R[-1]C,0,0)))"
                    .Cells(14, i).FormulaR1C1 = "=IFERROR(IF(IF(R[-10]C=999999999,VLOOKUP(R[-11]C,'DATA Sch J'!R6C3:R20000C16,8,FALSE),VLOOKUP(R[-10]C,'DATA Sch J'!R6C2:R20000C16,9,FALSE))<0,IF(R[-10]C=999999999,VLOOKUP(R[-11]C,'DATA Sch J'!R6C3:R20000C16,8,FALSE),VLOOKUP(R[-10]C,'DATA Sch J'!R6C2:R20000C16,9,FALSE)),-(IF(R[-10]C=999999999,VLOOKUP(R[-11]C,'DATA Sch J'!R6C3:R20000C16,8,FALSE),VLOOKUP(R[-10]C,'DATA Sch J'!R6C2:R20000C16,9,FALSE)))),0)"
                    .Cells(15, i).Value = "=0"
                    .Cells(16, i).FormulaR1C1 = "=SUM(R[-3]C:(OFFSET(R[-1]C,0,0)))"
                    .Cells(17, i).FormulaR1C1 = "=IFERROR(IF(R[-13]C=999999999,VLOOKUP(R[-14]C,'DATA Sch J'!R6C3:R20000C16,10,FALSE),VLOOKUP(R[-13]C,'DATA Sch J'!R6C2:R20000C16,11,FALSE)),0)"
                    .Cells(18, i).FormulaR1C1 = "=IFERROR(IF(IF(R[-14]C=999999999,VLOOKUP(R[-15]C,'DATA Sch J'!R6C3:R20002C16,11,FALSE),VLOOKUP(R[-14]C,'DATA Sch J'!R6C2:R20002C16,12,FALSE))<0,IF(R[-14]C=999999999,VLOOKUP(R[-15]C,'DATA Sch J'!R6C3:R20002C16,11,FALSE),VLOOKUP(R[-14]C,'DATA Sch J'!R6C2:R20002C16,12,FALSE)),-(IF(R[-14]C=999999999,VLOOKUP(R[-15]C,'DATA Sch J'!R6C3:R20002C16,11,FALSE),VLOOKUP(R[-14]C,'DATA Sch J'!R6C2:R20002C16,12,FALSE)))),0)"
                    .Cells(19, i).Value = "=0"
                    .Cells(20, i).FormulaR1C1 = "=SUM(R[-3]C:(OFFSET(R[-1]C,0,0)))"
                    .Cells(21, i).FormulaR1C1 = "=R21C3"
                    .Cells(22, i).FormulaR1C1 = "=IFERROR(R[-6]C/R[-1]C,0)"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("NOL")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("ETC")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 4
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$D$2"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$7,COLUMN()-6,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-6,0)"
                    .Cells(6, i).FormulaR1C1 = "=IF(VLOOKUP(R[-3]C,'DATA 500U'!R6C2:R20000C12,11,FALSE)<>NOL!R[12]C,"""",IF(VLOOKUP(R[-3]C,'DATA 500U'!R6C2:R20000C31,27,FALSE),"""",""X""))"
                    .Cells(11, i).FormulaR1C1 = "=IFERROR(HLOOKUP(R[-7]C,'NOL-P'!R4C3:R92C16384,89,FALSE),0)"
                    .Cells(12, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-8]C,'DATA 500U'!R6C2:R20000C31,4,FALSE),0))"
                    .Cells(13, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-9]C,'DATA 500U'!R6C2:R20000C31,5,FALSE),0))"
                    .Cells(14, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-10]C,'DATA 500U'!R6C2:R20000C31,6,FALSE),0))"
                    .Cells(15, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-11]C,'DATA 500U'!R6C2:R20000C31,7,FALSE),0))"
                    .Cells(16, i).FormulaR1C1 = "=IF(SUM(R[-5]C:(OFFSET(R[-1]C,0,0)))<0,0,SUM(R[-5]C:(OFFSET(R[-1]C,0,0))))"
                    .Cells(17, i).FormulaR1C1 = "='C-Proposal'!R12C3"
                    .Cells(18, i).FormulaR1C1 = "=R[-1]C*HLOOKUP(R[-14]C,'Schedule J'!R4C4:R22C16384,19,FALSE)"
                    .Cells(19, i).FormulaR1C1 = "=IF(R[-3]C<R[-1]C,R[-3]C,R[-1]C)"
                    .Cells(25, i).FormulaR1C1 = "=IFERROR(HLOOKUP(R[-21]C,'NOL-PA'!R4C3:R75C16384,72,FALSE),0)"
                    .Cells(26, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-22]C,'DATA 500U'!R6C2:R20000C31,14,FALSE),0))"
                    .Cells(28, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-24]C,'DATA 500U'!R6C2:R20000C31,16,FALSE),0))"
                    .Cells(29, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-25]C,'DATA 500U'!R6C2:R20000C31,17,FALSE),0))"
                    .Cells(30, i).FormulaR1C1 = "=IF(SUM(R[-5]C:(OFFSET(R[-1]C,0,0)))<0,0,SUM(R[-5]C:(OFFSET(R[-1]C,0,0))))"
                    .Cells(31, i).FormulaR1C1 = "='C-Proposal'!R12C3"
                    .Cells(32, i).FormulaR1C1 = "=R[-1]C*HLOOKUP(R[-28]C,'Schedule J'!R4C4:R22C16384,19,FALSE)"
                    .Cells(33, i).FormulaR1C1 = "=R[-14]C"
                    .Cells(34, i).FormulaR1C1 = "=R[-2]C-R[-1]C"
                    .Cells(35, i).FormulaR1C1 = "=IF(R[-1]C<R[-5]C,R[-1]C,R[-5]C)"
                    .Cells(36, i).FormulaR1C1 = "=R[-6]C-R[-1]C"
                    .Cells(37, i).FormulaR1C1 = "=-(IFERROR(VLOOKUP(R[-33]C,'DATA 500U'!R6C2:R20000C31,25,FALSE),0))"
                    .Cells(38, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-34]C,'DATA 500U'!R6C2:R20000C31,26,FALSE),0)"
                    .Cells(39, i).FormulaR1C1 = "=IF(AND(R[-4]C+R[-1]C>R[-5]C,R[-14]C>0),R[-5]C,R[-4]C+R[-1]C)"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("DivExl")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("CLOSE")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C2,COLUMN()-5,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-5,0)"
                    .Cells(6, i).Value = "=IF(VLOOKUP(R[-2]C,'DATA Sch R'!R3C2:R20000C17,3,FALSE)<>R[2]C,"""",IF(VLOOKUP(R[-2]C,'DATA Sch R'!R3C2:R20000C17,4,FALSE)<>R[3]C,"""",""X""))"
                    .Cells(8, i).FormulaR1C1 = "=VLOOKUP(R[-4]C,'DATA Sch R'!R3C2:R20000C17,3,FALSE)"
                    .Cells(9, i).FormulaR1C1 = "=VLOOKUP(R[-5]C,'DATA Sch R'!R3C2:R20000C17,4,FALSE)"
                    .Cells(10, i).FormulaR1C1 = "=R[-2]C-R[-1]C"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("NOL-P")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("LIMIT")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 2
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=ManagerialMemberInfo[ns1:UnitaryID]"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$6,COLUMN()-3,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$6,COLUMN()-3,0)"
                    .Cells(6, i).FormulaR1C1 = "=IF(VLOOKUP(R[-2]C,'DATA 500U-P'!R3C2:R20000C8,7,FALSE)<>'NOL-P'!R[86]C,"""",""X"")"
                    .Cells(8, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-4]C,'DATA 500U-P'!C2:C4,3,FALSE),0)"
                    .Cells(12, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-8]C,'DATA 500U-P'!C2,0))<>'NOL-P'!R[-8]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-8]C,'DATA 500U-P'!C2,0)))"
                    .Cells(13, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-9]C,'DATA 500U-P'!C2,0))<>'NOL-P'!R[-9]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-9]C,'DATA 500U-P'!C2,0)))"
                    .Cells(14, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-6]C)"
                    .Cells(16, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-12]C,'DATA 500U-P'!C2,0)+1,1)<>'NOL-P'!R[-12]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-12]C,'DATA 500U-P'!C2,0)+1,1))"
                    .Cells(17, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-13]C,'DATA 500U-P'!C2,0)+1,1)<>'NOL-P'!R[-13]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-13]C,'DATA 500U-P'!C2,0)+1,1))"
                    .Cells(18, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-10]C)"
                    .Cells(20, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-16]C,'DATA 500U-P'!C2,0)+2,1)<>'NOL-P'!R[-16]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-16]C,'DATA 500U-P'!C2,0)+2,1))"
                    .Cells(21, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-17]C,'DATA 500U-P'!C2,0)+2,1)<>'NOL-P'!R[-17]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-17]C,'DATA 500U-P'!C2,0)+2,1))"
                    .Cells(22, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-14]C)"
                    .Cells(24, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-20]C,'DATA 500U-P'!C2,0)+3,1)<>'NOL-P'!R[-20]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-20]C,'DATA 500U-P'!C2,0)+3,1))"
                    .Cells(25, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-21]C,'DATA 500U-P'!C2,0)+3,1)<>'NOL-P'!R[-21]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-21]C,'DATA 500U-P'!C2,0)+3,1))"
                    .Cells(26, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-18]C)"
                    .Cells(28, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-24]C,'DATA 500U-P'!C2,0)+4,1)<>'NOL-P'!R[-24]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-24]C,'DATA 500U-P'!C2,0)+4,1))"
                    .Cells(29, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-25]C,'DATA 500U-P'!C2,0)+4,1)<>'NOL-P'!R[-25]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-25]C,'DATA 500U-P'!C2,0)+4,1))"
                    .Cells(30, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-22]C)"
                    .Cells(32, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-28]C,'DATA 500U-P'!C2,0)+5,1)<>'NOL-P'!R[-28]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-28]C,'DATA 500U-P'!C2,0)+5,1))"
                    .Cells(33, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-29]C,'DATA 500U-P'!C2,0)+5,1)<>'NOL-P'!R[-29]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-29]C,'DATA 500U-P'!C2,0)+5,1))"
                    .Cells(34, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-26]C)"
                    .Cells(36, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-32]C,'DATA 500U-P'!C2,0)+6,1)<>'NOL-P'!R[-32]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-32]C,'DATA 500U-P'!C2,0)+6,1))"
                    .Cells(37, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-33]C,'DATA 500U-P'!C2,0)+6,1)<>'NOL-P'!R[-33]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-33]C,'DATA 500U-P'!C2,0)+6,1))"
                    .Cells(38, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-30]C)"
                    .Cells(40, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-36]C,'DATA 500U-P'!C2,0)+7,1)<>'NOL-P'!R[-36]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-36]C,'DATA 500U-P'!C2,0)+7,1))"
                    .Cells(41, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-37]C,'DATA 500U-P'!C2,0)+7,1)<>'NOL-P'!R[-37]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-37]C,'DATA 500U-P'!C2,0)+7,1))"
                    .Cells(42, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-34]C)"
                    .Cells(44, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-40]C,'DATA 500U-P'!C2,0)+8,1)<>'NOL-P'!R[-40]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-40]C,'DATA 500U-P'!C2,0)+8,1))"
                    .Cells(45, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-41]C,'DATA 500U-P'!C2,0)+8,1)<>'NOL-P'!R[-41]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-41]C,'DATA 500U-P'!C2,0)+8,1))"
                    .Cells(46, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-38]C)"
                    .Cells(48, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-44]C,'DATA 500U-P'!C2,0)+9,1)<>'NOL-P'!R[-44]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-44]C,'DATA 500U-P'!C2,0)+9,1))"
                    .Cells(49, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-45]C,'DATA 500U-P'!C2,0)+9,1)<>'NOL-P'!R[-45]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-45]C,'DATA 500U-P'!C2,0)+9,1))"
                    .Cells(50, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-42]C)"
                    .Cells(52, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-48]C,'DATA 500U-P'!C2,0)+10,1)<>'NOL-P'!R[-48]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-48]C,'DATA 500U-P'!C2,0)+10,1))"
                    .Cells(53, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-49]C,'DATA 500U-P'!C2,0)+10,1)<>'NOL-P'!R[-49]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-49]C,'DATA 500U-P'!C2,0)+10,1))"
                    .Cells(54, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-46]C)"
                    .Cells(56, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-52]C,'DATA 500U-P'!C2,0)+11,1)<>'NOL-P'!R[-52]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-52]C,'DATA 500U-P'!C2,0)+11,1))"
                    .Cells(57, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-53]C,'DATA 500U-P'!C2,0)+11,1)<>'NOL-P'!R[-53]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-53]C,'DATA 500U-P'!C2,0)+11,1))"
                    .Cells(58, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-50]C)"
                    .Cells(60, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-56]C,'DATA 500U-P'!C2,0)+12,1)<>'NOL-P'!R[-56]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-56]C,'DATA 500U-P'!C2,0)+12,1))"
                    .Cells(61, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-57]C,'DATA 500U-P'!C2,0)+12,1)<>'NOL-P'!R[-57]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-57]C,'DATA 500U-P'!C2,0)+12,1))"
                    .Cells(62, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-54]C)"
                    .Cells(64, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-60]C,'DATA 500U-P'!C2,0)+13,1)<>'NOL-P'!R[-60]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-60]C,'DATA 500U-P'!C2,0)+13,1))"
                    .Cells(65, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-61]C,'DATA 500U-P'!C2,0)+13,1)<>'NOL-P'!R[-61]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-61]C,'DATA 500U-P'!C2,0)+13,1))"
                    .Cells(66, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-58]C)"
                    .Cells(68, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-64]C,'DATA 500U-P'!C2,0)+14,1)<>'NOL-P'!R[-64]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-64]C,'DATA 500U-P'!C2,0)+14,1))"
                    .Cells(69, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-65]C,'DATA 500U-P'!C2,0)+14,1)<>'NOL-P'!R[-65]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-65]C,'DATA 500U-P'!C2,0)+14,1))"
                    .Cells(70, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-62]C)"
                    .Cells(72, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-68]C,'DATA 500U-P'!C2,0)+15,1)<>'NOL-P'!R[-68]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-68]C,'DATA 500U-P'!C2,0)+15,1))"
                    .Cells(73, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-69]C,'DATA 500U-P'!C2,0)+15,1)<>'NOL-P'!R[-69]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-69]C,'DATA 500U-P'!C2,0)+15,1))"
                    .Cells(74, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-66]C)"
                    .Cells(76, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-72]C,'DATA 500U-P'!C2,0)+16,1)<>'NOL-P'!R[-72]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-72]C,'DATA 500U-P'!C2,0)+16,1))"
                    .Cells(77, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-73]C,'DATA 500U-P'!C2,0)+16,1)<>'NOL-P'!R[-73]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-73]C,'DATA 500U-P'!C2,0)+16,1))"
                    .Cells(78, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-70]C)"
                    .Cells(80, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-76]C,'DATA 500U-P'!C2,0)+17,1)<>'NOL-P'!R[-76]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-76]C,'DATA 500U-P'!C2,0)+17,1))"
                    .Cells(81, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-77]C,'DATA 500U-P'!C2,0)+17,1)<>'NOL-P'!R[-77]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-77]C,'DATA 500U-P'!C2,0)+17,1))"
                    .Cells(82, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-74]C)"
                    .Cells(84, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-80]C,'DATA 500U-P'!C2,0)+18,1)<>'NOL-P'!R[-80]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-80]C,'DATA 500U-P'!C2,0)+18,1))"
                    .Cells(85, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-81]C,'DATA 500U-P'!C2,0)+18,1)<>'NOL-P'!R[-81]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-81]C,'DATA 500U-P'!C2,0)+18,1))"
                    .Cells(86, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-78]C)"
                    .Cells(88, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-84]C,'DATA 500U-P'!C2,0)+19,1)<>'NOL-P'!R[-84]C,0,INDEX('DATA 500U-P'!C5,MATCH('NOL-P'!R[-84]C,'DATA 500U-P'!C2,0)+19,1))"
                    .Cells(89, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-P'!C2,MATCH('NOL-P'!R[-85]C,'DATA 500U-P'!C2,0)+19,1)<>'NOL-P'!R[-85]C,0,INDEX('DATA 500U-P'!C6,MATCH('NOL-P'!R[-85]C,'DATA 500U-P'!C2,0)+19,1))"
                    .Cells(90, i).FormulaR1C1 = "=PRODUCT(R[-1]C,R[-82]C)"
                    .Cells(92, i).FormulaR1C1 = "=SUM(R[-78]C,R[-74]C,R[-70]C,R[-66]C,R[-62]C,R[-58]C,R[-54]C,R[-50]C,R[-46]C,R[-42]C,R[-38]C,R[-34]C,R[-30]C,R[-26]C,R[-22]C,R[-18]C,R[-14]C,R[-10]C,R[-6]C,R[-2]C)"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("SchA-3")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("FINAL")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C2,COLUMN()-5,0)"
                    .Cells(4, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C3,COLUMN()-5,0)"
                    .Cells(9, i).FormulaR1C1 = "=VLOOKUP(R[-5]C,'DATA Sch A-3'!R7C2:R20000C37,3,FALSE)"
                    .Cells(10, i).FormulaR1C1 = "=VLOOKUP(R[-6]C,'DATA Sch A-3'!R7C2:R20000C37,4,FALSE)"
                    .Cells(11, i).FormulaR1C1 = "=VLOOKUP(R[-7]C,'DATA Sch A-3'!R7C2:R20000C37,5,FALSE)"
                    .Cells(12, i).FormulaR1C1 = "=VLOOKUP(R[-8]C,'DATA Sch A-3'!R7C2:R20000C37,6,FALSE)"
                    .Cells(13, i).FormulaR1C1 = "=VLOOKUP(R[-9]C,'DATA Sch A-3'!R7C2:R20000C37,7,FALSE)"
                    .Cells(14, i).FormulaR1C1 = "=VLOOKUP(R[-10]C,'DATA Sch A-3'!R7C2:R20000C37,8,FALSE)"
                    .Cells(15, i).FormulaR1C1 = "=VLOOKUP(R[-11]C,'DATA Sch A-3'!R7C2:R20000C37,9,FALSE)"
                    .Cells(16, i).FormulaR1C1 = "=VLOOKUP(R[-12]C,'DATA Sch A-3'!R7C2:R20000C37,10,FALSE)"
                    .Cells(17, i).FormulaR1C1 = "=VLOOKUP(R[-13]C,'DATA Sch A-3'!R7C2:R20000C37,11,FALSE)"
                    .Cells(18, i).FormulaR1C1 = "=VLOOKUP(R[-14]C,'DATA Sch A-3'!R7C2:R20000C37,12,FALSE)"
                    .Cells(19, i).FormulaR1C1 = "=VLOOKUP(R[-15]C,'DATA Sch A-3'!R7C2:R20000C37,13,FALSE)"
                    .Cells(20, i).FormulaR1C1 = "=VLOOKUP(R[-16]C,'DATA Sch A-3'!R7C2:R20000C37,14,FALSE)"
                    .Cells(21, i).FormulaR1C1 = "=VLOOKUP(R[-17]C,'DATA Sch A-3'!R7C2:R20000C37,15,FALSE)"
                    .Cells(22, i).FormulaR1C1 = "=VLOOKUP(R[-18]C,'DATA Sch A-3'!R7C2:R20000C37,16,FALSE)"
                    .Cells(23, i).FormulaR1C1 = "=VLOOKUP(R[-19]C,'DATA Sch A-3'!R7C2:R20000C37,17,FALSE)"
                    .Cells(24, i).FormulaR1C1 = "=VLOOKUP(R[-20]C,'DATA Sch A-3'!R7C2:R20000C37,18,FALSE)"
                    .Cells(25, i).FormulaR1C1 = "=VLOOKUP(R[-21]C,'DATA Sch A-3'!R7C2:R20000C37,19,FALSE)"
                    .Cells(26, i).FormulaR1C1 = "=VLOOKUP(R[-22]C,'DATA Sch A-3'!R7C2:R20000C37,20,FALSE)"
                    .Cells(27, i).FormulaR1C1 = "=VLOOKUP(R[-23]C,'DATA Sch A-3'!R7C2:R20000C37,21,FALSE)"
                    .Cells(28, i).FormulaR1C1 = "=VLOOKUP(R[-24]C,'DATA Sch A-3'!R7C2:R20000C37,22,FALSE)"
                    .Cells(30, i).FormulaR1C1 = "=VLOOKUP(R[-26]C,'DATA Sch A-3'!R7C2:R20000C37,23,FALSE)"
                    .Cells(31, i).FormulaR1C1 = "=VLOOKUP(R[-27]C,'DATA Sch A-3'!R7C2:R20000C37,24,FALSE)"
                    .Cells(32, i).FormulaR1C1 = "=VLOOKUP(R[-28]C,'DATA Sch A-3'!R7C2:R20000C37,25,FALSE)"
                    .Cells(33, i).FormulaR1C1 = "=VLOOKUP(R[-29]C,'DATA Sch A-3'!R7C2:R20000C37,26,FALSE)"
                    .Cells(34, i).FormulaR1C1 = "=VLOOKUP(R[-30]C,'DATA Sch A-3'!R7C2:R20000C37,27,FALSE)"
                    .Cells(35, i).FormulaR1C1 = "=VLOOKUP(R[-31]C,'DATA Sch A-3'!R7C2:R20000C37,28,FALSE)"
                    .Cells(36, i).FormulaR1C1 = "=VLOOKUP(R[-32]C,'DATA Sch A-3'!R7C2:R20000C37,29,FALSE)"
                    .Cells(37, i).FormulaR1C1 = "=SUM(R[-28]C:(OFFSET(R[-1]C,0,0)))"
                    .Cells(40, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-36]C,'DATA Sch A-3'!R7C2:R20000C37,31,FALSE),0)"
                    .Cells(41, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-37]C,'DATA Sch A-3'!R7C2:R20000C37,32,FALSE),0)"
                    .Cells(42, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-38]C,'DATA Sch A-3'!R7C2:R20000C37,33,FALSE),0)"
                    .Cells(43, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-39]C,'DATA Sch A-3'!R7C2:R20000C37,34,FALSE),0)"
                    .Cells(44, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-40]C,'DATA Sch A-3'!R7C2:R20000C37,35,FALSE),0)"
                    .Cells(45, i).FormulaR1C1 = "=SUM(R[-5]C:(OFFSET(R[-2]C,0,0)))-R[-1]C"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("Schedule H")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("STOP")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C2,COLUMN()-5,0)"
                    .Cells(4, i).FormulaR1C1 = "=OFFSET('DATA Member'!R7C3,COLUMN()-5,0)"
                    .Cells(6, i).FormulaR1C1 = "=IF(R[13]C<>0,"""",""X"")"
                    .Cells(8, i).FormulaR1C1 = "=0"
                    .Cells(9, i).FormulaR1C1 = "=0"
                    .Cells(10, i).FormulaR1C1 = "=0"
                    .Cells(11, i).FormulaR1C1 = "=0"
                    .Cells(12, i).FormulaR1C1 = "=0"
                    .Cells(13, i).FormulaR1C1 = "=0"
                    .Cells(14, i).FormulaR1C1 = "=0"
                    .Cells(15, i).FormulaR1C1 = "=0"
                    .Cells(17, i).FormulaR1C1 = "=SUM(R[-9]C:(OFFSET(R[-2]C,0,0)))"
                    .Cells(18, i).FormulaR1C1 = "=IFERROR(VLOOKUP(R[-14]C,'DATA Sch A'!R12C2:R20000C83,42,FALSE),0)"
                    .Cells(19, i).FormulaR1C1 = "=R[-2]C-R[-1]C"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("NOL-PA")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("POINT")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 2
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=ManagerialMemberInfo[ns1:UnitaryID]"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$6,COLUMN()-3,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$6,COLUMN()-3,0)"
                    .Cells(6, i).Value = "=IF(VLOOKUP(R[-2]C,'DATA 500U-PA'!R3C2:R20000C7,6,FALSE)<>'NOL-PA'!R[69]C,"""",""X"")"
                    .Cells(12, i).FormulaR1C1 = "=VLOOKUP(R[-8]C,'DATA 500U-PA'!R3C2:R20000C4,3,FALSE)"
                    .Cells(15, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-11]C,'DATA 500U-PA'!C2,0))<>'NOL-PA'!R[-11]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-11]C,'DATA 500U-PA'!C2,0)))"
                    .Cells(16, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-12]C,'DATA 500U-PA'!C2,0))<>'NOL-PA'!R[-12]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-12]C,'DATA 500U-PA'!C2,0)))"
                    .Cells(18, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-14]C,'DATA 500U-PA'!C2,0)+1,1)<>'NOL-PA'!R[-14]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-14]C,'DATA 500U-PA'!C2,0)+1,1))"
                    .Cells(19, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-15]C,'DATA 500U-PA'!C2,0)+1,1)<>'NOL-PA'!R[-15]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-15]C,'DATA 500U-PA'!C2,0)+1,1))"
                    .Cells(21, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-17]C,'DATA 500U-PA'!C2,0)+2,1)<>'NOL-PA'!R[-17]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-17]C,'DATA 500U-PA'!C2,0)+2,1))"
                    .Cells(22, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-18]C,'DATA 500U-PA'!C2,0)+2,1)<>'NOL-PA'!R[-18]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-18]C,'DATA 500U-PA'!C2,0)+2,1))"
                    .Cells(24, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-20]C,'DATA 500U-PA'!C2,0)+3,1)<>'NOL-PA'!R[-20]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-20]C,'DATA 500U-PA'!C2,0)+3,1))"
                    .Cells(25, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-21]C,'DATA 500U-PA'!C2,0)+3,1)<>'NOL-PA'!R[-21]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-21]C,'DATA 500U-PA'!C2,0)+3,1))"
                    .Cells(27, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-23]C,'DATA 500U-PA'!C2,0)+4,1)<>'NOL-PA'!R[-23]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-23]C,'DATA 500U-PA'!C2,0)+4,1))"
                    .Cells(28, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-24]C,'DATA 500U-PA'!C2,0)+4,1)<>'NOL-PA'!R[-24]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-24]C,'DATA 500U-PA'!C2,0)+4,1))"
                    .Cells(30, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-26]C,'DATA 500U-PA'!C2,0)+5,1)<>'NOL-PA'!R[-26]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-26]C,'DATA 500U-PA'!C2,0)+5,1))"
                    .Cells(31, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-27]C,'DATA 500U-PA'!C2,0)+5,1)<>'NOL-PA'!R[-27]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-27]C,'DATA 500U-PA'!C2,0)+5,1))"
                    .Cells(33, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-29]C,'DATA 500U-PA'!C2,0)+6,1)<>'NOL-PA'!R[-29]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-29]C,'DATA 500U-PA'!C2,0)+6,1))"
                    .Cells(34, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-30]C,'DATA 500U-PA'!C2,0)+6,1)<>'NOL-PA'!R[-30]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-30]C,'DATA 500U-PA'!C2,0)+6,1))"
                    .Cells(36, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-32]C,'DATA 500U-PA'!C2,0)+7,1)<>'NOL-PA'!R[-32]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-32]C,'DATA 500U-PA'!C2,0)+7,1))"
                    .Cells(37, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-33]C,'DATA 500U-PA'!C2,0)+7,1)<>'NOL-PA'!R[-33]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-33]C,'DATA 500U-PA'!C2,0)+7,1))"
                    .Cells(39, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-35]C,'DATA 500U-PA'!C2,0)+8,1)<>'NOL-PA'!R[-35]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-35]C,'DATA 500U-PA'!C2,0)+8,1))"
                    .Cells(40, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-36]C,'DATA 500U-PA'!C2,0)+8,1)<>'NOL-PA'!R[-36]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-36]C,'DATA 500U-PA'!C2,0)+8,1))"
                    .Cells(42, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-38]C,'DATA 500U-PA'!C2,0)+9,1)<>'NOL-PA'!R[-38]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-38]C,'DATA 500U-PA'!C2,0)+9,1))"
                    .Cells(43, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-39]C,'DATA 500U-PA'!C2,0)+9,1)<>'NOL-PA'!R[-39]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-39]C,'DATA 500U-PA'!C2,0)+9,1))"
                    .Cells(45, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-41]C,'DATA 500U-PA'!C2,0)+10,1)<>'NOL-PA'!R[-41]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-41]C,'DATA 500U-PA'!C2,0)+10,1))"
                    .Cells(46, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-42]C,'DATA 500U-PA'!C2,0)+10,1)<>'NOL-PA'!R[-42]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-42]C,'DATA 500U-PA'!C2,0)+10,1))"
                    .Cells(48, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-44]C,'DATA 500U-PA'!C2,0)+11,1)<>'NOL-PA'!R[-44]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-44]C,'DATA 500U-PA'!C2,0)+11,1))"
                    .Cells(49, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-45]C,'DATA 500U-PA'!C2,0)+11,1)<>'NOL-PA'!R[-45]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-45]C,'DATA 500U-PA'!C2,0)+11,1))"
                    .Cells(51, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-47]C,'DATA 500U-PA'!C2,0)+12,1)<>'NOL-PA'!R[-47]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-47]C,'DATA 500U-PA'!C2,0)+12,1))"
                    .Cells(52, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-48]C,'DATA 500U-PA'!C2,0)+12,1)<>'NOL-PA'!R[-48]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-48]C,'DATA 500U-PA'!C2,0)+12,1))"
                    .Cells(54, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-50]C,'DATA 500U-PA'!C2,0)+13,1)<>'NOL-PA'!R[-50]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-50]C,'DATA 500U-PA'!C2,0)+13,1))"
                    .Cells(55, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-51]C,'DATA 500U-PA'!C2,0)+13,1)<>'NOL-PA'!R[-51]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-51]C,'DATA 500U-PA'!C2,0)+13,1))"
                    .Cells(57, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-53]C,'DATA 500U-PA'!C2,0)+14,1)<>'NOL-PA'!R[-53]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-53]C,'DATA 500U-PA'!C2,0)+14,1))"
                    .Cells(58, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-54]C,'DATA 500U-PA'!C2,0)+14,1)<>'NOL-PA'!R[-54]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-54]C,'DATA 500U-PA'!C2,0)+14,1))"
                    .Cells(60, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-56]C,'DATA 500U-PA'!C2,0)+15,1)<>'NOL-PA'!R[-56]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-56]C,'DATA 500U-PA'!C2,0)+15,1))"
                    .Cells(61, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-57]C,'DATA 500U-PA'!C2,0)+15,1)<>'NOL-PA'!R[-57]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-57]C,'DATA 500U-PA'!C2,0)+15,1))"
                    .Cells(63, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-59]C,'DATA 500U-PA'!C2,0)+16,1)<>'NOL-PA'!R[-59]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-59]C,'DATA 500U-PA'!C2,0)+16,1))"
                    .Cells(64, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-60]C,'DATA 500U-PA'!C2,0)+16,1)<>'NOL-PA'!R[-60]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-60]C,'DATA 500U-PA'!C2,0)+16,1))"
                    .Cells(66, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-62]C,'DATA 500U-PA'!C2,0)+17,1)<>'NOL-PA'!R[-62]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-62]C,'DATA 500U-PA'!C2,0)+17,1))"
                    .Cells(67, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-63]C,'DATA 500U-PA'!C2,0)+17,1)<>'NOL-PA'!R[-63]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-63]C,'DATA 500U-PA'!C2,0)+17,1))"
                    .Cells(69, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-65]C,'DATA 500U-PA'!C2,0)+18,1)<>'NOL-PA'!R[-65]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-65]C,'DATA 500U-PA'!C2,0)+18,1))"
                    .Cells(70, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-66]C,'DATA 500U-PA'!C2,0)+18,1)<>'NOL-PA'!R[-66]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-66]C,'DATA 500U-PA'!C2,0)+18,1))"
                    .Cells(72, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-68]C,'DATA 500U-PA'!C2,0)+19,1)<>'NOL-PA'!R[-68]C,0,INDEX('DATA 500U-PA'!C5,MATCH('NOL-PA'!R[-68]C,'DATA 500U-PA'!C2,0)+19,1))"
                    .Cells(73, i).FormulaR1C1 = "=IF(INDEX('DATA 500U-PA'!C2,MATCH('NOL-PA'!R[-69]C,'DATA 500U-PA'!C2,0)+19,1)<>'NOL-PA'!R[-69]C,0,INDEX('DATA 500U-PA'!C6,MATCH('NOL-PA'!R[-69]C,'DATA 500U-PA'!C2,0)+19,1))"
                    .Cells(75, i).FormulaR1C1 = "=R[-59]C+R[-56]C+R[-53]C+R[-50]C+R[-47]C+R[-44]C+R[-41]C+R[-38]C+R[-35]C+R[-32]C+R[-29]C+R[-26]C+R[-23]C+R[-20]C+R[-17]C+R[-14]C+R[-11]C+R[-8]C+R[-5]C+R[-2]C"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("Schedule A")
    With argSheet
        Set Rng = .Range(.Cells(1, 6), .Cells(1, .Columns.Count))
        Set c = Rng.Find("CODA")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 5
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$7,COLUMN()-7,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-7,0)"
                    .Cells(8, i).FormulaR1C1 = "=VLOOKUP(R[-4]C,'DATA Sch A'!R12C2:R20000C83,5,FALSE)"
                    .Cells(9, i).FormulaR1C1 = "=VLOOKUP(R[-5]C,'DATA Sch A'!R12C2:R20000C83,6,FALSE)"
                    .Cells(10, i).FormulaR1C1 = "=R[-2]C-R[-1]C"
                    .Cells(11, i).FormulaR1C1 = "=VLOOKUP(R[-7]C,'DATA Sch A'!R12C2:R20000C83,8,FALSE)"
                    .Cells(12, i).FormulaR1C1 = "=R[-2]C-R[-1]C"
                    .Cells(13, i).FormulaR1C1 = "=VLOOKUP(R[-9]C,'DATA Sch A'!R12C2:R20000C83,10,FALSE)"
                    .Cells(14, i).FormulaR1C1 = "=VLOOKUP(R[-10]C,'DATA Sch A'!R12C2:R20000C83,11,FALSE)"
                    .Cells(15, i).FormulaR1C1 = "=VLOOKUP(R[-11]C,'DATA Sch A'!R12C2:R20000C83,12,FALSE)"
                    .Cells(16, i).FormulaR1C1 = "=VLOOKUP(R[-12]C,'DATA Sch A'!R12C2:R20000C83,13,FALSE)"
                    .Cells(17, i).FormulaR1C1 = "=VLOOKUP(R[-13]C,'DATA Sch A'!R12C2:R20000C83,14,FALSE)"
                    .Cells(18, i).FormulaR1C1 = "=VLOOKUP(R[-14]C,'DATA Sch A'!R12C2:R20000C83,15,FALSE)"
                    .Cells(19, i).FormulaR1C1 = "=VLOOKUP(R[-15]C,'DATA Sch A'!R12C2:R20000C83,16,FALSE)"
                    .Cells(20, i).FormulaR1C1 = "=VLOOKUP(R[-16]C,'DATA Sch A'!R12C2:R20000C83,17,FALSE)"
                    .Cells(21, i).FormulaR1C1 = "=VLOOKUP(R[-17]C,'DATA Sch A'!R12C2:R20000C83,18,FALSE)"
                    .Cells(22, i).FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
                    .Cells(26, i).FormulaR1C1 = "=VLOOKUP(R[-22]C,'DATA Sch A'!R12C2:R20000C83,20,FALSE)"
                    .Cells(27, i).FormulaR1C1 = "=VLOOKUP(R[-23]C,'DATA Sch A'!R12C2:R20000C83,21,FALSE)"
                    .Cells(28, i).FormulaR1C1 = "=VLOOKUP(R[-24]C,'DATA Sch A'!R12C2:R20000C83,22,FALSE)"
                    .Cells(29, i).FormulaR1C1 = "=VLOOKUP(R[-25]C,'DATA Sch A'!R12C2:R20000C83,23,FALSE)"
                    .Cells(30, i).FormulaR1C1 = "=VLOOKUP(R[-26]C,'DATA Sch A'!R12C2:R20000C83,24,FALSE)"
                    .Cells(31, i).FormulaR1C1 = "=VLOOKUP(R[-27]C,'DATA Sch A'!R12C2:R20000C83,25,FALSE)"
                    .Cells(32, i).FormulaR1C1 = "=VLOOKUP(R[-28]C,'DATA Sch A'!R12C2:R20000C83,26,FALSE)"
                    .Cells(33, i).FormulaR1C1 = "=VLOOKUP(R[-29]C,'DATA Sch A'!R12C2:R20000C83,27,FALSE)"
                    .Cells(34, i).FormulaR1C1 = "=VLOOKUP(R[-30]C,'DATA Sch A'!R12C2:R20000C83,28,FALSE)"
                    .Cells(35, i).FormulaR1C1 = "=VLOOKUP(R[-31]C,'DATA Sch A'!R12C2:R20000C83,29,FALSE)"
                    .Cells(36, i).FormulaR1C1 = "=VLOOKUP(R[-32]C,'DATA Sch A'!R12C2:R20000C83,30,FALSE)"
                    .Cells(37, i).FormulaR1C1 = "=VLOOKUP(R[-33]C,'DATA Sch A'!R12C2:R20000C83,31,FALSE)"
                    .Cells(38, i).FormulaR1C1 = "=VLOOKUP(R[-34]C,'DATA Sch A'!R12C2:R20000C83,32,FALSE)"
                    .Cells(40, i).FormulaR1C1 = "=VLOOKUP(R[-36]C,'DATA Sch A'!R12C2:R20000C83,33,FALSE)"
                    .Cells(41, i).FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"
                    .Cells(42, i).FormulaR1C1 = "=R[-20]C-R[-1]C"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
    
    Set ws = Worksheets("Schedule A-5")
    With argSheet
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count))
        Set c = Rng.Find("LAST")
        If Not c Is Nothing Then
            TotalCol = c.Column
            LeftFixedCol = 3
            If TotalCol < LeftFixedCol + argColNum + 1 Then
                For i = TotalCol To LeftFixedCol + argColNum
                    .Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                    .Cells(1, i).Value = "Member" & i - LeftFixedCol
                    .Cells(2, i).Value = "=$C$2"
                    .Cells(3, i).Value = "=OFFSET('DATA Member'!$B$7,COLUMN()-5,0)"
                    .Cells(4, i).Value = "=OFFSET('DATA Member'!$C$7,COLUMN()-5,0)"
                    .Cells(11, i).Value = "=0"
                    .Cells(15, i).FormulaR1C1 = "=R15C3"
                    .Cells(16, i).FormulaR1C1 = "=R16C3"
                    .Cells(17, i).FormulaR1C1 = "=R[-1]C*HLOOKUP(R[-13]C,'Schedule J'!R4C4:R22C16384,19,FALSE)"
                    .Cells(18, i).FormulaR1C1 = "=HLOOKUP(R[-14]C,'C-Proposal'!R4C6:R22C16384,19,FALSE)"
                    .Cells(19, i).FormulaR1C1 = "=IF(R[-1]C>1000000,R[-1]C*0.025,0)"
                    .Cells(20, i).FormulaR1C1 = "=R[-3]C+R[-1]C"
                    .Cells(21, i).Value = "=0"
                Next i
                End If
            If TotalCol > LeftFixedCol + argColNum + 1 Then
                For i = TotalCol - 1 To LeftFixedCol + argColNum + 1 Step -1
                    .Columns(i).Delete
                Next i
            End If
        End If
    End With
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Nevermind figured it out

Added If ws.Visible = xlSheetVisible Then to the code and it worked
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

T
  • Question Question
Using VBA to fill in a chart
Replies
0
Views
135
Tdorman
T
K
  • Solved
Dynamic Sum Range Addresses...(?)
Replies
2
Views
472
Kenny_D
K
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
412
Sean15
Sean15
L
  • Locked
  • Question Question
How set range for chart in powerpoint created from vba
Replies
0
Views
70
Legacy 519704
L
zookeepertx
  • Question Question
Having trouble "deciphering" some code so I can see how it works
Replies
7
Views
355
zookeepertx
zookeepertx

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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
Back
Top