Parsing an array over multiple worksheets

Question

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

Sorry Scott...I'll add that. I'm getting a Run-time error '1004': Application-defined or object-defined error — rmunch123
– rmunch123, Commented Aug 23, 2017 at 17:08

Scott Craner · Accepted Answer · 2017-08-23 17:12:28Z

1

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

edited Aug 23, 2017 at 17:12

answered Aug 23, 2017 at 17:09

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rmunch123 Over a year ago

Ahhh I see. Is there a way to do this for an entire workbook? Or what is the best way to qualify this across all of the worksheets? Thank you sir!

rmunch123 Over a year ago

Scott...You are a genius. Thank you SO much for this!

Scott Craner Over a year ago

@rmunch123 Please remember to mark as correct by clicking the check mark by the answer.

Collectives™ on Stack Overflow

Parsing an array over multiple worksheets

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related