VBA Copy & Paste a Table as Values (Specific Columns Paste Formulas)

Hi Experts, I'm using the following code to copy and paste data from one table into another.
Sub SendforAnalysis()
  Dim TargetTable As ListObject
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")
    Range("TableFHAnalysisEngine").ClearContents
    TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
       Sheets("SetFHAnalysisData").Range("D5").PasteSpecial xlPasteValuesAndNumberFormats
       'Destination:=Sheets("SetFHAnalysisData").Range("D5")
    Application.CutCopyMode = False
    Sheets("FHAnalysisEngine").Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

I'm pasting values to minimize the number of calculated columns in the workbook; however, two the the columns I'm copying need to paste with formulas, the columns are [PreferredACCapabilityScore] and [AvailableACCapabilityScore]. How do I work that into the copy and paste code?
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Can you post a sample file (with obfuscated or "fake" data if necessary)?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I made an assumption...

is your source table for columns:  [PreferredACCapabilityScore] and [AvailableACCapabilityScore] contain formula like: TablePPCleanUORC[[#This Row],[Column1]]  ?

IF the target content location have the same row location as the source, then this will work for you:

Sheets("SetFHAnalysisData").Range("D5").PasteSpecial xlPasteFormulasAndNumberFormats

Open in new window


However, based on your requirement that the target content location started at row: 5 ("D5"), you may want to change the formula of columns:  [PreferredACCapabilityScore] and [AvailableACCapabilityScore] accordingly in your target sheet. They shouldn't use the formula that pointed back to your source sheet.
Roy CoxGroup Finance ManagerCommented:
It would be easier to check the code with an example workbook.

Assuming your existing code works then you need to add an error handler in case no SpecialCells are found.

Then copy the ListColumns you require with formula to the new data, you will need to check the column numbers. This code is untested and I'm not clear if the copied data is another Table. Test it on a file after creating a back up

Option Explicit

Sub SendforAnalysis()
    Dim TargetTable As ListObject

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")
        Range("TableFHAnalysisEngine").ClearContents
        On Error GoTo err_quit
        With TargetTable
            .DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            On Error GoTo 0
            Sheets("SetFHAnalysisData").Range("D5").PasteSpecial xlPasteValuesAndNumberFormats
            'copy Table listColumn and overwrite in new data
            .ListColumns(3).Range.Copy Sheets("SetFHAnalysisData").Range("F5")
            .ListColumns(6).Range.Copy Sheets("SetFHAnalysisData").Range("I5")
        End With
        '    Destination:=Sheets("SetFHAnalysisData").Range("D5")
clean_up:
        .CutCopyMode = False
        Sheets("FHAnalysisEngine").Activate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
err_quit:
    MsgBox "no data to copy"
    Resume clean_up
End Sub

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

-PolakAuthor Commented:
Can I refer to them by their table and header name somehow in the event that I insert columns later?
Roy CoxGroup Finance ManagerCommented:
Is the destination data formatted as a Table?
-PolakAuthor Commented:
Yes
-PolakAuthor Commented:
Roy, to clarify, I know how to paste to a Range.("Table[Column]") its the copying of the visible cells and pasting their formulas to it that are giving me the trouble. When i try:
Range("TablePPCleanUORC[[PreferredACCapabilityScore]]").Copy
            Range("TableFHAnalysisEngine[[PreferredACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

Open in new window

I end up with the whole column not the visible cells.
Roy CoxGroup Finance ManagerCommented:
That line doesn't copy visible cells only. Can you attach a dummy workbook with any sensitive data changed.
-PolakAuthor Commented:
Here's a sample workbook currently triggering a 1004 error with the code.

The two columns with the names [PreferredACCapabilityScore] and [AvailableACCapabilityScore], if successful, will paste with If statements into the table on the other worksheet.
FormulaCopyPasteProblem.xlsm
Roy CoxGroup Finance ManagerCommented:
I[ll take a look later
-PolakAuthor Commented:
Thanks... think thing is giving me fits, whenever I paste a formula into the destination table it just copies down the formula to the entire column. Def not what i would like to do. Here's the latest failed attempt
Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")
    TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Range("TableFHAnalysisEngine[[Name]]")
            TargetTable.ListColumns(58).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[PreferredACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

Open in new window

Roy CoxGroup Finance ManagerCommented:
The reason that the formulas are copying down is because it's a calculated field. To switch it off add this line of code

Application.AutoCorrect.AutoFillFormulasInLists = False

Open in new window


I'll take a proper look at the whole code later.
-PolakAuthor Commented:
Ahhhhh of course. That was so annoying. Okay so this code works as desired, but it's epicly slow. In some cases taking about  1.5 minutes to run. It runs much slower when the source table is flitered heavily so I guess that's the price we pay..... Please note that in the real workbook i'm copying Listcolumns 58-101; however here I'm just coding the first two.

I haven't included any of the With procedures or error handling that you had above just for the purpose of optimizing the approach first.  

Here's what I'd like to do, speed it up if possible (realize that WITH may help a little) and be able to express listcolumns(58) - listcolumns(101) more easily. I've done the code but its very long. Moreover, if you could select 58-101 in a range and then paste them their databody range visiblecells that would probably speed the code up considerably. Also, because the column headers are the same name in both the source and target it would be nice to refer to them by name in the copy line so that if I insert columns later it won't mess with anything.

Sub SendforAnalysis()
'Call ResetFHAnalysisEngine
  Dim TargetTable As ListObject
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.AutoCorrect.AutoFillFormulasInLists = False
    Range("TableFHAnalysisEngine").ClearContents
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")
    TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            'Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
            Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
            
            TargetTable.ListColumns(58).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[PreferredACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

            TargetTable.ListColumns(59).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[AvailableACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

           ''ALL THE WAY TO ListCoulmns(101) 
       
    Application.CutCopyMode = False
    Sheets("FHAnalysisEngine").Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.AutoCorrect.AutoFillFormulasInLists = True
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
I'll take a look this afternoon
Roy CoxGroup Finance ManagerCommented:
Check this.

I've changed the application code to detect calculation setting and reset it using lCalc.

The code should copy the block of columns and copy to the other Table
Option Explicit

Sub SendforAnalysis()
'Call ResetFHAnalysisEngine
    Dim TargetTable As ListObject
    Dim lCalc As Long

    With Application
        .ScreenUpdating = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .AutoCorrect.AutoFillFormulasInLists = False

        Range("TableFHAnalysisEngine").ClearContents

        Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")

        On Error Resume Next
        ''///change AvailableACCapabilityScore to last column name
        Range("TablePPCleanUORC[[Name]:[AvailableACCapabilityScore]]").SpecialCells(xlCellTypeVisible).Copy
        Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
        On Error GoTo 0

        .CutCopyMode = False

        Sheets("FHAnalysisEngine").Activate

        .ScreenUpdating = True
        .Calculation = lCalc
        .AutoCorrect.AutoFillFormulasInLists = True
    End With
End Sub

Open in new window

-PolakAuthor Commented:
Hi  Roy elegant code, but I think we've miscommunicatied. if you take a look back at my code and sample workbook I want the whole targettable to paste as values in the destination table EXCEPT for list columns 58-101 those i want to remain formulas. I think what's causing it to take so long is copying and pasting each list column one by one. I'm asking if we could do something about that. Pasting the entire table over with formulas is much faster, but then it boggs down the workbook more.

Working slow code:
Sub SendforAnalysis2()
'Call ResetFHAnalysisEngine
    Dim TargetTable As ListObject
    Dim lCalc As Long

    With Application
        .ScreenUpdating = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .AutoCorrect.AutoFillFormulasInLists = False

        Range("TableFHAnalysisEngine").ClearContents

        Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC")

        'On Error Resume Next
        TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy '
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
             
            TargetTable.ListColumns(58).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[PreferredACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

            TargetTable.ListColumns(59).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[AvailableACCapabilityScore]]").PasteSpecial xlPasteFormulasAndNumberFormats

''//all the way to listcolumn 101 whose column header is [QtyUH60L/M]

        'On Error GoTo 0
        .CutCopyMode = False

        Sheets("FHAnalysisEngine").Activate

        .ScreenUpdating = True
        .Calculation = lCalc
        .AutoCorrect.AutoFillFormulasInLists = True
    End With
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
Copy the whole range with formulas the copy the other columns and paste values in situ,

Use the macro Recorder to whilst selecting the columns to remove the formulas from. Paste the resulting code and I'll tidy it up
-PolakAuthor Commented:
Hmmmm, are you sure you want me to do that given that I want to copy the databody range & visible cells?

If so, to be clear, you want me to record a macro that copys my whole Table (visible cells only) then pastes that table in the destination table, then copy the columns I want as values and overwrite the columns that were just pasted as formulas ?
Roy CoxGroup Finance ManagerCommented:
Wouldn't that do what you want?
-PolakAuthor Commented:
I was just doing it in the other direction first thinking it was more logical for values to come first then formulas afterwards. One sec and i'll record that macro.
-PolakAuthor Commented:
Okay so. when doing this without VBA i learned something, if you attempt to paste formulas only (even after selecting visible cells/rows) you will paste the entire range, go head try yourself copy a range from a filtered table then paste that copied range to another table as formulas. You will paste the WHOLE range from the source table,  it's infuriating.

If you think that a better approach would be to just paste the databody range & visible cells to a destination like I am currently doing via:
TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy 
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")

Open in new window

And then place the formula I would like in each of my columns starting in liscolumns 58-101.... and then have it auto fill down, i'm beginning to think that's a better solution. The only tricky thing that I don't know how to do is after clearing the contents of the destination table, resizing the destination table so that it's only the # of rows of the copied range that's about to be pasted into it. That way when the formula fills down they will stop at the correct row.

If you still think we can do this with just copy and paste here is the recorded macro with my comments explaining each line. let me know if you need need any further clarification.
'started with a click of the mouse cursor in the diagonal orientation selecting the databodyrange of a table
    ActiveCell.Offset(2, 0).Range("A1:JG433").Select 'not sure why this started in A1, the range for TablePPCleanUORC excluding headers is D5:JJ437, last visible cell was probably in row 433, but there is most definately two columns of values past JG, [Name] to [DocumentIDValue] is the left most to right most header.
    Selection.SpecialCells(xlCellTypeVisible).Select 'making sure I only select visible/filtered rows via GoTo
    Selection.Copy 'copy those visible cells
    Sheets("SetFHAnalysisData").Select 'sheet where TableFHAnalysisEngine exists
    ActiveCell.Select 'cell D5 or if you prefer you can do an Offset of 1,0 from TableFHAnalysisEngine[[Name]]
    ActiveSheet.Paste 'I tried to paste formulas and number formats however when i did that more than just the filtered/visible selection from the source pasted so i did a normal paste, which resulted in values in the destination table
    Sheets("OpRequirements").Select 'sheet where TablePPCleanUORC resides
    ActiveWindow.SmallScroll ToRight:=50 'scrolling to the first column i'd like to select
    ActiveCell.Offset(-1, 57).Range("A1:AR433").Select 'selecting from header [PreferredACCapabilityScore] to [$UH60L/M]
    Selection.SpecialCells(xlCellTypeVisible).Select 'selecting only visable cells
    Selection.Copy
    Sheets("SetFHAnalysisData").Select 'sheet where TableFHAnalysisEngine exists
    ActiveWindow.SmallScroll ToRight:=48 'scrolling to the matching [PreferredACCapabilityScore]
    ActiveCell.Offset(-20, 54).Range("A1").Select 'one cell below the [PreferredACCapabilityScore] header aka BI5
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        'pasted formulas however with this method the more than just the visible/filtered rows are pasted the end result is larger (more rows) then my regular copy/paste of all the other fields

Open in new window

Roy CoxGroup Finance ManagerCommented:
Is there any other data on the sheet? If not, this might be an alternative.

1. Copy the whole sheet.
2. Filter and delete unwanted rows on the new sheet.
3. Copy and paste as values the cilumns that you don't want formulas in.
-PolakAuthor Commented:
Hi Roy, my objective behind this method is that an end user can filter that whole data set as desired with some linked slicers the send that data set to the TablefhAnalysisEngine and conduct their analysis on ONLY the filtered data.

That said, the size/rows of the filtered source data will change based on their selections.....How difficult would it be to resize the the destination table to be the correct size after they paste their filtered data set using the the databody range method I posted above. That method only copy's and pastes (as values) the filtered dataset. If the table was resized after the paste I could just place a formula in the first cell of each column I want as a formula and then it would flash filll down the entire data set.
Roy CoxGroup Finance ManagerCommented:
I thought that you were just moving data on a temporary basis for a specific analysis.
-PolakAuthor Commented:
I am... but all the code we've tried for Copying and Pasting that doesn't allow us to paste formulas that line up with the paste that results from.... (I cant control the filtering selections the end user makes...)
TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")

Open in new window

Any method that uses xlPasteFormulasAndNumberFormats will paste the non-visible rows too. Maybe if you see what i'm talking about in the code you realize what i'm talking about with placing a formula in the active cell... does he below make sense? (sans the problem where now autofill doesn't seem to want to work sigh).
 Sub SendforAnalysis2()
    Dim TargetTable As ListObject
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
        Range("TableFHAnalysisEngine").ClearContents
        'On Error GoTo err_quit
        With TargetTable
            Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
            
   ''///Something to resize the table to the right number of rows
            
        'On Error GoTo 0
        End With
        .AutoCorrect.AutoFillFormulasInLists = True
            Range("TableFHAnalysisEngine[[#Headers],[PreferredACCapabilityScore]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IFERROR(((INDEX(TableFHAnalysisEngine[@[AS350 with EOIR]:[UH60L/M]],MATCH([@SuggestedAsset],TableFHAnalysisEngine[[#Headers],[AS350 with EOIR]:[UH60L/M]],0))*[ApprovedHours])*((INDEX(TableFHAnalysisEngine[@[QtyAS350 with EOIR]:[QtyUH60L/M]],MATCH(CONCATENATE(""Qty"",[@SuggestedAsset]),TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]:[QtyUH60L/M]],0))))*1000)-(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH([@SuggestedAsset],TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]],0))*[ApprovedHours]),"""")"
            'the above formula will not autofill down the list why!!!!?!!?
    ''//then create this code block for each column I want a formula in
clean_up:
        .CutCopyMode = False
        'Sheets("FHAnalysisEngine").Activate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
'err_quit:
    'MsgBox "no data to copy"
    'Resume clean_up
End Sub

Open in new window

-PolakAuthor Commented:
doing a simple test in another table this code flash fills the formula down with no issues...
Range("Table12[[#Headers],[test]]").Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=1+1"

Is auto fill a table specific property that you can turn off?
Roy CoxGroup Finance ManagerCommented:
This code that I gave you earlier should stop the formulas auto filling down
-PolakAuthor Commented:
Roy, we're miss communicating. Here is what I need to do via VBA:

1. Copy and Paste the visible range of TablePPCleanUORC2 into TableFHAnalysisEngine (my above code does this correctly)
2. Resize TableFHAnalysisEngine so it its only the size of the range that was just placed inside of it
3. Place formulas in cells Offset 1,0 from the appropriate header rows
4. Have those formulas flash fill down
-PolakAuthor Commented:
Ignore my last comment if you read it, I have no idea why #4 is NOT flash filling down. Even when the destination table is correctly sized for the incomming data pasting the formula in the first cell below the header does not result in it flash filling down for the pasted databodyrange's visible cells.
-PolakAuthor Commented:
PHEW. I figured it out, still not copying down automatically, but I just learned about .Autofill Destination.

I would have never gotten there here without you; moreover, I wouldn't have realized that after pasting xlPasteFormulasAndNumberFormats that my data was misaligned, which woulda been bad.

Take a look at this code and it should clear up any confusion. If you can clean it up that would be appreciated, especially before I go create each code block to paste the appropriate formula in listcolumns 58-101.

PS I've already had a problem pasting a formula that was too long how do I join/concatenate two parts in the activecell.formula ?
PPS I doubt this will run very fast once I have all the formulas in there, any way to speed it up?

Sub SendforAnalysis2()
    Dim TargetTable As ListObject
    Dim DestinationTable As ListObject
    Dim NumBlanks As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        Range("TableFHAnalysisEngine").ClearContents

       'Size Table so it can Recieve the largest (unfiltered) dataset possible with current data
        Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").Resize Range("$D$4:$JJ$1300")

       'Copy and paste Filtered Data from Source table to destination table
       Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
       With TargetTable
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
       End With
       
       'Delete Unused Table Rows after Paste
             With Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").DataBodyRange
             'On Error Resume Next
             NumBlanks = .Columns(1).SpecialCells(xlBlanks).Count
             'On Error GoTo 0
                 If NumBlanks > 0 Then
             .Rows(.Rows.Count - NumBlanks).Resize(NumBlanks).Delete
                 End If
             End With
       'Paste forumlas in columns that you want to be calculated
     
       'Sheets("SetFHAnalysisData").Activate
        Set DestinationTable = Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine")
        With DestinationTable
            Range("TableFHAnalysisEngine[[#Headers],[PreferredACCapabilityScore]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IFERROR(((INDEX(TableFHAnalysisEngine[@[AS350 with EOIR]:[UH60L/M]],MATCH([@SuggestedAsset],TableFHAnalysisEngine[[#Headers],[AS350 with EOIR]:[UH60L/M]],0))*[ApprovedHours])*((INDEX(TableFHAnalysisEngine[@[QtyAS350 with EOIR]:[QtyUH60L/M]],MATCH(CONCATENATE(""Qty"",[@SuggestedAsset]),TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]:[QtyUH60L/M]],0))))*1000)-(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH([@SuggestedAsset],TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]],0))*[ApprovedHours]),"""")"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[PreferredACCapabilityScore]")
       End With
              
    ''//then create this code block for each column I want a formula in
clean_up:
        .CutCopyMode = False
        'Sheets("FHAnalysisEngine").Activate
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
'err_quit:
    'MsgBox "no data to copy"
    'Resume clean_up
End Sub

Open in new window

-PolakAuthor Commented:
Learned something new again.... If the source table is not filtered then the
DataBodyRange (xlCellTypeVisible).Copy _ 
Destination:=Range("TableFHAnalysisEngine[[Name]]")

Open in new window

method WILL paste formulas. If the source table is filtered it will paste values. As such I had to include an If statement in my code to check so as to not paste formulas in ALL the columns. I've also done the annoying process of adding all of my formulas to the code. If you would like to clean it up a bit, this is as it stands right now:
Sub SendforAnalysis2()
MsgBox "This will take 30secs - 1min to process, hang in there!", vbInformation + vbOKCancel, "Sit Tight"
'Call ResetFHAnalysisEngine
    Dim TargetTable As ListObject
    Dim DestinationTable As ListObject
    Dim NumBlanks As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        Range("TableFHAnalysisEngine").ClearContents

       'Size Table so it can Recieve the largest (unfiltered) dataset possible with current data
        Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").Resize Range("$D$4:$JR$1300")

       'Copy and paste Filtered Data from Source table to destination table
    
    If Sheets("OpRequirements").ListObjects("TablePPCleanUORC2").AutoFilter.FilterMode = True Then
    MsgBox "Yes filters are applied to the source table"
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
       With TargetTable
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
            Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
       End With
    Else
       MsgBox "No filters are applied to the source table"
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
       With TargetTable
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
        End With
    End If
       
       'Delete Unused Table Rows after Paste
             With Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").DataBodyRange
             'On Error Resume Next
             NumBlanks = .Columns(1).SpecialCells(xlBlanks).Count
             'On Error GoTo 0
                 If NumBlanks > 0 Then
             .Rows(.Rows.Count - NumBlanks).Resize(NumBlanks + 1).Delete
                 End If
             End With
       'Paste forumlas in columns that you want to be calculated
        
        Sheets("SetFHAnalysisData").Activate
        Set DestinationTable = Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine")
        With DestinationTable
           Range("TableFHAnalysisEngine[[#Headers],[PreferredACCapabilityScore]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IFERROR(((INDEX(TableFHAnalysisEngine[@[AS350 with EOIR]:[UH60L/M]],MATCH([@SuggestedAsset],TableFHAnalysisEngine[[#Headers],[AS350 with EOIR]:[UH60L/M]],0))*[ApprovedHours])*((INDEX(TableFHAnalysisEngine[@[QtyAS350 with EOIR]:[QtyUH60L/M]],MATCH(CONCATENATE(""Qty"",[@SuggestedAsset]),TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]:[QtyUH60L/M]],0))))*1000)-(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH([@SuggestedAsset],TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]],0))*[ApprovedHours]),"""")"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[PreferredACCapabilityScore]")
        
            Range("TableFHAnalysisEngine[[#Headers],[AvailableACCapabilityScore]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IFERROR(((INDEX(TableFHAnalysisEngine[@[AS350 with EOIR]:[UH60L/M]],MATCH([@AvailableAsset],TableFHAnalysisEngine[[#Headers],[AS350 with EOIR]:[UH60L/M]],0))*[ApprovedHours])*((INDEX(TableFHAnalysisEngine[@[QtyAS350 with EOIR]:[QtyUH60L/M]],MATCH(CONCATENATE(""Qty"",[@AvailableAsset]),TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]:[QtyUH60L/M]],0))))*1000)-(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH([@SuggestedAsset],TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]],0))*[ApprovedHours]),"""")"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[AvailableACCapabilityScore]")
'''''''''''''''
            Range("TableFHAnalysisEngine[[#Headers],[mSUMHrsAvailAC]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=(SUMIF(TableFHAnalysisSummary[Aircraft Type],[@AvailableAsset],TableFHAnalysisSummary[Annual FH]))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[mSUMHrsAvailAC]")

            Range("TableFHAnalysisEngine[[#Headers],[mSUMHrsPrefAC]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=(SUMIF(TableFHAnalysisSummary[Aircraft Type],[@SuggestedAsset],TableFHAnalysisSummary[Annual FH]))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[mSUMHrsPrefAC]")

            Range("TableFHAnalysisEngine[[#Headers],[AvailACHrsPercent]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=[ApprovedHours]/[@SUMHrsAvailAC]"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[AvailACHrsPercent]")
            
            Range("TableFHAnalysisEngine[[#Headers],[PreferACHrsPercent]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=[ApprovedHours]/[@SUMHrsPrefAC]"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[PreferACHrsPercent]")
        
            Range("TableFHAnalysisEngine[[#Headers],[mHrsAvailAC]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=[mSUMHrsAvailAC]*([@AvailACHrsPercent])"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[mHrsAvailAC]")

            Range("TableFHAnalysisEngine[[#Headers],[mHrsPrefAC]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=[@mSUMHrsPrefAC]*[@PreferACHrsPercent]"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[mHrsPrefAC]")
'''''''''CPFH
            Range("TableFHAnalysisEngine[[#Headers],[$AS350 with EOIR]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$AS350 with EOIR]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$AS350 with EOIR]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$AS350 with EOIR]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$B200]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$B200]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$B200]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$B200]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$B350]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$B350]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$B350]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$B350]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C12]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C12]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C12]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C12]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C206]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C206]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C206]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C206]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C206 Night Owl]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C206 Night Owl]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C206 Night Owl]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C206 Night Owl]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C210]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C210]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C210]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C210]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C550]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C550]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C550]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C550]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$C550 3B]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C550 3B]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$C550 3B]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$C550 3B]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$DHC8]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$DHC8]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$DHC8]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$DHC8]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$EC120]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$EC120]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$EC120]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$EC120]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$P3 AEW]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$P3 AEW]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$P3 AEW]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$P3 AEW]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$P3 LRT]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$P3 LRT]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$P3 LRT]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$P3 LRT]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$PC12]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$PC12]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$PC12]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$PC12]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$S76]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$S76]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$S76]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$S76]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UAS]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$UAS]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID(TableFHAnalysisEngine[[#Headers],[$UAS]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UAS]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UAS Guardian]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UAS Guardian]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UAS Guardian]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UAS Guardian]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UH1]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH1]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH1]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UH1]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UH1N]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH1N]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH1N]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UH1N]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UH60A]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH60A]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH60A]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UH60A]")
            
            Range("TableFHAnalysisEngine[[#Headers],[$UH60L/M]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF((INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH60L/M]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]])))=0,99999999,(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH(MID([[#Headers],[$UH60L/M]],2,99),TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]]))))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[$UH60L/M]")
'''''''''Qty
            Range("TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyAS350 with EOIR]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyAS350 with EOIR]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyAS350 with EOIR]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyAS350 with EOIR]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyB200]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB200]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB200]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB200]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyB200]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyB350]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB350]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB350]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyB350]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyB350]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC12]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC12]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC12]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC12]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC12]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC206]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC206]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC206 Night Owl]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206 Night Owl]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206 Night Owl]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC206 Night Owl]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC206 Night Owl]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC210]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC210]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC210]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC210]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC210]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC550]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC550]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyC550 3B]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550 3B]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550 3B]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyC550 3B]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyC550 3B]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyDHC8]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyDHC8]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyDHC8]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyDHC8]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyDHC8]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyEC120]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyEC120]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyEC120]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyEC120]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyEC120]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyP3 AEW]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 AEW]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 AEW]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 AEW]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyP3 AEW]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyP3 LRT]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 LRT]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 LRT]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyP3 LRT]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyP3 LRT]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyPC12]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyPC12]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyPC12]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyPC12]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyPC12]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyS76]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyS76]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyS76]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyS76]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyS76]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUAS]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUAS]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUAS Guardian]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS Guardian]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS Guardian]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUAS Guardian]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUAS Guardian]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUH1]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUH1]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUH1N]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1N]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1N]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH1N]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUH1N]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUH60A]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60A]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60A]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60A]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUH60A]")
            
            Range("TableFHAnalysisEngine[[#Headers],[QtyUH60L/M]]").Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.Formula = "=IF(SelectedAircraftInventory=""Current Inventory"", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60L/M]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)), IF(SelectedAircraftInventory=""Modified Inventory"", INDEX(TableModifiedACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableModifiedACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60L/M]],4,99),TableModifiedACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),IF(SelectedAircraftInventory="""", INDEX(TableCurrentACInventory[[AS350 with EOIR]:[UH60L/M]], MATCH([@[OpLocationCode]:[OpLocationCode]],TableCurrentACInventory[[OL Code]:[OL Code]],0), MATCH(MID([[#Headers],[QtyUH60L/M]],4,99),TableCurrentACInventory[[#Headers],[AS350 with EOIR]:[UH60L/M]],0)),"""")))"
            Selection.AutoFill Destination:=Range("TableFHAnalysisEngine[QtyUH60L/M]")
             
        End With
clean_up:
        .CutCopyMode = False
        
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        Sheets("FHAnalysisEngine").Activate
    End With
'err_quit:
    'MsgBox "no data to copy"
    'Resume clean_up
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
I'm not sure why the formulas should need replacing, I haven't experience this before.

When you use a With Statement you need to place a stop before each reference to the sheet and you do not usually need to activate it or select it. See below

'        Sheets("SetFHAnalysisData").Activate
        Set DestinationTable = Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine")
        With DestinationTable
           .Range("TableFHAnalysisEngine[[#Headers],[PreferredACCapabilityScore]]").Select

Open in new window


Also try  not selecting the cell to put the formula in. Not selecting the formula cells each time will save some time

 'Paste forumlas in columns that you want to be calculated

        '        Sheets("SetFHAnalysisData").Activate
        Set DestinationTable = Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine")
        With DestinationTable
            'ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Cells(2, 1).Select
            qith .Range("TableFHAnalysisEngine[[PreferredACCapabilityScore]]").Range.Cells(2, 1)
            .Formula = _
                "=IFERROR(((INDEX(TableFHAnalysisEngine[@[AS350 with EOIR]:[UH60L/M]],MATCH([@SuggestedAsset],TableFHAnalysisEngine[[#Headers],[AS350 with EOIR]:[UH60L/M]],0))*[ApprovedHours])*((INDEX(TableFHAnalysisEngine[@[QtyAS350 with EOIR]:[QtyUH60L/M]],MATCH(CONCATENATE(""Qty"",[@SuggestedAsset]),TableFHAnalysisEngine[[#Headers],[QtyAS350 with EOIR]:[QtyUH60L/M]],0))))*1000)-(INDEX(TableFHAnalysisSummary[[CPFH]:[CPFH]],MATCH([@SuggestedAsset],TableFHAnalysisSummary[[Aircraft Type]:[Aircraft Type]],0))*[ApprovedHours]),"""")"
            .AutoFill Destination:=Range("TableFHAnalysisEngine[PreferredACCapabilityScore]")
        End With

Open in new window

-PolakAuthor Commented:
10-4, I'll try the above in the morning.

As a quick note I'm not sure the above will help with any of that, but for whatever reason the Last Row of data doesn't make it over during the paste, this happens regardless of if there is filtering or not. I have tried to adjust the delete unused table rows code by +1 and -1 to NumBlanks. But it happens regardless.
Roy CoxGroup Finance ManagerCommented:
Check out Resize.

Also, you use a With Statement but still use the ListObject name


 
 'Copy and paste Filtered Data from Source table to destination table
        Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
        If Sheets("OpRequirements").ListObjects("TablePPCleanUORC2").AutoFilter.FilterMode = True Then
            MsgBox "Yes filters are applied to the source table"
            With TargetTable
                .DataBodyRange.SpecialCells(xlCellTypeVisible).Resize(.DataBodyRange.ListRows.Count, .DataBodyRange.ListColumns.Count).Copy _
                        Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
            End With
        Else
            MsgBox "No filters are applied to the source table"
            With TargetTable
                 .DataBodyRange.SpecialCells(xlCellTypeVisible).Resize(.DataBodyRange.ListRows.Count, .DataBodyRange.ListColumns.Count).Copy
                Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
            End With
        End If

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
"I'm not sure why the formulas should need replacing, I haven't experience this before."
In a nutshell; When using .Copy _ Destination:= if the source selection is filtered the destination will paste only as values. If the source selection is not filtered .Copy_ Destination:= will paste with formulas. If you attempt to force xlPasteFormulas you will lose your filtering and paste all data.

"When you use a With Statement you need to place a stop before each reference to the sheet and you do not usually need to activate it or select it. See below"
That what I thought.... except without the sheet activated it triggers the following....Capture.JPG"Check Out Resize"
I got this with that code Capture2.JPG However I did take a closer look at what NumBlanks was doing and I got it to work by using..
Rows(.Rows.Count - (NumBlanks - 1)).Resize(NumBlanks).Delete

Open in new window

Okay now you ready for the behavior that I can't explain and has got me flat out stumped...........

I've commented out the now working resize code and my formula pasting code. SOMETIMES when dealing with a filtered selection/copy and a destination table size of 1300 rows the paste will loop, until all 1300 rows have been exhausted / the number of rows in the selection. This happens with a source selection of 16 rows. If I decrease the size of the destination table to 450 rows the looping of the paste happens however less frequently. If I decrease the size of the destination table to 50 rows it never happens.

What's causing the lopping behavior?
-PolakAuthor Commented:
Okay you're going to think I'm crazy .....

So the only commonality in the looping scenarios was two separate filtered sets each had 16 rows of data. So I thought a little deeper about that.....

Look at what i'm setting my table size to before the paste $D$4:$JR$1300. 1300-4 is 1296. You know what goes evenly into 1296... 16. So I played with that a little bit. I filtered the source table so it was equal to 7 rows. Then set the destination table size to $D$4:$JR$18. 18-4 is 14, and 7*2 is 14. As such the data will duplicate twice. Set the destination table size equal to 1299/1301 or 17/19 in these two test and the looping behavior stop.

Whenever the size of the source copy will evenly fit into the rowmin-rowmax the data will duplicate.

Yes, this was extremely hard to debug.
-PolakAuthor Commented:
For right now I've set the max table size to $D$4:$JR$453; 453-4 is equal to a prime number 449. As prime numbers are only divisible by themselves and 1 (I rarely have a filtering scenario where there is 1 row in the source table and there is a max of 433 rows of data for now) this solves my problem until i can figure out how to resize the table by the size of the COPIED range.
-PolakAuthor Commented:
Update...
I've managed some code that find the size of the copied source and sets the size of the destination table equal to it.

What do you think? Also, how weird is that looping problem! Code up until the formula pastes below:
Sub SendforAnalysis2()
MsgBox "This will take around 30secs to process.", vbInformation + vbOKOnly, "Sit Tight"
'Call ResetFHAnalysisEngine
    Dim TargetTable As ListObject
    Dim DestinationTable As ListObject
    Dim NumBlanks As Long
    Dim NumNonBlanks As Long
    Dim TableResizeRange As String
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .CutCopyMode = False
        Range("TableFHAnalysisEngine").ClearContents

       
       'Determine the size of the table, then rezie the table
       'No longer have to run the "Delete Unused Table Rows after Paste" code
        NumNonBlanks = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2").Range.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 + 4 'minus one header row and offest four rows
        TableResizeRange = "$D$4:$JR$" & NumNonBlanks
        Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").Resize Range(TableResizeRange)
        
        'Size Table so it can Recieve the largest (unfiltered) dataset possible with current data
        'Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").Resize Range("$D$4:$JR$453")

       'Copy and paste Filtered Data from Source table to destination table

    
    If Sheets("OpRequirements").ListObjects("TablePPCleanUORC2").AutoFilter.FilterMode = True Then
        MsgBox "Yes filters are applied to the source table"
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
       With TargetTable
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy '_
            'Destination:=Sheets("SetFHAnalysisData").Range("TableFHAnalysisEngine[[Name]]")
            Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
       End With
    Else
       MsgBox "No filters are applied to the source table"
    Set TargetTable = Sheets("OpRequirements").ListObjects("TablePPCleanUORC2")
       With TargetTable
            TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
            Range("TableFHAnalysisEngine[[Name]]").PasteSpecial xlPasteValuesAndNumberFormats
        End With
    End If
       
       'Delete Unused Table Rows after Paste
'             With Sheets("SetFHAnalysisData").ListObjects("TableFHAnalysisEngine").DataBodyRange
'             On Error Resume Next
'             NumBlanks = .Columns(1).SpecialCells(xlBlanks).Count
'             On Error GoTo 0
'                 If NumBlanks > 0 Then
'             .Rows(.Rows.Count - (NumBlanks - 1)).Resize(NumBlanks).Delete
'                 End If
'             End With
      
       'Paste forumlas in columns that you want to be calculated

Open in new window

Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.