Macro won't loop thru tabs of workbook


Board Regular
Mar 23, 2009
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.

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
    ActiveWindow.LargeScroll Down:=1
    ActiveWindow.LargeScroll Down:=1
    Workbooks.Open Filename:="c:\Work Files\Templates\Shortage Request Template V3.xls"
    Application.DisplayAlerts = False
    'Windows("Shortage Request Template V3").Activate
    Application.DisplayAlerts = False
     Call ADOFromExcelToAccess 'pastes the copied data and runs another Macro
     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:= _
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    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
    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
    Set rs = Nothing
    Set cn = Nothing
End Sub
Last edited by a moderator:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
Upvote 0
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.

Upvote 0
...what? :unsure:

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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back