Macro won't loop thru tabs of workbook

[tvman5683]

I have three macros together in a template worksheet.
The first macro is supposed to loop thru the tabs on the open Active sheet, then run the two other macros, then come back to the Active sheet and advance to to tab 2 and loop the process all over again.
If I have 3 tabs in the Active sheet, the macro runs three times but it only keeps reprocessing the first tab(3times)
Here is the code: Any help would be greatly appreciated.
Thanks

Option Explicit
 
Sub LoopThroughSheets()
    Dim ws As Worksheet
    
    
    
    For Each ws In Worksheets
     'ActiveSheet.Name = "book4"
     ' running from an active sheet named book4
     Call Macro6 'copies data and opens a second sheet
     
     'heres where I think I need to get back to the original sheet to run Macro6 on the next tab in the workbook.
     
         
        On Error Resume Next 'Will continue if an error results
        ws.Range("A1") = ws.Name
         
         '***********************
         
    Next ws
End Sub
Sub Macro6()
'
' Macro6 Macro
' Macro recorded 3/22/2009 by jbella0
'
'
    ActiveWindow.LargeScroll Down:=1
    Range("B16:K43").Select
    ActiveWindow.LargeScroll Down:=1
    Range("B16:K73").Select
    ActiveWindow.LargeScroll Down:=1
    Range("B16:K103").Select
    Selection.Copy
    Workbooks.Open Filename:="c:\Work Files\Templates\Shortage Request Template V3.xls"
    Application.DisplayAlerts = False
    'Windows("Shortage Request Template V3").Activate
    Range("A1").Select
    Application.DisplayAlerts = False
     Call ADOFromExcelToAccess 'pastes the copied data and runs another Macro
     'ws.Close
     Application.DisplayAlerts = True
     
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Call Macro5
        
End Sub
Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
'Dim qd As DAO.QueryDef
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=c:\D DRIVE\Shortage and District Request.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "Tester", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    ' all records in a table
    r = 3 ' the start row in the worksheet
     'ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        "_", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)) _
        , TrailingMinusNumbers:=True
    Range("E8").Select
    Sheets("Tester").Select
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("Action") = Range("A" & r).Value
            .Fields("Type") = Range("I2").Value
            .Fields("District") = Range("G2")
            .Fields("Tech") = Range("H2")
            .Fields("Div") = Range("B" & r).Text
            .Fields("Pls") = Range("C" & r).Text
            .Fields("Part") = Range("D" & r).Value
            .Fields("New Qty") = Range("F" & r).Value
            
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        
        r = r + 1 ' next row
    Loop
    
    
    
    
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Sheets("Mail").Select
    Range("A1:M200").Select
    Selection.ClearContents
    Range("A1").Select
    ActiveWindow.Close
    
  
    
    
End Sub

 

[Von Pookie]

You'll need to dim the ws variable outside of all macros so all 3 macros can reference it.

Basically, your first code is looping through the sheets, but none of the other macros are written for it--they're still referencing whatever sheet is active. You'll need to use the ws variable in those macros as well to refer to the correct sheet.

 

[tvman5683]

Thanks for the tip. I downloaded the HTML maker and will use it next time. Luckily I did find a simple loop code and embedded my macros in and it seems to work great.
I'll post it as soon as I get the HTML maker up and running.

Thanks
JB

 

[Von Pookie]

...what?

OH. The link in my post. Right. That is my "signature" on the board and displays on every post I make. The text under my name in my post is/was not specifically directed at you.