Worksheet Loop

taylorpa5 · Mar 22nd 2016

Can't figure out how to get the worksheet loop to advance to the next sheet. The way code is written currently loops the procedure on the same worksheet. What am I missing here?

Code

Sub ACCESS_OCIInventorySetup()
    
    'Copy over unclean part number into new column range
    Sheetcount = ActiveWorkbook.Worksheets.Count


    For S = 1 To Sheetcount
   
        Range("J1").Value = "Clean Part No"
        For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
        
        Cells(i, "J") = Cells(i, "C").Value
        
        Next i
        
        'Substitute all special characters and spaces and replace with ""
        Intersect(Columns("J"), ActiveSheet.UsedRange).NumberFormat = "@"


        For Each v In Array(" ", ".", "#", "-", "/", "~*", "(", ")", "BAV", "OCI", "ATT", "\", "LVN", "SDC")


        Columns("J").Replace v, "", xlPart


        Next v
    
    Next S
 
End Sub

Display More

taylorpa5 · Mar 22nd 2016

Re: Worksheet Loop

Fixed it. Thank you for anyone who took the time to review my post. Needed to activate each sheet on the loop.
The new code is:

Code

Sub ACCESS_OCIInventorySetup()
    
    'Copy over unclean part number into new column range


    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    
    
        Range("J1").Value = "Clean Part No"
        For i = 2 To Cells(Rows.Count, "C").End(xlUp).Row
        
        Cells(i, "J") = Cells(i, "C").Value
        
        Next i
        
        'Substitute all special characters and spaces and replace with ""
        Intersect(Columns("J"), ActiveSheet.UsedRange).NumberFormat = "@"


        For Each v In Array(" ", ".", "#", "-", "/", "~*", "(", ")", "BAV", "OCI", "ATT", "\", "LVN", "SDC")


        Columns("J").Replace v, "", xlPart


        Next v
    
    Next ws
 
End Sub

Display More

Grimes0332 · Mar 22nd 2016

Re: Worksheet Loop

You don't need to activate the sheet, and it is recommended you don't.

2 main reasons:
Code executes faster
You have to explicitly refer to a sheet, avoids those errors where the code expects a certain sheet to be active, but it's not.

Note the 'With' and the dot notation in front of Cell references. That's basically a shorthand that says anything after the dot is assumed to be a child of the last object referenced by a 'With'

Code

Option Explicit




Sub ACCESS_OCIInventorySetup()


   ' Declare your variables
   Dim ws As Excel.Worksheet
   Dim i As Long
   Dim v As Variant
   
   'Copy over unclean part number into new column range


   For Each ws In ActiveWorkbook.Worksheets


      With ws


         .Range("J1").Value = "Clean Part No"


         For i = 2 To .Cells(Rows.Count, "C").End(xlUp).Row
            .Cells(i, "J") = .Cells(i, "C").Value
         Next i


         'Substitute all special characters and spaces and replace with ""
         Intersect(.Columns("J"), ws.UsedRange).NumberFormat = "@"


         For Each v In Array(" ", ".", "#", "-", "/", "~*", "(", ")", "BAV", "OCI", "ATT", "\", "LVN", "SDC")
            .Columns("J").Replace v, "", xlPart
         Next v


      End With


   Next ws


End Sub

Display More

taylorpa5 · Mar 22nd 2016

Re: Worksheet Loop

I friggen love this forum. My code actually works pretty fast (not a lot of worksheets), but I do appreciate the quick lesson. I'll give this a shot tomorrow.

Worksheet Loop

Participate now!

Share