Apply a Macro to a list of sheets

Fener · Feb 9, 2015

Hi, i'm sorry for my bad english, but i'm Italian.
I would like to apply a macro to a list of sheets.
I mean, I have 30 sheets, and the their names are written in the sheet "Start", Range("A5:A100"). In a sheet "Setting" i have the macro that I would like to apply to every sheets (and the specific name of each sheet is in that range)...
How can I do it?

Thank you very much

NeonRedSharpie · Feb 9, 2015

Code:

Sub macro(sheetName As String)

    Sheets(sheetName).Select
    
    'HERE IS WHERE YOUR MACRO GOES


End Sub


Sub populateList()


    For x = 5 To 100
        Call macro(Cells(x, 1).Text)
    Next x


End Sub

You could do something like that so you don't have to re-write the macro?

Fener · Feb 9, 2015

I tried, but it's not working...
I would like the macro read each company in the sheet "Start", and for each company, it enters in the specific sheet's name and apply the macro that comes from sheet "setting".
I would like to do in this way in order that if I want to modify some formula, I will work only in the sheet "Setting" (as test) and after I will apply with the macro, the same layout/formula/setting of the sheet "setting" to the List of sheets contained in the sheet "start"

I hope I explained a bit better.

Thank you for the help

Greg Truby · Feb 9, 2015

I have moved this thread to "questions in other languages". Please re-state your question in Italian and please clarify a little more the third paragraph of your most recent post:

I would like to do in this way in order that if I want to modify some formula, I will work only in the sheet "Setting" (as test) and after I will apply with the macro, the same layout/formula/setting of the sheet "setting" to the List of sheets contained in the sheet "start"

Ho spostato la discussione alle "domande in altre lingue". Inserisci nuovamente indicare la tua domanda in italiano e vi prego di chiarezza un po 'di più, terzo comma, del più recente post:

Mi piacerebbe fare in questo modo, in modo che se voglio modificare una formula, lavorerò solo in il foglio "Setting" (come test) e dopo io applicherò con la macro, il layout stesso / formula / impostazione del foglio "impostazione" per la lista di fogli contenuti nel foglio "start"

Greg Truby · Feb 9, 2015

Also, please clarify exactly want you mean here:

Inoltre, si prega di chiarire esattamente vuole dire qui:

Fener said:
In a sheet "Setting" i have the macro

Fener · Feb 9, 2015

Ho 500 fogli:
"Start" contiene una lista di diverse società (più di 500)
"Setting" è un foglio test in cui scrivo e testo la macro che poi applicherò agli altri fogli, i cui nomi sono contenuti nel foglio "start"

Quello che vorrei fare è:
Per ogni nome di foglio contenuto in "Start", applicare la macro (testata nel foglio "Setting") ad ogni specifico nome/foglio contenuto nella lista.

Greg Truby · Feb 9, 2015

This is a very basic version of a macro that would do the type of activity you are describing. Note that it does not have any kind of error handling. So if a sheet is missing, the execution will stop and you will be thrown into the debugger.

Questa è una versione di base di una macro che avrebbe fatto il tipo di attività che si sta descrivendo. Si noti che non ha alcun tipo di gestione degli errori. Quindi, se un foglio manca, l'esecuzione si ferma e si sarà gettato nel debugger.

Sub Example1() '// extremely basic Dim celItem As Excel.Range, _ wsTarget As Excel.Worksheet For Each celItem In Worksheets("Start").Range("A5:A500").Cells Set wsTarget = Worksheets(celItem.Value) RepeatedProcedure wsTarget Next celItem End Sub Sub RepeatedProcedure(ByRef wsTarget As Excel.Worksheet) '// This would be the procedure that you wish to '// execute for each worksheet on the list. ' '// For demonstration purposes this routine just '// changes the color of the first four cells. With wsTarget.Range("A1:B2").Interior Select Case .Color Case vbCyan: .Color = vbYellow Case vbYellow: .Color = vbGreen Case vbGreen: .Color = vbRed Case vbRed: .Color = vbBlue Case Else: .Color = vbCyan End Select End With End Sub

Greg Truby · Feb 9, 2015

We can improve on this slightly by adding some code that will keep execution from stopping if there is no sheet on the list.

