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