CODEWITHSUNDEEP
vbaexcel
noob_coder
noob_coder

Reputation: 13

Formatting Macro doesnt work on all worksheets

Below is part of my code that deals with formatting the output generated. So, to put it concisely, my macro pulls data from various sources, compares them and generates statistics like Market variance and catch-up etc. in multiple worksheets. What I need it to do is a format a range of cells as "Percentage" in all worksheets. What's happening is that the formatting only applies itself in the first worksheet generated and not in the subsequent worksheets. The part of code that formats into "comma" works fine on all sheets but not percentage. I'm not really sure what the problem is. I've tried tweaking but with no success. I'm really pressed for time and need this to work as soon as possible, so I apologize in advance if this question exists already. PFB code snippet:

ActiveWorkbook.Sheets.Select
Range(Cells(100, 2), Cells(142, MarketRangeColumn + 10)).Select
Selection.Style = "Comma"
Selection.numberformat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.numberformat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range(Cells(143, 2), Cells(146, MarketRangeColumn + 10)).Select
Selection.Style = "Percent"

Thanks in advance guys!

Upvotes: 0

Views: 116

Answers (1)

Absinthe
Absinthe

Reputation: 3391

When you select all the sheets like that the code has no way to determine which sheet you want the code to run on (though your approach is logical VBA just doesn't work like that, in fact nearly all programming languages require you to specify the object you want to use), so it uses the first in the selection.

Use a for loop to move through the sheets. Also, you don't need to select something to act on it and you can use a With block to shorten the code a bit:

For s = 1 To Sheets.Count

    With Sheets(s).Range(Cells(100, 2), Cells(142, MarketRangeColumn + 10))
        .Style = "Comma"
        .NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        .NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    End With

    Sheets(s).Range(Cells(143, 2), Cells(146, MarketRangeColumn + 10)).Style = "Percent"

Next s

Upvotes: 0

Related Questions