VBA: Pass a sheet to another sub

[FryGirl]

I need to loop thru a specified number of worksheets and run a sub. I would like to keep the two subs separately since it actually has two purposes.

Basically, If Sheet5.Range("B1").value = "Prod" then I need to loop thru the 12 sheets, but if not, then I need to just run the Sub RunMe on Sheet3.

I've crafted an initial sub, but not sure how to pass between the two.

[FONT=Times New Roman][COLOR=#000000]Sub FillEachWs()[/COLOR][/FONT]
[COLOR=#000000][FONT=Times New Roman]    Dim i As Long[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]    For i = 1 To 12[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]        If Sheet5.Range("B1").Value ="Prod" Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]            With Sheets(CStr(i))[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]                Call RunMe[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]            End With[/FONT][/COLOR]
[COLOR=#000000][FONT=Times New Roman]        Else[/FONT][/COLOR]

[COLOR=#000000][FONT=Times New Roman]    Next i[/FONT][/COLOR]
[FONT=Times New Roman][COLOR=#000000]End Sub[/COLOR][/FONT]

[FONT=Times New Roman][COLOR=#000000]Sub RunMe()[/COLOR][/FONT]
[COLOR=#000000][FONT=Times New Roman]    Sheet3.Range("AU1").Value ="Check"[/FONT][/COLOR]
[FONT=Times New Roman][COLOR=#000000]End Sub[/COLOR][/FONT]

 

[Fluff]

Will you need to run the "RunMe" sub on it's own?

 

[Fluff]

Having looked at your code the 1st macro makes no sense. Can you explain what it's meant to be doing?

 

[FryGirl]

Yes, exactly. I will need to run the "RunMe" sub on it's own, so that's why I'm trying to separate the two.

The first sub was just my initial attempt to put something together, but quickly found out I was not entirely sure who to do it.

I'm okay with having the loop in the "RunMe" sub, just need to be able to distinguish between requiring the loop or just running the "RunMe" sub on Sheet3 only.

 

[Fluff]

Can you please explain what you are trying to do, as those codes make no-sense to me.

 

[JoeMo]

Yes, exactly. I will need to run the "RunMe" sub on it's own, so that's why I'm trying to separate the two.

The first sub was just my initial attempt to put something together, but quickly found out I was not entirely sure who to do it.

I'm okay with having the loop in the "RunMe" sub, just need to be able to distinguish between requiring the loop or just running the "RunMe" sub on Sheet3 only.

If the "Prod" criterion is met, what do you want to do to the sheets with indices 1- 12? As it stands now the RunMe sub simply puts a value in a single cell in Sheet3 so there's no point in calling it 12 consecutive times.

 

[FryGirl]

Okay,we can drop the first code. This is my original code below. Of coursetrimmed down, but once I see the concept I can apply it to the rest of the code.

[COLOR=black]Sub RunMe()[/COLOR][COLOR=#333333]
[COLOR=black][/COLOR]   Sheet3.Range("AU1").Value ="Check"[/COLOR][COLOR=#333333]
[COLOR=black][/COLOR]End Sub[/COLOR][COLOR=#222222]

This code works fine by placing "Check" in Range("AU1"), but only on Sheet3.

I now need to expand the code to work in two ways. At times I will havejust Sheet3 in my workbook, and at other times I will have multiple worksheetsin the workbook. If there is one worksheet, it is named "1",but the code name is Sheet3. If there are multiple sheets they will benamed 1, 2, 3, 4, 5, 6 etc.

On another sheet, if S[/COLOR]heet5.Range("B1").Value="Prod" then that means its a workbook with multiple sheets, and ifnot, then its just Sheet3.

 

[Fluff]

Are you trying to put "Check" into AU1 on every sheet in the workbook?
If so try

Sub FillEachWs()
   Dim Ws As Worksheet
   For Each Ws In ThisWorkbook.Worksheets
      Ws.Range("AU1").Value = "Check"
   Next Ws
End Sub

 

[JoeMo]

If there are multiple sheets in the workbook, do you still want to put "Check" in cell AU1 on Sheet3, and what do you want to do to the other sheets?

 

[FryGirl]

Yes, I'm putting "Check" into AU1 on every sheet is the workbook, but only those with a number.

1, 2, 3, 4, 5, 6 etc.​