Referring to sheets based on variable list of names

[Luka_LB]

Hello everyone!

could u please advise on my new case

so,

I have VBA that brings in A:A column sheets and sheet names of one of the excel files into working file

then I want to have a Code that can refer to sheets based on that names in A:A column

sheet names may vary, as well as in numbers, as in names.

VBA should go to that particular sheet, make some changes, then refer to next sheet in list, and when it's done it must stop



any ideas?


FYI
my current level at coding is pretty basic

 

[tlowry]

Here is some code...

The name of the sheet holding the names of the other sheets is "Master". You need to change this to your situation.

Sub modifySheets()
    Dim lr, shtName
    With Sheets("[B][COLOR=#008000]Master[/COLOR][/B]") '  <-- sheet name with other sheet names in first column
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For Each shtName In Application.WorksheetFunction.Transpose(.Range("A1:A" _
                                        & .Cells(Rows.Count, 1).End(xlUp).Row).Value)
            MakeChanges shtName
        Next shtName
    End With
End Sub
Sub MakeChanges(shtName)
    On Local Error Resume Next
    With Sheets(shtName)
        .Range("A1") = shtName & " --- " & Now()  '  <-- fill in what you want to do
    End With
End Sub

 

[Luka_LB]

tlowry

thank you, your code works fine!

by the way you have taught me one more thing

that it's possible to write something in scopes Sub MakeChanges(shtName) it's totally new for me

thank you for your support and time

 

[Luka_LB]

tlowry

I'm facing the problem with modifying second code,

Sub MakeChanges(shtName)
On Local Error Resume Next    With Sheets(shtName)       
      '.Range("A1") = shtName & " --- " & Now()  '  <-- fill in what you want to do 
       [COLOR=#ff0000]Module1.Delete_Column [/COLOR] ' <--- This is the code I want to run on every sheet, but cant fit it in correctly :/
End With
End Sub

Module1.Delete_Column ' <--- This is the code I want to run on every sheet, but cant fit it in correctly :/

If I add my code, it runs only on 'Master' sheet on sheets listed in A:A
Could you assist on that?

 

[tlowry]

So here’s the deal.

Develop the code you want to execute on each worksheet in your list. The idea is to pass to the subroutine(s) the name of the sheet.

Delete_Column_Master (shtName)

Then have the subroutine do your modifications to the sheet.

Sub Delete_Column_Master(shtName)
    
    MsgBox "Ta Dar! from " & shtName
    Sheets(shtName).Columns(4).Interior.ColorIndex = 4

End Sub

You can put the subroutine into either the “ThisWorkbook” module; or in a separate Module. It is best to make sure that the name of this subroutine is unique. In that way, you will be sure that the correct subroutine is being run.

Putting it all together in code:

Sub modifySheets()
    Dim lr, shtName
    With Sheets("Master") '  <-- sheet name with other sheet names in first column
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For Each shtName In Application.WorksheetFunction.Transpose(.Range("A1:A" _
                                        & .Cells(Rows.Count, 1).End(xlUp).Row).Value)
            MakeChanges shtName
        Next shtName
    End With
End Sub
Sub MakeChanges(shtName)
    On Local Error Resume Next
    With Sheets(shtName)
        '.Range("A1") = shtName & " --- " & Now()  '  <-- fill in what you want to do
       
        Delete_Column_Master (shtName)
       
    End With
End Sub
Sub Delete_Column_Master(shtName)
   
    MsgBox "Ta Dar! from " & shtName
    Sheets(shtName).Columns(4).Interior.ColorIndex = 4
 
End Sub