Repeating a Macro across an Entire Workbook

[Nicole_Michelle01]

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!

 

[Barrie Davidson]

Would this work for you?

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,

 

[Nicole_Michelle01]

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

 

[Nicole_Michelle01]

Will this also insert the 2 columns on every worksheet?

 

[Barrie Davidson]

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

KeyWord = InputBox("Enter Keyword to use in macro (e.g., userpsswd)")

to

KeyWord = "your keyword"

 

[Barrie Davidson]

Will this also insert the 2 columns on every worksheet?

Yes.

 

[Nicole_Michelle01]

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]

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]

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.

 

[Nicole_Michelle01]

got it! yes it works! now how do I add all the keywords at once?