[Webinar] Learn how to a build a cloud-first strategyRegister Now


Updating formuals afer a copy

Posted on 2014-07-18
Medium Priority
Last Modified: 2014-07-18
This macro is executed on the form button labeled “Add Summary Year”.
Sub InsertNewYearConsolidated()
    Dim ws As Worksheet, rw As Long
    Set ws = Worksheets("Consolidated")
    'Insert 12 rows above Totals and the spacer row
    rw = ws.Range("A:A").Find("Totals").Row
    ws.Rows(((rw - 1) & ":" & (rw + 10))).Insert Shift:=xlDown
    'copy the format from the first 12 rows (6 to 17)
    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
   Application.CutCopyMode = False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Open in new window

The above code produces the following results (I’ve provided just a portion)
 FormulasIn row 31 the formula in place do not reflect the new the changes in the ranges of the formulas. Let me give you an example. Before I update, the formula in C19 is =Sum(C6:C17). After the update in cell C31 the formula should be =Sum(C6:C29) instead of =Sum(C6:C17). I need a way to update the formulas when the Consolidated sheet is added
Question by:Frank Freese
  • 3
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 total points
ID: 40205284
Change your SUM formulas to include the row immediately above the formula.  For example,
C19:  =SUM(C6:C18)


Author Comment

by:Frank Freese
ID: 40205297
That I understand - but the client wants this done for them. I just don't know how to macro update the formulas in Row 31.
As I've mentioned before, the client thinks a macro is a fish. With that being said I guess a micro is a small fish :)
LVL 24

Accepted Solution

Ejgil Hedegaard earned 1800 total points
ID: 40205303
Change formula to Sum(C6:C18) to include empty row 18, then the formula will fit the new range when the rows are inserted.

Besides that, don't use the fixed range Range("A18:A29").Select when you insert the values.
Use the dynamic range like the code above copying the format.
ws.Range("A" & (rw - 1)).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Open in new window

    ws.Range("A" & (rw - 1)).Select
    Selection.PasteSpecial Paste:=xlPasteValues

Open in new window


Author Comment

by:Frank Freese
ID: 40205390
that works! thank you

Author Closing Comment

by:Frank Freese
ID: 40205396
thanks folks

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question