VBA or Power Query help: Iterate copy/paste from one sheet to another and save each iteration

Antonino2023 

With out opening any file (for own security reasons at the moment) maybe you can replace the line inputRow.Range.Copy tblModel.DataBodyRange.Rows(1) with the following code:

inputRow.Range.Copy
tblModel.DataBodyRange.Rows(1).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

This code copies the range from inputRow.Range and then pastes only the values into tblModel.DataBodyRange.Rows(1) using the PasteSpecial method. The Application.CutCopyMode = False line is added to clear the clipboard.

Regarding the speed optimization, here are a few suggestions:

1. Disable screen updating and events: Add Application.ScreenUpdating = False and Application.EnableEvents = False before the loop and set them back to True after the loop completes. This prevents unnecessary screen refreshing and event triggering during the execution of the loop.
2. Use arrays for data manipulation: Load the input range and destination range into arrays, perform the necessary operations in memory, and then write the updated arrays back to the worksheet. Working with arrays can significantly improve the performance compared to reading and writing cell-by-cell.
3. Minimize interactions with the worksheet: Instead of repeatedly clearing the contents of tables or individual ranges, consider resizing and overwriting existing data directly.
4. Use efficient methods for deleting blank rows: If you need to delete blank rows, consider using advanced filtering or the AutoFilter method to identify and delete the blank rows in a single operation, rather than row-by-row deletion.

Remember to thoroughly test any modifications to ensure they produce the desired results and maintain data integrity.

These suggestions are general performance improvement techniques that can be applied to various scenarios.

I think I got it! I referenced this link: https://stackoverflow.com/questions/24294923/how-to-copy-only-values-in-excel-vba-from-a-range 

I used the PasteSpecial xlPasteValues function as shown in the modified code below:

Also, please use this new file to test it if you would like. Let me know if it does not let you make a downloadable copy of it.

Sub IterateAndCopyPaste_V2()
    Dim wb As Workbook
    Dim wsInputs As Worksheet
    Dim wsAnalysis As Worksheet
    Dim wsSummaries As Worksheet
    Dim tblInputs As ListObject
    Dim tblModel As ListObject
    Dim tblAnalysis As ListObject
    Dim tblSummaries As ListObject
    Dim inputRow As ListRow
    Dim analysisRow As ListRow
    Dim lastRow As Long
    Dim i As Long
    Dim rSource As Range 'Source Range
    Dim rDest As Range   'Target Range
    
    ' Temporary ovewrite value
    Dim Temp As String
    Temp = "Temp"
    
    ' Set references to the workbook and worksheets
    Set wb = ThisWorkbook
    Set wsInputs = wb.Worksheets("Inputs")
    Set wsAnalysis = wb.Worksheets("Analysis")
    Set wsSummaries = wb.Worksheets("Summaries")
    
    ' Set references to the tables
    Set tblInputs = wsInputs.ListObjects("Inputs_T")
    Set tblModel = wsAnalysis.ListObjects("Model_T")
    Set tblAnalysis = wsAnalysis.ListObjects("Analysis_T")
    Set tblSummaries = wsSummaries.ListObjects("Summaries_T")
    
    ' Helper variables
    num_Inputs_rows = tblInputs.DataBodyRange.Rows.Count
    On Error Resume Next    ' This is a way to handle the error when SpecialCells(xcellTypeBlanks) is empty and returns an error
    num_Inputs_empty_rows = tblInputs.DataBodyRange.SpecialCells(xlCellTypeBlanks).Rows.Count
    On Error GoTo 0
    data_num_Inputs_rows = num_Inputs_rows - num_Inputs_empty_rows
    
    ' Clear tblSummaries before beginning first/new iteration
    tblSummaries.Range(2, 1).Value = Temp   ' This is a temporary fix to allow table to clear in next line
    tblSummaries.DataBodyRange.ClearContents
    tblSummaries.DataBodyRange.Delete
    tblSummaries.Range(2, 1).Value = Temp   ' Temp is added back in to allow the For loop to identify LastRow for i = 1 (throws an error without this)
    
    ' Loop through each row in tblInputs
    For i = 1 To data_num_Inputs_rows
        
        ' Get the current input row
        Set inputRow = tblInputs.ListRows(i)
        
        ' Clear the cells in Model_T
        tblModel.DataBodyRange.ClearContents    ' This prevents a pop-up on every iteration asking to replace cells with existing data
                
        ' Copy the input row to the first row of Model_T
        inputRow.Range.Copy tblModel.DataBodyRange.Rows(1)
        
        ' Wait for calculations to complete (if necessary)
        ' (In testing this is a buffer to observe impact of code modifications)
        Application.Wait (Now + TimeValue("0:00:05"))
        
        ' Determine which row the Analysis data should be pasted in based on current iteration (i)
        lastRow = tblSummaries.ListRows.Count
        
        ' Copy all rows from tblAnalysis to tblSummaries
        ' tblAnalysis.DataBodyRange.Copy Destination:=tblSummaries.DataBodyRange.Offset(lastRow - 1)
        Set rSource = tblAnalysis.DataBodyRange
        Set rDest = tblSummaries.DataBodyRange.Offset(lastRow - 1)
        rSource.Copy
        rDest.PasteSpecial xlPasteValues
                
        If i = data_num_Inputs_rows Then
        tblModel.DataBodyRange.ClearContents ' Clear tblModel
        MsgBox "Analysis complete. Go to the Summaries sheet and make an offline copy."
        End If
                
        ' Clear the clipboard
        Application.CutCopyMode = False
     Next i
End Sub

'Sub DeleteBlankRows_V2(ws As Worksheet, tbl As ListObject)
'    Dim AllSummaries As Range
'    Set AllSummaries = tbl.DataBodyRange
'    AllSummaries.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'End Sub

I may have more questions as I develop the code a bit more.