Repeating a Macro across an Entire Workbook

Nicole_Michelle01

Board Regular
Joined
Nov 9, 2005
Messages
50
I have 5 worksheets (Sheet1, Sheet2, Sheet3, Sheet4, Sheet5) and I want the following macro run for each worksheet:

Sub SetupFile()
For intcount = 1 To 256
If InStr(1, Cells(1, intcount), "userpsswd") > 0 Then
Cells(1, intcount).EntireColumn.Delete
End If
Next intcount
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Review Notes"
Columns("A:A").Select
Selection.Columns.AutoFit
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = "Dept."
End Sub

And also, if I want to add additional keywords to the InStr row ie "userpsswd" "psswd_expdate" "psswd_createdate", what would that look like?

thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Would this work for you?
Code:
Option Explicit
Sub SetupAllFiles()
Dim ws As Worksheet, KeyWord As String

KeyWord = InputBox("Enter Keyword to use in macro (e.g., userpsswd)")
For Each ws In ActiveWorkbook.Worksheets
    Call SetupFile(KeyWord, ws)
Next ws
End Sub


Sub SetupFile(KeywordPassed As String, ActiveWs As Worksheet)
Dim intcount As Integer

For intcount = 1 To 256
    If InStr(1, ActiveWs.Cells(1, intcount), KeywordPassed) > 0 Then
        ActiveWs.Cells(1, intcount).EntireColumn.Delete
    End If
Next intcount
ActiveWs.Columns("A:A").Insert Shift:=xlToRight
ActiveWs.Range("A1").FormulaR1C1 = "Review Notes"
ActiveWs.Columns("A:A").Columns.AutoFit
ActiveWs.Columns("E:E").Insert Shift:=xlToRight
ActiveWs.Range("E1").FormulaR1C1 = "Dept."
End Sub

The code prompts the user for the keyword to use and will apply that to all sheets in the active workbook.

Regards,
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330

ADVERTISEMENT

Nicole_Michelle01 said:
What if the keyword is always the same every time? What would that look like?

The user is prompted for the keyword. If you don't want the prompt, change
Code:
KeyWord = InputBox("Enter Keyword to use in macro (e.g., userpsswd)")

to
Code:
KeyWord = "your keyword"
 

Nicole_Michelle01

Board Regular
Joined
Nov 9, 2005
Messages
50

ADVERTISEMENT

And I want the macro to run to do the column delete and adds all at once without me having to click the next sheet then re-run the macro. But if this can't be done, that's okay.
 

Nicole_Michelle01

Board Regular
Joined
Nov 9, 2005
Messages
50
Ok, I'll make the change but do I comma separate the whole list of keywords ie

KeyWord = "keyword1", "keyword2", "keyword3", "keyword4" ...etc?
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Nicole_Michelle01 said:
And I want the macro to run to do the column delete and adds all at once without me having to click the next sheet then re-run the macro. But if this can't be done, that's okay.

Have you tried the code? It will cycle through all the worksheets in the workbook.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,806
Messages
5,833,778
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top