CODEWITHSUNDEEP
arraysexcelvbavariables
rmunch123
rmunch123

Reputation: 3

Parsing an array over multiple worksheets

I am trying to breakdown an array of tickers on one sheet into multiple sheets. I basically am trying to for loop through multiple sheets using the "i" variable as both a worksheet identifier and also a multiplier for how many tickers go on each sheet.

Can anyone spot the error I'm making here? Or any other ways that you'd recommend to attack this?

"BreakdownCount" = the user inputted grouping size...in this case 200 as a test.

Thanks!

For i = 1 To BreakdownCount  
    UpperRange = 8 + (i - 1) * 200
    LowerRange = 8 + i * 200
    MsgBox UpperRange
    MsgBox LowerRange

    Worksheets("Data " & i).Range(Cells(8, 2), Cells(207, 2)).Value = Worksheets("Scanner - ALL").Range(Cells(UpperRange, 2), Cells(LowerRange, 2)).Value  
Next

Upvotes: 0

Views: 36

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

you need to qualify ALL Range object with their parent sheet. That includes the Cells() inside the Range():

For i = 1 To BreakdownCount  
    UpperRange = 8 + (i - 1) * 200
    LowerRange = 8 + i * 200
    MsgBox UpperRange
    MsgBox LowerRange

    Worksheets("Data " & i).Range(Worksheets("Data " & i).Cells(8, 2), Worksheets("Data " & i).Cells(207, 2)).Value = Worksheets("Scanner - ALL").Range(Worksheets("Scanner - ALL").Cells(UpperRange, 2), Worksheets("Scanner - ALL").Cells(LowerRange, 2)).Value  
Next

A Little short hand:

Dim wsOut as Worksheet
With Worksheets("Scanner - ALL")
    For i = 1 To BreakdownCount 
        Set wsOut =  Worksheets("Data " & i)
        UpperRange = 8 + (i - 1) * 200
        LowerRange = 8 + i * 200
        MsgBox UpperRange
        MsgBox LowerRange

        wsOut.Range(wsOut.Cells(8, 2), wsOut.Cells(207, 2)).Value = .Range(.Cells(UpperRange, 2), .Cells(LowerRange, 2)).Value  
    Next
End With

Upvotes: 1

Related Questions