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 in Others by Kithuzzz
• 27,740 points
44 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 by narikkadan
• 51,240 points

Related Questions In Others

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
• 51,240 points
67 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
• 51,240 points
81 views
0 votes
1 answer

Windows 10 IE is not working with old VBA code

I discovered the answer. The issue was ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 51,240 points
96 views
0 votes
1 answer

Excel VBA: Open Hyperlinks in a loop and copy paste download link in a sheet

Refer this tutorial for your solution: https://evermap.com/Tutorial_AB ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 51,240 points
92 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
522 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
2,382 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
• 22,940 points
110 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,640 points
274 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
• 51,240 points
94 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
• 51,240 points
83 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