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

Antonino2023 

You can started with the VBA code to achieve your task.

Here's an example VBA code that you can use as a starting point:

Sub IterateAndCopyPaste()
    Dim wb As Workbook
    Dim wsInputs As Worksheet
    Dim wsAnalysis As Worksheet
    Dim wsSummaries As Worksheet
    Dim tblInputs 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
    
    ' Set references to the workbook and worksheets
    Set wb = ThisWorkbook
    Set wsInputs = wb.Worksheets("All Inputs")
    Set wsAnalysis = wb.Worksheets("Analysis")
    Set wsSummaries = wb.Worksheets("Summaries")
    
    ' Set references to the tables
    Set tblInputs = wsInputs.ListObjects("Inputs")
    Set tblAnalysis = wsAnalysis.ListObjects("Analysis_T")
    Set tblSummaries = wsSummaries.ListObjects("Summaries")
    
    ' Loop through each row in the Inputs table
    For i = 1 To tblInputs.DataBodyRange.Rows.Count
        ' Get the current input row
        Set inputRow = tblInputs.ListRows(i)
        
        ' Clear the cells in Analysis_T
        tblAnalysis.DataBodyRange.ClearContents
        
        ' Copy the input row to the first row of Analysis_T
        inputRow.Range.Copy tblAnalysis.DataBodyRange.Rows(1)
        
        ' Wait for calculations to complete (if necessary)
        ' Add code here to wait for the calculations to complete
        
        ' Copy all rows from Analysis_T to Summaries
        lastRow = tblSummaries.DataBodyRange.Rows.Count
        tblAnalysis.DataBodyRange.Copy Destination:=tblSummaries.DataBodyRange.Offset(lastRow)
        
        ' Delete rows from Summaries where Result 2 is "No"
        DeleteRowsWithNoResult2 wsSummaries, tblSummaries
        
        ' Clear the clipboard
        Application.CutCopyMode = False
    Next i
End Sub

Sub DeleteRowsWithNoResult2(ws As Worksheet, tbl As ListObject)
    Dim result2Column As Range
    Dim cell As Range
    
    ' Set the Result 2 column range
    Set result2Column = tbl.ListColumns("Result 2").DataBodyRange
    
    ' Loop through each cell in the Result 2 column
    For Each cell In result2Column.Cells
        ' Check if the value is "No" and delete the entire row
        If cell.Value = "No" Then
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

Please note that this is a basic example, and you may need to modify it based on your specific workbook structure and requirements. You'll also need to add code to wait for the calculations to complete, as indicated in the comments.

To run the code, press Alt+F11 to open the VBA editor in Excel, insert a new module, and paste the code into the module. You can then run the IterateAndCopyPaste macro to perform the desired iterations.

You can achieve the desired task using Power Query too in Excel.

Here's a step-by-step solution using Power Query:

1. Open your Excel workbook and go to the "Data" tab.
2. Click on "Get Data" and select "From Other Sources" -> "Blank Query". This will open the Power Query Editor.
3. In the Power Query Editor, go to the "Home" tab and click on "Advanced Editor".
4. Replace the existing code with the following Power Query code:

let
    Source = Excel.CurrentWorkbook(){[Name="Inputs"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [#"Result 2"] = "Yes"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Result 2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Model", "Parts"}, {"Calculation 1", "Result 1"}, {"Calculation 2", "Result 2"}, {"Calculation 3", "Result 3"}}),
    #"Appended Queries" = Table.Combine({#"Renamed Columns", #"Renamed Columns", #"Renamed Columns"}) // Replace the number of repetitions here
in
    #"Appended Queries"
​

5. In the code above, replace "Inputs" with the actual name of your table in the "All Inputs" sheet.

6. Replace the comment // Replace the number of repetitions here with the actual number of times you want to repeat the rows.

7. Click on "Done" to close the Power Query Editor.

This Power Query code will filter the rows based on "Result 2" column, remove the "Result 2" column, rename the columns accordingly, and append the resulting table multiple times based on the specified number of repetitions.

Once you've completed the above steps, Power Query will load the transformed data into a new table in Excel. The table will contain the desired iterations based on your requirements.

You can refresh the data in the table whenever needed by clicking on "Data" -> "Refresh All".

I hope the proposal solutions, with AI helps, using VBA or Power Query meets your requirements.