Set variable to Apply Macro to multiple worksheets

[RosieRose]

Hi there, I have written a macro for a specific workbook to apply to multiple worksheets, I have about 30 other workbooks to apply this to and the sheet numbers will be different in each work book. All sheets that need to be formatted contain the word "plot" in the name. How do I set a variable to activate any worksheets with "plot" in their name? here is the macro I would like to modify: Sub MacroThroughsheets()
Dim i As Integer
For i = 56 To 70
Sheets(i).Activate
ActiveSheet.Columns("A:A").ColumnWidth = 2
ActiveSheet.Columns("B:B").ColumnWidth = 7
ActiveSheet.Columns("C:C").ColumnWidth = 4
ActiveSheet.Columns("D:D").ColumnWidth = 7
ActiveSheet.Columns("E:E").ColumnWidth = 10
ActiveSheet.Columns("F:F").ColumnWidth = 9
ActiveSheet.Columns("G:G").ColumnWidth = 4
ActiveSheet.Columns("H:T").ColumnWidth = 8.14
ActiveSheet.Columns("U:U").ColumnWidth = 2
Rows("1:51").Select
Selection.RowHeight = 15
ActiveSheet.PageSetup.PrintArea = "$A$1:$U$51"
Next i
End Sub
Thank you!

 

[Snakehips]

RosieRose,

Welcome to MrExcel.

Try....

Sub MacroThroughsheets()
Dim i As Integer
Dim sht As Worksheet


For Each sht In ThisWorkbook.Sheets
If LCase(sht.Name) Like ("*plot*") Then
With sht
.Columns("A:A").ColumnWidth = 2
.Columns("B:B").ColumnWidth = 7
.Columns("C:C").ColumnWidth = 4
.Columns("D:D").ColumnWidth = 7
.Columns("E:E").ColumnWidth = 10
.Columns("F:F").ColumnWidth = 9
.Columns("G:G").ColumnWidth = 4
.Columns("H:T").ColumnWidth = 8.14
.Columns("U:U").ColumnWidth = 2
.Rows("1:51").RowHeight = 15
.PageSetup.PrintArea = "$A$1:$U$51"
End With
End If
Next sht
End Sub

Hope that helps.

 

[RosieRose]

Hi, I keep getting the following message: Run-time error 13: type mismatch, this comes up on Next Sht.

 

[Snakehips]

Where do you have the new code?

As is, it should be in either in a sheet module or a code module of the sheet you are converting.

 

[RosieRose]

Hi Tony, I have tried running it both from in the module for the workbook, as well as in a module for my personal workbook. I was hoping I could save this to my personal workbook, thus it would be a general macro that I could apply to the 30+ workbooks I need to reformat. Thank you kindly for you help and patience, as I am new to writing codes for specific sheet selections

 

[Snakehips]

Rosie,

Copy the below into a module within your Personal workbook.

It will then act upon the Active Workbook.

Sub MacroThroughsheets()
'Will format the ACTIVE WORKBOOK
Dim i As Integer
Dim sht As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
If MsgBox("Are you happy to continue formatting workbook...... " & wb.Name & "  ?", vbYesNo, "Just Checking!") = vbYes Then
For Each sht In wb.Sheets
If LCase(sht.Name) Like ("*plot*") Then
With sht
.Columns("A:A").ColumnWidth = 2
.Columns("B:B").ColumnWidth = 7
.Columns("C:C").ColumnWidth = 4
.Columns("D:D").ColumnWidth = 7
.Columns("E:E").ColumnWidth = 10
.Columns("F:F").ColumnWidth = 9
.Columns("G:G").ColumnWidth = 4
.Columns("H:T").ColumnWidth = 8.14
.Columns("U:U").ColumnWidth = 2
.Rows("1:51").RowHeight = 15
.PageSetup.PrintArea = "$A$1:$U$51"
End With
End If
Next sht
End If
End Sub

 

[RosieRose]

Hi again, Tony. I copied the code into a module in my personal workbook. I am still getting the Run-time error 13 Type-Mismatch for the line of code: Next Sht

 

[Snakehips]

Hi Rosie,

I'm struggling to understand why this is not working at your end.
What version of Excel are you running?
Do you only have one instance running?
Code is in a code module of Personal?
How are you running the code?
Are you definitely running my latest code?
Have you tried crossing your fingers?

 

[RosieRose]

Hi Tony, to answer your questions : I am running Excel 2010, I only have one instance running, code is in a module of personal. I have tried running the code by pressing run in the VBA Basic screen. I have also tried playing it line by line using F8. Could it have something to do with declaring i as a integer and not referring to it later in the code? I have tried crossing my fingers....unfortunately, still no luck. I do appreciate you time and help, thank you.

 

[RosieRose]

I made a small change, if I play it through line by line using the step into feature, I still get the same message to debug, yet in I continue to pressF8, I can eventually run the code through the whole workbook.... getting closer.

Sub MacroThroughsheets()
'Will format the ACTIVE WORKBOOK
Dim i As Integer
Dim sht As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
If MsgBox("Are you happy to continue formatting workbook...... " & wb.Name & "  ?", vbYesNo, "Just Checking!") = vbYes Then
For Each sht In wb.Sheets
If sht.Name Like ("*plot*") Then
sht.Activate
ActiveSheet.Columns("A:A").ColumnWidth = 2
ActiveSheet.Columns("B:B").ColumnWidth = 7
ActiveSheet.Columns("C:C").ColumnWidth = 4
ActiveSheet.Columns("D:D").ColumnWidth = 7
ActiveSheet.Columns("E:E").ColumnWidth = 10
ActiveSheet.Columns("F:F").ColumnWidth = 9
ActiveSheet.Columns("G:G").ColumnWidth = 4
ActiveSheet.Columns("H:T").ColumnWidth = 8.14
ActiveSheet.Columns("U:U").ColumnWidth = 2
Rows("1:51").Select
Selection.RowHeight = 15
ActiveSheet.PageSetup.PrintArea = "$A$1:$U$51"

End If
Next sht
End If
End Sub