Auto fill column in a table

[narrowgate88]

When I recorded this macro, I was working with a list that only had 60 rows. The lists vary, so I need to modify the macro to autofill all necessary rows. What do I need to change? It's in the section for addign a grand total column.

Private Sub cmdOK_Click()
    
Dim Location, Month, Year As String
Dim c As Range


Dim TotalRows, I As Long

Application.ScreenUpdating = False
' ================================================================
' The following code determines which txt file to open and modify.
' ================================================================

Year = cboYear.Value

If cboLocation.Value = "The Christian Village" Then
    Location = "TCV"
    ElseIf cboLocation.Value = "Fair Havens Christian Home" Then
    Location = "FHCH"
    ElseIf cboLocation.Value = "Pleasant Meadows Christian Village" Then
    Location = "PMCV"
    ElseIf cboLocation.Value = "Lewis Memorial Christian Village" Then
    Location = "LMCV"
    ElseIf cboLocation.Value = "LMCV Garden Villas" Then
    Location = "LMCVSH"
    ElseIf cboLocation.Value = "Hoosier Christian Village" Then
    Location = "HCV"
    ElseIf cboLocation.Value = "Wabash Christian Retirement Center" Then
    Location = "WCRC"
    ElseIf cboLocation.Value = "Shawnee Christian Nursing Center" Then
    Location = "SCNC"
    ElseIf cboLocation.Value = "Washington Christian Village" Then
    Location = "WCV"
    ElseIf cboLocation.Value = "Spring River Christian Village" Then
    Location = "SRCV"
    ElseIf cboLocation.Value = "Chicagoland Christian Village" Then
    Location = "CCV"
    ElseIf cboLocation.Value = "Risen Son Christian Village" Then
    Location = "RSCV"
    ElseIf cboLocation.Value = "Heartland Christian Village" Then
    Location = "HLCV"
    ElseIf cboLocation.Value = "Hickory Point Christian Village" Then
    Location = "HPCV"
    ElseIf cboLocation.Value = "Bridgeway Christian Village" Then
    Location = "BCV"
End If

If cboMonth.Value = "January" Then
    Month = "01"
    ElseIf cboMonth.Value = "February" Then
    Month = "02"
    ElseIf cboMonth.Value = "March" Then
    Month = "03"
    ElseIf cboMonth.Value = "April" Then
    Month = "04"
    ElseIf cboMonth.Value = "May" Then
    Month = "05"
    ElseIf cboMonth.Value = "June" Then
    Month = "06"
    ElseIf cboMonth.Value = "July" Then
    Month = "07"
    ElseIf cboMonth.Value = "August" Then
    Month = "08"
    ElseIf cboMonth.Value = "September" Then
    Month = "09"
    ElseIf cboMonth.Value = "October" Then
    Month = "10"
    ElseIf cboMonth.Value = "November" Then
    Month = "11"
    ElseIf cboMonth.Value = "December" Then
    Month = "12"
End If
' ===============================================================
' The following code opens the txt file and converts it to excel.
' ===============================================================
    Workbooks.OpenText Filename:= _
        "I:\Dept\Accounting\Facility AR Reconciliations\AR Exports\" & Location & "_" & Year & "" & Month & ".TXT" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), _
        Array(2, 3), Array(3, 1), Array(4, 9), Array(5, 1), Array(6, 9), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 9)), TrailingMinusNumbers:=True
' =============================================
' The following code adds a grand total column.
' =============================================
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("g2:g60")
' ================================================
' The following code formats the currency columns.
' ================================================
    Columns("E:G").Select
    Selection.Style = "Comma"
' ================================================
' The following code converts the data to a table.
' ================================================
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$60"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
        Range("Table1[[#Headers],[Effective Date]]").Select
' ==================================================
' The following code adds a column for the Location.
' ==================================================
    Selection.ListObject.ListColumns.Add Position:=1
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "" & Location & ""
    Range("A2").Select
    Selection.AutoFill Destination:=Range("Table1[Location]")
    Range("Table1[Location]").Select
' =======================================================
' The following code modifies the table view for Billing.
' =======================================================
    Range("Table1[[#Headers],[Effective Date]]").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("Table1[[#Headers],[Account Number]]").Select
    ActiveCell.FormulaR1C1 = "Account"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = True
    Columns("F:G").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
' ==============================================================
' The following code deletes all rows with a "Total" Value of 0.
' ==============================================================
'    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:="-"
'    Application.DisplayAlerts = False
'    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
'    Application.DisplayAlerts = True
'    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
'    Range("Table1[[#Headers],[Location]]").Select
' ==========================================
' The following code prints the spreadsheet.
' ==========================================
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
' =================================================
' The following code saves the file for Accounting.
' =================================================
    ChDir "I:\Dept\Accounting\Facility AR Reconciliations\AR Import Files"
    ActiveWorkbook.SaveAs Filename:= _
        "I:\Dept\Accounting\Facility AR Reconciliations\AR Import Files\" & Location & "_" & Year & "" & Month & ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' =======================================
' The following code closes the UserForm.
' =======================================
    Unload Me
    Application.ScreenUpdating = True

End Sub

[Marcol]

I can't test this for obvious reasons

However try this

