In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
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
Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.
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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll
718 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.