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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,455
Messages
5,572,223
Members
412,448
Latest member
ManuW
Top