VBA Loop through sheets not detecting parameters

0 votes

Therefore, I'm attempting to format all sheets other than the "Names" sheet. and the solution I came up with below doesn't appear to be able to loop and find the "Names" sheet. When the sheet in question is active, it will try to format "Names" or it will only format one other sheet at a time.

Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Names" Then
            Rows("1:1").Select
            Selection.AutoFilter
            ActiveSheet.Range("$A$1:$Q$19").AutoFilter Field:=4, Criteria1:="="
            Rows("2:2").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Range(Selection, Selection.End(xlDown)).Select
            Selection.Delete Shift:=xlUp
            ActiveSheet.Range("$A$1:$Q$16").AutoFilter Field:=4
            Columns("G:G").Select
            Range(Selection, Selection.End(xlToRight)).Select
            Selection.Delete Shift:=xlToLeft
            Range("J15").Select
        End If
        
    Next ws

I've tried rewriting the codes completely but the same problem persists

Jan 24, 2023 in Others by Kithuzzz
• 38,010 points
249 views

1 answer to this question.

0 votes

Rewriting to prevent in addition to removing Activesheet. Consider utilizing a With statement to unambiguously link each action to the current sheet in addition to selecting and possibly exploring an alternative to Criteria1:="=" (as previously mentioned).

Sub Format_Worksheets()

    Dim WS As Worksheet
    Dim lRow As Long
    Dim lCol As Long
    
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> "Names" Then
            With WS
                .Rows("1:1").AutoFilter
                .Range("$A$1:$Q$19").AutoFilter Field:=4, Criteria1:="="
                lRow = .Range("A2").End(xlDown).Row
                lCol = .Range("A2").End(xlToRight).Column
                .Range(.Cells(lRow, 1), .Cells(lRow, lCol)).Delete shift:=xlUp
                .Range("$A$1:$Q$16").AutoFilter Field:=4
                lCol = .Range("G1").End(xlToRight).Column
                .Range("G1", .Cells(1, lCol)).Delete shift:=xlToLeft
            End With
        End If
    Next WS

End Sub

If this works out for you, please let me know. It worked for me, but I'm not sure if the formatting will be exactly the same as what you did. Although I rewrote it without the terms ".select" or ".activate," it's often difficult to determine without examining the data.

answered Jan 24, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,012 views
0 votes
1 answer

Excel VBA is not recording macro properly

The "awkward code" is an R1C1 notation ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,420 points
766 views
0 votes
1 answer

How to clear contents of an Excel Workbook through vba

When the range you're referring to doesn't ...READ MORE

answered Nov 19, 2022 in Others by narikkadan
• 63,420 points
504 views
0 votes
1 answer

Loop formula in excel vba

You can use an IF statement to ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
553 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
829 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,123 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
419 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
687 views
0 votes
1 answer

VBA: Do While Loop does not want to looping

Try this: currentRow = 11 Do While originalSheet.Cells(currentRow, 1).Value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
305 views
0 votes
1 answer

Excel VBA Rnd Not Actually Random

 Initialize the random number function. Sub test() Dim number ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,420 points
380 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP