Adjust the macro code in excel visual basics

Question

Good Afternoon everyone,

Currently I am dealing with the issue of a macro that always refers to the one excel worksheet in which I have recored the macro. I have around 300 worksheets that I would like to use the macro on. My question is how I can adjust the code in order to refer to any of these worksheets and not just the one in which I have recorded it.

CASE

In the following I have listed part of the code, whereas the crucial part is shown by the definition "Sheets("6110").Select", in which the macro always refers to the sheet "6110" and not to the one im working with. Furthermore, all worksheets are protected with the same password, means I would like to implement the process in which the macro unprotects the sheet in the beginning of the code and protects it again at the end of the operation.

(Macro code visual basics)

Sheets("6110").Select
ActiveSheet.Unprotect
Range("C5:C13,D4:P13").Select
Range("D4").Activate
With Selection.Font
    .Name = "Calibri"
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
End With

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 12 '19 at 07:19
You might need to expand a little bit what you are trying to achieve. `in order to refer to any of these worksheets` without any condition is hard to guess... Do you want to run the same exact macro on all of them at once? or whenever you want on each individually? Follow @Pᴇʜ advise, though I think you should read one of the countless VBA Tutorials before trying this. — FAB, Sep 12 '19 at 07:31
I agree that you should take @Pᴇʜ advise as well. But to achieve what you need just deleting the first line `Sheets("6110").Select` would do the trick to have it run on the current sheet rather than go to that one. If you want to do them all at once, a simple loop will work. — Plutian, Sep 12 '19 at 07:34
Why read a tutorial when you can watch one: [Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)](https://www.youtube.com//watch?v=c8reU-H1PKQ&index=5&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5) — TinMan, Sep 12 '19 at 07:43
@JelleManne you are trying to loop all worksheets in the workbook and apply the above formatting in the `Range("C5:C13,D4:P13")`? — Error 1004, Sep 12 '19 at 07:59
@FAB I would like to use the macro whenever I want individually. And I already tried to use the code by deleting the definition. I have to add to my question, that the worksheets are all protected with the same password and therefore need to be unprotected and protected in order to be adjusted, can that be done by a macro as well? And I apologise if my description of the problem is not the best but I am not the most experienced coder obviously. — JelleManne, Sep 12 '19 at 08:05

score 0 · Answer 1 · answered Sep 12 '19 at 08:09

Hope the below code help you to understand the main points:

Option Explicit

Sub LoopAllSheets()

    Dim ws As Worksheet
    Dim rng As Range

    'You loop all the worksheets in the workbook
    For Each ws In ThisWorkbook.Worksheets

        'Create a with statement referring the sheet you are currenlty loop
        With ws

            'Unprotect the loop sheet may need to add your password
            .Unprotect

            'Create a range you want to use
            Set rng = .Range("C5:C13,D4:P13")

            'You refer to the range fonts
            With rng.Font
                .Name = "Calibri"
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .TintAndShade = 0
                .ThemeFont = xlThemeFontMinor
            End With

        End With

    Next ws

End Sub

score 0 · Answer 2 · answered Sep 12 '19 at 08:10

As I understand the question, you want to Format each worksheet's font etc.:

Option explicit
public Sub alignFont()
    Dim ws As Worksheet
    For each ws in ThisWorkbook.Worksheets
        ws.Unprotect
        with ws.Cells
            .Name = "Calibri"
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor  
        end with
    next ws
end Sub

Some sidenotes:

- Always use Option explicit at the beginning of your modules
- Do take the advise from the comments to not use select, this is not needed, see above
- Recording macros can be usefull to find certain properties etc - but you should usually not just try to Twist the recorded macro but start from scratch and check if you really want to do what the macro did. Eg do you really want to Change the strikethrough? This was added by the macro, but if all you care about is font etc, then this may lead to some very wrong Information in your EXcel.

Adjust the macro code in excel visual basics

2 Answers2