Link to home
Create AccountLog in
Avatar of NVIT
NVITFlag for United States of America

asked on

Make sub to format and total ranges

Before.xlsx shows the current state.
After.xlsx shows what I want.

In Before, for User 1, I recorded the macro to make it look like After.

Is there a way to make this more 'generic' so that it runs for the other Users, regardless of how many?

Sub Title_And_Total_Group()
    Range("A4").Select
    Selection.Copy
    Range("C3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("F8").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
    Range("F8").Select
    Selection.Copy
    Range("F8:M8").Select
    ActiveSheet.Paste
    Selection.Font.Bold = True
    Selection.End(xlToLeft).Select
    Range("A11").Select
End Sub

Open in new window

Before.xlsx
After.xlsx
Avatar of aikimark
aikimark
Flag of United States of America image

.xlsx can not contain VBA code.  You will need to save as .xlsm format
Avatar of NVIT

ASKER

I know that. Can you help w/ a macro solution?
Ah.  I'll look at it tomorrow AM.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Good point Rgonzo. I missed that.
Thanks for tweaking the code accordingly. :)
You can get a similar result without VBA and using the Subtotal Wizard or with a slight change to the data format, you could use a Pivot Table.

Thanks
Rob H
Before.xlsx
Avatar of NVIT

ASKER

Thank you, Neeraj and Rgonzo!
Avatar of NVIT

ASKER

Is it easy enough for you to revise your code and add bottom borders on the last 2 rows as shown?
User generated image
Or, should I open another question?