Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies. Only from Platform Scholar.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Make sub to format and total ranges
Posted on 2016-08-10
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?
After.xlsx
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
After.xlsx
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
2- +2
9 Comments
Active today
Please try this.....
Sub ApplyFormatAndFormuals()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Range("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
If rng.Cells(1).Row <> 1 Then
rng.Cells(1).Copy rng.Cells(1).Offset(-1, 2)
With rng.Cells(1).Offset(-1, 2).Font
.Bold = True
.Size = 14
.Name = "Calibri"
End With
rng.Cells(rng.Rows.Count + 1).Offset(0, 5).Resize(, 8).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
rng.Cells(rng.Rows.Count + 1).Offset(0, 5).Resize(, 8).Font.Bold = True
End If
Next rng
Application.ScreenUpdating = True
End Sub
Active today
HI,
Since the number of lines is variable, then try
Since the number of lines is variable, then try
Sub ApplyFormatAndFormuals()
Dim rng As Range
Application.ScreenUpdating = False
For Each rng In Range("A:A").SpecialCells(xlCellTypeConstants, 2).Areas
If rng.Cells(1).Row <> 1 Then
rng.Cells(1).Copy rng.Cells(1).Offset(-1, 2)
With rng.Cells(1).Offset(-1, 2).Font
.Bold = True
.Size = 14
.Name = "Calibri"
End With
rng.Cells(rng.Rows.Count + 1).Offset(0, 5).Resize(, 8).FormulaR1C1 = "=SUM(R[-" & rng.Rows.Count & "]C:R[-1]C)"
rng.Cells(rng.Rows.Count + 1).Offset(0, 5).Resize(, 8).Font.Bold = True
End If
Next rng
Application.ScreenUpdating = True
End Sub
Active today
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
Thanks
Rob H
Before.xlsx
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
There are times when I have encountered the need to decompress a response from a PHP request.
This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 3 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.