Loop Based on Table Contents

JustinJ

New Member
Joined
Sep 19, 2020
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,719
Members
414,401
Latest member
grenona2020

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