Formatting multiple worksheets with varying rows

[rpmurray]

Good afternoon. I am attempting to add some code to an already massive macro, but I am totally stuck. I have about 20 worksheets in this workbook; sheets 3 through sheets 17 are identical. Sheets 1 & 2 are summary sheets and 18-20 are background calcs. I need to format sheets 3-17 only, and depending on the region the workbook pertains to, the last row will always be different (it will be consistent throughout a single workbook, but I'll be running workbooks for different regions). So, for each workbook, I need to identify which is the last row of data on Sheets 3-17, and then format that row to BOLD. I need to find the last row on sheet 3 since some of the other sheets have "If/Then" formulas in every row down to Row 250, so finding the last row of data on those sheets doesn't work. I know how to find the last row on sheet 3, but how do I use that info on the remaining sheets? I think I need to set a variable like (Myvariable.Row), but I don't know how to use that in a command. Can someone help me write a macro that will format sheets 3 through sheets 17 that will bold the last row on each sheet? There are tons of examples on the net, but none that fit my need exactly. I was hoping I could just activate the last row on sheet 3 and then select sheets 3 through 17 and then say Selection.Font.bold = True, but I guess that will only work on the active sheet, not all subsequent sheets. Apparently I need to loop through every sheet. Any help is greatly appreciated. Thanks!

 

[DanteAmor]

I did not understand very well, you want to find the last row with data from sheet3, and put it bold. Assuming that the last row with data from sheet3 is 25, you want the other sheets to also line 25 in bold.


Or do you want to find the last row with data on each sheet and put it bold?
It may be that sheet3 is 25, leaf 4 is 47, etc.


Which column should be used to know which is the last row with data.
I have already commented, you have formulas, the formula can return "" and then that is not the last row, the last row will be when the formula returns something different to ""

 

[rpmurray]

Yes, if the last row on sheet 3 is 25, then I want to bold line 25 on sheets 3-17. I know how to use VBA to identify the last row, but that’s where I get stuck. I don’t know how to use that info for the rest of the sheets. Thanks.

 

[rpmurray]

I think I have an idea. Can I format sheet three, and then copy the entire sheet and paste formatting on sheets for through 17? I think that would work.

 

[DanteAmor]

Try this

Sub format_bold()
    lr = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
    Sheets(Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)).Select
    Rows(lr).Select
    Selection.Font.Bold = True
    Sheets(3).Select
End Sub

If this instruction is not what you need for the last row with data, then you can use the one you already know.

lr = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row

I know how to use VBA to identify the last row