Loop Based on Table Contents

[JustinJ]

Hi Everyone,
I created the macro below to extract a single data set based on user input for 'and-on' usage. I would like to create a looped version to capture all the historical data sets. There are more than one thousand historical data sets (denoted by number, 1-1000) but I cannot guarantee that each number exists in the table. I would like for the inputbox to be replaced and instead use column C of my table to loop through all the historical data sets.

Sub LoopNewLNWorkbook()

    'This macro creates a user defined line number workbook from queried data within this workbook and saves in a predetermined location

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        '.Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With

    Workbooks.Add
    Dim LineNumber As Variant
    LineNumber = InputBox("Input Line Number to Analyze")

    Dim sFolder As String
    sFolder = "C:\Users\nc541c\Documents\10_Business\10_BCA\787\FBJ\Filler Data Collect Analysis\Line Number Reports"

    Dim FName As String
    FName = sFolder & "\" & LineNumber & ".xlsx"

    Sheets.Add(After:=Sheets(1)).Name = "FWD LH"
    Sheets.Add(After:=Sheets(2)).Name = "FWD RH"
    Sheets.Add(After:=Sheets(3)).Name = "AFT LH"
    Sheets.Add(After:=Sheets(4)).Name = "AFT RH"
    Sheets(1).Delete
    ActiveWorkbook.SaveAs FileName:=FName

    Dim Source As String
    Source = "Filler Data Collect Query.xlsm"
    ThisWorkbook.Activate
    Sheets(1).Unprotect Password:=Range("F3")
    Sheets(1).Range("B8") = LineNumber
    Range("A1").Select
    Sheets(1).Protect Password:=Range("F3")

    'Sheet2 Copy values
    Sheets(2).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks(LineNumber & ".xlsx").Activate
    Sheets(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .EntireColumn.AutoFit
    End With
    Range("A1").Select

    'Sheet3 Copy values
    Workbooks(Source).Activate
    Sheets(3).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks(LineNumber & ".xlsx").Activate
    Sheets(2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .EntireColumn.AutoFit
    End With
    Range("A1").Select

    'Sheet4 Copy values
    Workbooks(Source).Activate
    Sheets(4).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks(LineNumber & ".xlsx").Activate
    Sheets(3).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .EntireColumn.AutoFit
    End With
    Range("A1").Select

    'Sheet5 Copy values
    Workbooks(Source).Activate
    Sheets(5).Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks(LineNumber & ".xlsx").Activate
    Sheets(4).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .EntireColumn.AutoFit
    End With
    Range("A1").Select

    ActiveWorkbook.Sheets(1).Select
    ActiveWorkbook.Close True

    Workbooks(Source).Activate
    Sheets(5).Select
    Range("A1").Select
    Sheets(4).Select
    Range("A1").Select
    Sheets(3).Select
    Range("A1").Select
    Sheets(2).Select
    Range("A1").Select
    Sheets(1).Select
    Range("A1").Select

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        '.Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With

End Sub