VBA automatically apply two macros in multiple worksheets 1

[uszd]

I have macro1 and macro2 in a workbook with 10 sheets. 3 worksheets' name begins with "a", two begins with "b". Does anyone know how automatically go through all 10 sheets and apply macro1 to 3 "a*" sheets , macro2 to 2 "b*" sheets?

Thanks!

 

[SkipVought]

Hi,

I don't know what you mean by, "apply macro1 to 3 "a*" sheets , macro2 to 2 "b*" sheets."

Here's how to loop thru sheets...

dim ws as worksheet
for each ws in thisworkbook.worksheets
  
next

What do your macros do?

Please post your code.



Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[uszd]

Hi Skip,
Thanks.
I want macro1 applys to 3 workseets whose name begins with "a", macro2 applys to 2 worksheets name begins with "b". following is my code:

Sub processthisbook()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

If InStr(1, ws.Name, "a") > 0 Then
Call macro1
Else
If InStr(1, ws.Name, "b") > 0 Then
Call macro2
Else
Exit Sub
End If
End If
Next ws
End Sub

however, it seems it just run macro2 everytime. Thanks!

 

[SkipVought]

Please use the Process TGML tags for [ignore]

[/ignore]

[code]
Sub processthisbook()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      select case left(ws.Name, 1)
        Case "a"
           Call macro1
        Case "b"
           Call macro2
        Case Else
           Exit Sub 'do you REALLY want to bail out or just let the For...Next finish??????
      End select
   Next ws
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[uszd]

It's should be fine, but everytime I'm running only macro2 for current sheet . what's wrong with that?

 

[SkipVought]

That's why I asked you to post your code. I avoid the use of Select and Activate.

You ought to reference the sheet by passing an argument.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[uszd]

hi Skip,

These are my codes:

Sub processthisbook1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case Left(ws.Name, 1)
Case "a"
Call macro1
Case "b"
Call macro2
End Select
Next ws
End Sub

Sub macro1()
Dim strBcell As Date
Dim OBcell As Range
strBcell = Worksheets("Data").Range("a1")
Set OBcell = Rows("1").Find(what:=strBcell)
Range(OBcell.Offset(1, 0), OBcell.Offset(35, 0)).Value = Range("b2:b35").Value
End Sub

Sub macro2()
Dim strCcell As Date
Dim OCcell As Range
strCcell = Worksheets("Data").Range("a1")
Set OCcell = Rows("1").Find(what:=strCcell)
Range(OCcell.Offset(1, 0), OCcell.Offset(35, 0)).Value = Range("c2:c35").Value
End Sub

BTW, the Worksheets("Data").Range("a1") is determined by

Sub start1()
Dim acell As Date
acell = InputBox("Please enter last month ")
Worksheets("Data").Range("a1") = acell
End Sub

Many thanks!

 

[SkipVought]

I'd use just ONE macro and split the ws.name in the macro. But that's me.

Sub processthisbook1()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      Select Case Left(ws.Name, 1)
        Case "a"
           Call macro1 ws
        Case "b"
           Call macro2 ws
       End Select
   Next ws
End Sub
Sub macro1(ws As Worksheet)
    Dim strBcell As Date
    Dim OBcell As Range
    strBcell = Worksheets("Data").Range("a1")
    Set OBcell = ws.Rows("1").Find(what:=strBcell)
    ws.Range(OBcell.Offset(1, 0), OBcell.Offset(35, 0)).Value = ws.Range("b2:b35").Value
End Sub

Sub macro2(ws As Worksheet)
    Dim strCcell As Date
    Dim OCcell As Range
    strCcell = Worksheets("Data").Range("a1")
    Set OCcell = ws.Rows("1").Find(what:=strCcell)
    ws.Range(OCcell.Offset(1, 0), OCcell.Offset(35, 0)).Value = ws.Range("c2:c35").Value
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[uszd]

Thank you Skip.

Could you show me how to use just one macro to perform the whole thing?

 

[SkipVought]

Sub processthisbook1()
   Dim ws As Worksheet
   For Each ws In ThisWorkbook.Worksheets
      Select Case Left(ws.Name, 1)
        Case "a", "b"
           Call NEWmacro ws
       End Select
   Next ws
End Sub
Sub NEWmacro(ws As Worksheet)
    Dim strBcell As Date
    Dim OBcell As Range, sRng as string
    strBcell = Worksheets("Data").Range("a1")
    with ws
       Set OBcell = .Rows("1").Find(what:=strBcell)
       select case Left(.name, 1)
         case "a"
            srng = "a2:a35"
         case "b"
            srng = "b2:b35"
       end select
       .Range(OBcell.Offset(1, 0), OBcell.Offset(35, 0)).Value = .Range(srng).Value
    end with
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}