How do I loop through the same range for all worksheets?

24 Views Asked by At

I have Excel VBA code which will look at a process (on a worksheet labeled as "Process A") and will produce a flowchart on a worksheet labeled as Flowchart. The problem is that I may have up to 15 different processes (on 15 different worksheets, labeled from "Process A" to "Process O"). What I'd like to do is to have VBA act on "Process A" and then move on to each consecutive process and do the same. I have given part of the coding below. Essentially, the code uses "Case" to determine if an object is a Connector, a Decision, or a Process. Then, it places a shape corresponding to each of these at a designated location and formats according to the code. I have tried various ways to loop through all worksheets and ranges, but each of them has resulted in a failure. Thank you in advance for any help you can provide!

Sub ProcessA()


Line1:     Dim ws As Worksheets
Dim ws2 As Worksheet: Set ws2 = Worksheets("Flowchart")
Dim wb As Workbook
Dim shp As Shape
Dim Left As Variant, Top As Variant, Width As Variant, Height As Variant
Dim BegX As Variant, BegY As Variant, EndX As Variant, EndY As Variant
Dim arw As Shape
Dim Vrb As String
Dim rng As Range


For Each ws In ThisWorkbook.Worksheets
If IsNumeric(Range("CK2:CK23")) = True Then


    Left = rng.Value
    Top = rng.Offset(, 1).Value
    Width = rng.Offset(, 2).Value
    Height = rng.Offset(, 3).Value
    BegX = rng.Offset(, 4).Value
    BegY = rng.Offset(, 5).Value
    EndX = rng.Offset(, 6).Value
    EndY = rng.Offset(, 7).Value
    Vrb = rng.Offset(, -87).Value

 End If

            Select Case rng.Offset(, -85).Value
            Case "Connector"
            Set shp = ws2.Shapes.AddShape(msoShapeFlowchartConnector, Left, Top, Width, Height)
                With shp
                    .TextFrame.Characters.Text = Vrb
0

There are 0 best solutions below