Possiamo migliorare questo leggermente aggiungendo qualche codice che manterrà esecuzione di arrestarsi se non c'è foglio sulla lista.

 Sub Example2() '// code so that simple error don't stop execution. Dim celItem As Excel.Range, _ wsTarget As Excel.Worksheet '// we're simply going to ignore any errors. On Error Resume Next For Each celItem In Worksheets("Start").Range("A5:A500").Cells '// we need to set the target worksheet to nothing. '// if we don't and we error out, we'll run our '// repeated macro a second time on the same worksheet. Set wsTarget = Nothing '// no sense doing anything if the cell is empty If Len(celItem.Value) <> 0 Then Set wsTarget = Worksheets(celItem.Value) '// if the sheet exists then our object '// variable is set, otherwise it is NOTHING. If Not wsTarget Is Nothing Then RepeatedProcedure wsTarget End If '// (len(cell) <> 0) Next celItem End Sub

Greg Truby · Feb 9, 2015

Or we can even add in a little error handling that will mark those cells that contain invalid names.

Oppure possiamo anche aggiungere in un po 'di gestione degli errori che segnerà quelle celle che contengono i nomi validi.

 Sub Example3() '// do more than ignore errors, mark them. Dim celItem As Excel.Range, _ cmtError As Excel.Comment, _ strComment As String, _ wsTarget As Excel.Worksheet '// we're simply going to ignore any errors. On Error GoTo ErrorHandler For Each celItem In Worksheets("Start").Range("A5:A500").Cells '// we need to set the target worksheet to nothing. '// if we don't and we error out, we'll run our '// repeated macro a second time on the same worksheet. Set wsTarget = Nothing '// no sense doing anything if the cell is empty If Len(celItem.Value) <> 0 Then Set wsTarget = Worksheets(celItem.Value) '// if the sheet exists then our object '// variable is set, otherwise it is NOTHING. If Not wsTarget Is Nothing Then RepeatedProcedure wsTarget End If '// (len(cell) <> 0) EndOfLoop: Next celItem Exit Sub ErrorHandler: '¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨¨ With celItem On Error Resume Next Set cmtError = .Comment '// If the cell doesn't have a comment, we'll add a comment. '// If it DOES already have a comment, we'll tack our text onto the '// end of the existing comment. If cmtError Is Nothing Then .AddComment Text:="This worksheet does not exist." Else Let strComment = cmtError.Text cmtError.Text vbCrLf & vbCrLf & "This worksheet does not exist.", Len(strComment) + 4 End If '// rather than actually overwriting any existing cell formatting, we '// will add a conditional format that will turn the cell pink. '// We can very easily go in and delete this by hand. Much easier '// to clean up rather than trying to undo formatting applied directly '// to the cell. With .FormatConditions .Add Type:=xlExpression, Formula1:="=TRUE" With .Item(.Count) .SetFirstPriority .StopIfTrue = True With .Interior .PatternColorIndex = xlAutomatic .Color = 10066431 .TintAndShade = 0 End With '// .interior End With '// fc.item

End With '// format conditions End With '// cell item On Error GoTo ErrorHandler Resume EndOfLoop End Sub

Greg Truby · Feb 9, 2015

Fener said:
"Setting" è un foglio test in cui scrivo e testo la macro che poi applicherò agli altri fogli, i cui nomi sono contenuti nel foglio "start"

The way you originally wrote this in English, it can be interpreted to mean that "Setting" is a macro sheet, something that was in Excel about twenty years ago. However, the XLM 4.0 macro language was replaced with VBA back in 1997. I assume you did NOT mean this is a macro sheet, but that you mean something else. However I am unclear as to your meaning.

Il modo in cui è stato originariamente scritto questo in inglese, può essere interpretato nel senso che "Setting" è un foglio macro, qualcosa che era in Excel una ventina di anni fa. Tuttavia, il linguaggio macro XLM 4.0 è stato sostituito con VBA nel 1997. Presumo che non intendevo questo è un foglio di macro, ma che dire qualcosa di diverso. Comunque io sono poco chiaro per il vostro senso.

Apply a Macro to a list of sheets

Fener

New Member

Excel Facts

NeonRedSharpie

Well-known Member

Fener

New Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Fener

New Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

Apply a Macro to a list of sheets

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock