+ Reply to Thread
Results 1 to 3 of 3

Excel Macro Visual Basic code not looking at all sheets with second section of code.

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    16

    Excel Macro Visual Basic code not looking at all sheets with second section of code.

    I have a section of code that does not look at all sheets when it runs the second section of the code. It runs correct with the first section and when it runs the second section it only looks at sheet1 and ignores the other sheets. When it moves to the next file it runs correct again with the first section of the code and on second section it only looks at sheet1 again. I need this code to look at all sheets every time. I have attached the current files I'm using to test this and I have marked my problem in red where it should have looked at all the sheets like in the sections above it.

    Sub ABC()
    Dim sPath As String, sName As String
    Dim bk As Workbook ', sh As Worksheet
    Dim wshLoop As Worksheet
    Dim rw As Long
    
    Set sh = ActiveSheet  'I will record the value and workbook name
    ' in the activesheet when the macro runs
    
    rw = 3 ' which row to write to in the activesheet
    sPath = "C:\Users\Heinrich Venter\Documents\Trend files\"
    sName = Dir(sPath & "*trend*.xls") ' for xl2007 & "*.xls"
    Do While sName <> ""
    
    Set bk = Workbooks.Open(sPath & sName)
    For Each wshLoop In bk.Sheets
    sh.Cells(rw, "A") = bk.Name ' File name
    sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
    sh.Cells(rw, "C") = wshLoop.Range("N14") ' Date
    sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
    sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
    sh.Cells(rw, "F") = wshLoop.Range("P10") ' Volume m3
    sh.Cells(rw, "G") = wshLoop.Range("M16") ' SS
    sh.Cells(rw, "H") = wshLoop.Range("M17") ' Con
    sh.Cells(rw, "I") = wshLoop.Range("M18") ' pH
    sh.Cells(rw, "J") = wshLoop.Range("M19") ' Soluble Iron
    sh.Cells(rw, "K") = wshLoop.Range("M20") ' Total Iron
    sh.Cells(rw, "L") = wshLoop.Range("M21") ' Mol
    sh.Cells(rw, "M") = wshLoop.Range("M22") ' Nitrite
    sh.Cells(rw, "N") = wshLoop.Range("M23") ' Glycol
    sh.Cells(rw, "O") = wshLoop.Range("M24") ' Aerobic bacteria
    sh.Cells(rw, "P") = wshLoop.Range("M25") ' Anaerobic bacteria
    sh.Cells(rw, "Q") = wshLoop.Range("M27") ' Mild steel
    sh.Cells(rw, "R") = wshLoop.Range("M29") ' Copper
    sh.Cells(rw, "S") = wshLoop.Range("G41") ' Comment Ser 1
    sh.Cells(rw, "T") = wshLoop.Range("F41") ' Check field
    rw = rw + 1
    Next wshLoop
    
    Set bk = Workbooks.Open(sPath & sName)
    For Each wshLoop In bk.Sheets
    sh.Cells(rw, "A") = bk.Name ' File name
    sh.Cells(rw, "B") = bk.Worksheets(1).Range("I3")  ' Name
    sh.Cells(rw, "C") = bk.Worksheets(1).Range("N14") ' Date
    sh.Cells(rw, "D") = bk.Worksheets(1).Range("J7")  ' Client
    sh.Cells(rw, "E") = bk.Worksheets(1).Range("Q9")  ' Circuit
    sh.Cells(rw, "F") = bk.Worksheets(1).Range("P10") ' Volume m3
    sh.Cells(rw, "G") = bk.Worksheets(1).Range("N16") ' SS
    sh.Cells(rw, "H") = bk.Worksheets(1).Range("N17") ' Con
    sh.Cells(rw, "I") = bk.Worksheets(1).Range("N18") ' pH
    sh.Cells(rw, "J") = bk.Worksheets(1).Range("N19") ' Soluble Iron
    sh.Cells(rw, "K") = bk.Worksheets(1).Range("N20") ' Total Iron
    sh.Cells(rw, "L") = bk.Worksheets(1).Range("N21") ' Mol
    sh.Cells(rw, "M") = bk.Worksheets(1).Range("N22") ' Nitrite
    sh.Cells(rw, "N") = bk.Worksheets(1).Range("N23") ' Glycol
    sh.Cells(rw, "O") = bk.Worksheets(1).Range("N24") ' Aerobic bacteria
    sh.Cells(rw, "P") = bk.Worksheets(1).Range("N25") ' Anaerobic bacteria
    sh.Cells(rw, "Q") = bk.Worksheets(1).Range("N27") ' Mild steel
    sh.Cells(rw, "R") = bk.Worksheets(1).Range("N29") ' Copper
    sh.Cells(rw, "S") = bk.Worksheets(1).Range("G42") ' Comment Ser 1
    sh.Cells(rw, "T") = bk.Worksheets(1).Range("F42") ' Check field
    rw = rw + 1
    Next wshLoop
    
    
    bk.Close SaveChanges:=False
    sName = Dir()
    Loop
    
    End Sub
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644
    Why aren't you using the loop variable wshLoop in the second For loop?

    In that loop you only refer to bk.Worksheets(1) which is the first worksheet in the workbook you just opened.

    Actually, why do you have 2 loops?

    As far as I can see they are identical.
    Last edited by Norie; 05-10-2014 at 07:49 AM.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-20-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Excel Macro Visual Basic code not looking at all sheets with second section of code.

    O.o wow how could I miss that?

    Thank you I had a brain fart lol.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Visual Basic Macro code stops working
    By LPChester in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2013, 07:56 AM
  2. The Code Window in the Visual Basic Editor of Excel
    By Marilyaryott in forum Tips and Tutorials
    Replies: 0
    Last Post: 03-23-2012, 10:31 PM
  3. Excel 2007 : Excel Combo-box Visual Basic Code
    By daibao in forum Excel General
    Replies: 3
    Last Post: 08-18-2010, 02:35 AM
  4. [SOLVED] Visual basic Code or editor won't load in Excel
    By johnsday in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2006, 02:10 PM
  5. [SOLVED] How do I hide my Visual basic code in Excel?
    By geoffc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 08:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1