0

So I am basically needing to different arrays to progress at the same time in the loop for it to paste values on each different sheet. I am not understanding how to get both to progress...

    Sub TransAll()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, Arr4 As Variant
    Dim i As Integer
    Dim x As Integer

    Set wb1 = Workbooks("primecost.xlsm")
    Set wb2 = Workbooks("inventory.xlsm")
    Set wb3 = Workbooks("transmanager.xlsm")

    Arr1 = Array(2, 3, 5, 6)
    Arr2 = Array(2, 3, 4, 5, 6, 7)

    Arr3 = Array(1, 2, 3, 4)
    Arr4 = Array(5, 6, 7, 8, 9)


    'for "I" works fine by iteself, but it needs "I" and "X" to progress accordingly in the same loop according to the different arrays.

    For i = LBound(Arr1) To UBound(Arr1)
    'For x = LBound(Arr3) To UBound(Arr3)

        wb1.Sheets(Arr1(i)).Cells.Copy
            wb3.Sheets(Arr3(x)).Cells.PasteSpecial Paste:=xlPasteValues
    Next i
    'Next x


    '------------------------------------------------------------------
        'this works but on at a time... very slow and a pain
        'wb1.Sheets(6).Cells.Copy
            'wb3.Sheets(1).Cells.PasteSpecial Paste:=xlPasteValues    
    '------------------------------------------------------------------      

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    End Sub

I just need to know how to get both variants progressing inside the same loop.

Improve this question
3
  • just add say, x = x+1 after you use it? Commented Feb 13, 2019 at 22:29
  • Or move your Next x to be above Next i Commented Feb 13, 2019 at 22:32
  • That was it... it was seriously that simple... x before i... sometimes i hate this thing... lol Thank you! Commented Feb 13, 2019 at 22:37

1 Answer 1

Reset to default
1

To increment x before i just switch the Next x to be before Next i. 

Sub TransAll()

Dim wb1 As Workbook: Set wb1 = Workbooks("primecost.xlsm")
Dim wb2 As Workbook: Set wb2 = Workbooks("inventory.xlsm")
Dim wb3 As Workbook: Set wb3 = Workbooks("transmanager.xlsm")

Dim Arr1, Arr2, Arr3, Arr4
Dim i As Long, x As Long

Arr1 = Array(2, 3, 5, 6)
Arr2 = Array(2, 3, 4, 5, 6, 7)
Arr3 = Array(1, 2, 3, 4)
Arr4 = Array(5, 6, 7, 8, 9)

Application.ScreenUpdating = False
Application.DisplayAlerts = False  '<-- Is this one needed?
Application.EnableEvents = False

    For i = LBound(Arr1) To UBound(Arr1)
        For x = LBound(Arr3) To UBound(Arr3)
            wb1.Sheets(Arr1(i)).Cells.Copy
            wb3.Sheets(Arr3(x)).Cells.PasteSpecial Paste:=xlPasteValues
        Next x
    Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Also applied a little cleaner (in my opinion) structure to your code. Note that variables not defined default to Variant so when declaring variants, you can just use Dim Arr

If you had indented your code in this order, you may have noticed the issue without needing to post. Notice that the open and closing statements around loops are aligned. Your code in the below format would have made the issue stand out a little more making it easier to debug/correct since i and x would have been on different indentations - just an example of the power of adopting standard indentation/structure when coding

'Notice the i & x statements are misaligned!
Application.Screen/Alerts/Events 
    For i = ..
        For x = ..
            'Action Statements
        Next i
    Next x
Application.Screen/Alerts/Events

Solution Post Comments

Update your loop to 

For i = LBound(Arr1) To UBound(Arr1)
     wb1.Sheets(Arr1(i)).Cells.Copy
     wb3.Sheets(Arr3(i)).Cells.PasteSpecial Paste:=xlPasteValues
Next i
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

It runs through and ends up pasting the last page of the first array (I) on every page... So it acts like it is pasting 2, 3, 5, 6 on every page, and 6 being the last one is the one that shows... It should be pasting I-2 on x-1 i-3 on x-2 and so on...
Stepping through it is is runnin next x 4 times and then going to next i and x 4 times... so it is not reading them together... it needs to be next x and i at the same time
Oh - so you only need 4 copy/pastes?
I follow, please see the edit at bottom of my solution. This is dependent on Arr1 and Arr3 always being the same size.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.