Do Loops in VBA Applied Across Multiple Worksheets

Hi all,

I am attempting to run a process that will populate worksheets in a workbook from a summary sheet.

The summary sheet contains a table that has a prefix of "X" followed by numbers 1 to 20, which correspond to the naming convention of the worksheets in the workbook. What I am looking to do is match up the data from the summary sheet and populate a range of cells in the corresponding worksheet (e.g. if in the table "X17" has a number 26 beside it, I would like that number to be entered in cell "D2" in the spreadsheet named "X17").

I have attempted this a few different ways including:


Dim I As Long
For I = Sheets("X1") To ("X20")

If Worksheets = I Then ...

However, this errors. Although I've used VBA for a while now it has only been for very limited things and I've never worked with arrays or do loops.

I would be really grateful for any help or advice on how to proceed with this.

Thank you

I don't think you can use the syntax as you are doing so in the for loop, you would probably have to do either:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

then operate your code on the sh object as neccessary.

Hi and thanks for your reply.
I am still encountering an error in what I am doing. The syntax I have applied is a variation of what you have suggested:


For Count = 2 To 20
Set I = Sheets("X" & Count)
TAR = "TARGET" & Count

Sheets(I).Activate

If I Then
Application.Run "SolverReset"

SolverAdd "$S$46", 2, "$C$4"
SolverOk "$G$86", 3, TAR, "$B$50,$B$52"
Application.DisplayAlerts = False
SolverSolve UserFinish:=True

next

This is only a small portion of what I need to do. I need to populate the referenced cells from the summary sheet as well, however, this is erroring repeatedly and I am quite lost still. Have I managed to do anything right?
I need to automate this across 20 sheets and although I can do it manually sheet by sheet I can't find a simple way of getting it to do it 20 times.

Thank you

When you use a command like:
set I = Sheets("X" & count)
then you can use a command like:

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

I'm not sure what

Please Login or Register  to view this content.

is meant to do

It's hard to debug from what is available - can you upload a workbook (click go advanced and then attachments).

Replace any sensitive data with dummy data

Thank you. That works a little better. Unfortunately I am unable to upload an example whilst at work but I'll create something at home to illustrate what I'm after if that helps and post it later on in the day.
Basically I wanted to tie each increment in the sheet number to an increment in the target value taken from a summary sheet. By making the amendment that you have suggested above the Solver portion of the code runs on each sheet but fails to update the target value.
Do you think this will be a relatively easy fix?
Thanks again.

I think so,

I'm not sure that your solverok statement is correct - you are setting the 3rd command to "TARGET2". I'm not that familiarly with programming the solver but I would guess you want something like:
SolverOK SetCell:="$G$86", MaxMinVal:=3, ValueOf:=Count, ByChange:="$B$50,$B$52"

I think you possibly also want to reset the solver each time as well. I think you just type solverreset for this.

Hi,

The Solver component works as required, and Application.Run "SolverReset" ensures that the Solver options are reset every time. I've tested this across 5 sheets although in the previous version I coded each sheet within different sub routines (calling each one from the main sheet). What I wanted to avoid was having to do this 20 times for the larger version of this exercise and this is where I am experiencing difficulties, as I’ve had very limited exposure to this level of complexity.
Hope this is a better explanation of what I’m trying to do.

Thanks

Can you post the individual bits of code that work on each of the 5 sheets and I'll show you how to put them into a loop.

Hi,

It's a fairly lengthy bit of code and unfortuately due to the sensitivity of the work I am carrying out I cannot disclose each component, however, I have simplified some things in the code pasted below. Basically, some values are read in from a summary sheet and some things from the worksheet x1 are used to populate the cells in worksheet x2.

Please find the code (which has been tested and works as expected) below - please note this is repeated for sheet x2, x3 and so on and the values taken from the summary sheet increase by 1 each time (so from C5 to C6 and so on).


Public TARGET1 As Variant
Public ENDNUM1 As Variant

Public STARTNEXT2 As Variant
Public PREV2 As Variant

Sub SETUP()

Sheets("X1").Activate

Application.Run "SolverReset"

TARGET1 = Sheets("SUMMARY").Range("C5")
ENDNUM1 = Sheets("SUMMARY ").Range("D5").Value
If Not IsNumeric(TARGET1) Then
MsgBox "Please Enter a Valid Target and End Balance Value"
End If
Sheets("X1").Range("C5") = TARGET1
Sheets("X1").Range("C4") = ENDNUM1
Call SOLVER1

End Sub


Sub SOLVER1()

Sheets("X1").Activate

Application.Run "SolverReset"

SolverAdd "$S$46", 2, "$C$4"
SolverOk "$G$86", 3, TARGET1, "$B$50,$B$52"
Application.DisplayAlerts = False
SolverSolve UserFinish:=True



If Range("B50") < 0 Then
GoTo TERMINATE2
If Range("B52") < 0 Then
GoTo TERMINATE2
End If
End If

Dim RESULT As Variant


RESULT = Application.Run("SolverSolve", True)

If RESULT < 2 Then

MsgBox "SOLUTION FOUND", vbInformation, "SOLUTION FOUND"
ElseIf RESULT = 13 Then MsgBox "ERROR PLEASE CHECK PARAMETERS ENTERED AND TRY AGAIN"
Else: MsgBox "Solver was unable to find a solution.", vbExclamation, "SOLUTION NOT FOUND"

If RESULT > 2 < 13 Then

Exit Sub
TERMINATE:
MsgBox "A SOLUTION CANNOT BE CALCULATED. PLEASE TRY AGAIN AND ENTER A TARGET VALUE OF < = 1"

TERMINATE2:
MsgBox "A SOLUTION CANNOT BE CALCULATED. PLEASE CHECK INPUT VALUES"
End If
End If
STARTNEXT2 = Sheets("X1").Range("$U$30:$U$44").Value
Sheets("X2").Range("$D$10:$D$24") = STARTNEXT2

PREV2 = Sheets("X1").Range("$D$49:$R$64")
Sheets("X2").Range("$U$49:$AI$64") = PREV2

Call SOLVER2
Call SOLVER3
Call SOLVER4
Call SOLVER5

End Sub

You need to post the code for solver2, solver3, solver4 and solver 5 as well.