Private Sub cmdOK_Click()
    Dim Location As String, Month As String, Year As String
    
    'Application.ScreenUpdating = False
    ' ================================================================
    ' The following code determines which txt file to open and modify.
    ' ================================================================
    
    Year = cboYear.Value
    
    Select Case cboLocation.Value
        Case "The Christian Village"
            Location = "TCV"
        Case "Fair Havens Christian Home"
            Location = "FHCH"
        Case "Pleasant Meadows Christian Village"
            Location = "PMCV"
        Case "Lewis Memorial Christian Village"
            Location = "LMCV"
        Case "LMCV Garden Villas"
            Location = "LMCVSH"
        Case "Hoosier Christian Village"
            Location = "HCV"
        Case "Wabash Christian Retirement Center"
            Location = "WCRC"
        Case "Shawnee Christian Nursing Center"
            Location = "SCNC"
        Case "Washington Christian Village"
            Location = "WCV"
        Case "Spring River Christian Village"
            Location = "SRCV"
        Case "Chicagoland Christian Village"
            Location = "CCV"
        Case "Risen Son Christian Village"
            Location = "RSCV"
        Case "Heartland Christian Village"
            Location = "HLCV"
        Case "Hickory Point Christian Village"
            Location = "HPCV"
        Case "Bridgeway Christian Village"
            Location = "BCV"
    End Select
    
    Select Case cboMonth.Value
        Case "January"
            Month = "01"
        Case "February"
            Month = "02"
        Case "March"
            Month = "03"
        Case "April"
            Month = "04"
        Case "May"
            Month = "05"
        Case "June"
            Month = "06"
        Case "July"
            Month = "07"
        Case "August"
            Month = "08"
        Case "September"
            Month = "09"
        Case "October"
            Month = "10"
        Case "November"
            Month = "11"
        Case "December"
            Month = "12"
    End Select
    ' ===============================================================
    ' The following code opens the txt file and converts it to excel.
    ' ===============================================================
        Workbooks.OpenText Filename:= _
            "I:\Dept\Accounting\Facility AR Reconciliations\AR Exports\" & Location & "_" & Year & "" & Month & ".TXT" _
            , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), _
            Array(2, 3), Array(3, 1), Array(4, 9), Array(5, 1), Array(6, 9), Array(7, 1), Array(8, 1), _
            Array(9, 1), Array(10, 9)), TrailingMinusNumbers:=True
    ' =============================================
    ' The following code adds a grand total column.
    ' =============================================
        Dim LastRow As Long
        
        Range("G1") = "Total"
        Range("G2").FormulaR1C1 = "=RC[-2]-RC[-1]"
        LastRow = Range("G" & Rows.Count).End(xlUp).Row
        Range("G2").AutoFill Destination:=Range("G2:G" & LastRow)
    ' ================================================
    ' The following code formats the currency columns.
    ' ================================================
        Columns("E:G").Style = "Comma"
    ' ================================================
    ' The following code converts the data to a table.
    ' ================================================
        ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$" & LastRow), , xlYes).Name = _
            "Table1"
        Range("Table1[#All]").Select
            Range("Table1[[#Headers],[Effective Date]]").Select
    ' ==================================================
    ' The following code adds a column for the Location.
    ' ==================================================
        Selection.ListObject.ListColumns.Add Position:=1
        Range("Table1[[#Headers],[Column1]]") = "Location"
        Range("A2").FormulaR1C1 = "" & Location & ""
        Range("A2").AutoFill Destination:=Range("Table1[Location]")
        Range("Table1[Location]").Select
    ' =======================================================
    ' The following code modifies the table view for Billing.
    ' =======================================================
        Range("Table1[[#Headers],[Effective Date]]").Select
        ActiveCell.FormulaR1C1 = "Date"
        Range("Table1[[#Headers],[Account Number]]").Select
        ActiveCell.FormulaR1C1 = "Account"
        Cells.EntireColumn.AutoFit
        Range("C:C,E:G").EntireColumn.Hidden = True
        Range("A1").Select
    ' ==============================================================
    ' The following code deletes all rows with a "Total" Value of 0.
    ' ==============================================================
    '    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:="-"
    '    Application.DisplayAlerts = False
    '    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    '    Application.DisplayAlerts = True
    '    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
    '    Range("Table1[[#Headers],[Location]]").Select
    ' ==========================================
    ' The following code prints the spreadsheet.
    ' ==========================================
        ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    ' =================================================
    ' The following code saves the file for Accounting.
    ' =================================================
        ChDir "I:\Dept\Accounting\Facility AR Reconciliations\AR Import Files"
        ActiveWorkbook.SaveAs Filename:= _
            "I:\Dept\Accounting\Facility AR Reconciliations\AR Import Files\" & Location & "_" & Year & "" & Month & ".xlsm" _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    ' =======================================
    ' The following code closes the UserForm.
    ' =======================================
    'Application.ScreenUpdating = True
    
    Unload Me

End Sub

I have tried to speed your code up a little.
I hope I have not been over zealous.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[narrowgate88]

I received a run-time error '1004': Autofill method of range class failed.

[narrowgate88]

I do like some of the streamlining that you did. I had thought about using Select Case instead of Ifs, but wasn't sure how it would affect processing speeds. I appreciate the removal of the range selections. I hadn't thought about doing that.

[Marcol]

We need a column that has previously been filled by your code (This will be of variable length) to base LastRow on

In the meantime try changing this line

LastRow = Range("G" & Rows.Count).End(xlUp).Row

to

LastRow = ActiveSheet.UsedRange.Rows.Count

Let me know how it runs after that
I have some suspicions on other lines.

Cheers

[narrowgate88]

Worked perfectly.