VBA - Very slow loop through rows to copy/paste values-CodePudding

I'm fairly new to VBA and need to calculate a large list of different iterations of 3 variables within an Excel formula. I do not believe "What-If" analysis can be used to complete this due to there being 3 variables.

I have written the below to try to "copy/paste" the three variables from a variable sheet "Batch" into another calculation sheet (Main). After this calculation has taken place within the sheet I select the result from the calculation sheet (Main) and paste this back into the original "batch" sheet.

The problem I appear to be having is that this is incredibly slow - estimations around 18 hours on my machine based on single iteration times. If anyone could assist in helping me optimise I would greatly appreciate it!!

Sub Macro3()

Application.ScreenUpdating = False
Dim i As Long

    For i = 2 To 65095
        Application.StatusBar = "Current iteration: " & i
        'CopyPaste Data
        ThisWorkbook.Worksheets("Main").Range("D6").Value = Worksheets("Batch").Range("D" & i).Value
        ThisWorkbook.Worksheets("Main").Range("D7").Value = Worksheets("Batch").Range("E" & i).Value
        ThisWorkbook.Worksheets("Main").Range("D8").Value = Worksheets("Batch").Range("F" & i).Value
        ThisWorkbook.Worksheets("Main").Range("D9").Value = Worksheets("Batch").Range("G" & i).Value
        'CopyPaste Result
        ThisWorkbook.Worksheets("Batch").Range("H" & i).Value = Worksheets("Main").Range("E19")
        Next i
End Sub

CodePudding user response：

Example of using Application.Transpose() to support one instance of values being input, triggering calculations once:

Sub testApplicationTranspose()
    With Sheets(1)
        Dim sourceRng As Range:  Set sourceRng = .Range("b2:b5")
        Dim destinationRng As Range:  Set destinationRng = .Range("d7:g7")
    End With
    destinationRng = Application.Transpose(sourceRng)
End Sub

Edit1: Applying to your code:

Sub Execute()
    applicationToggle False
    Dim i As Long
    For i = 2 To 65095
        Application.StatusBar = "Current iteration: " & i
        'CopyPaste Data
        Dim sourceRng As Range:  Set sourceRng = Worksheets("Batch").Range("D" & i & ":G" & i)
        Dim destinationRng As Range:  Set destinationRng = ThisWorkbook.Worksheets("Main").Range("D6:D9")
        destinationRng = Application.Transpose(sourceRng)
        'CopyPaste Result
        ThisWorkbook.Worksheets("Batch").Range("H" & i).Value = Worksheets("Main").Range("E19")
    Next i
    applicationToggle True
End Sub

Sub applicationToggle(val as Boolean)
    With Application
        .EnableEvents = val
        .ScreenUpdating = val
    End With
End Sub