Home Creating a summary sheet for financials using a macro
Reply: 0

Creating a summary sheet for financials using a macro

user2392
1#
user2392 Published in May 27, 2018, 3:35 am

I have a workbook that have two financial sheets for 50 facilities. Each facilities has two tabs one tab ends in Fvar and the other tab ends in LTM. For each facilities the sheets are identical. I want to create a macro that creates a summary for both sheets that sums across each cell for the Fvar and LTM for specific ranges. So in short I want a summary for every facility FVAR tab and a summary for every facility LTM tab. I recorded myself doing this process for FVAR:

     Sheets("AL-Monroe County Hospit-Fvar").Select
Sheets("AL-Monroe County Hospit-Fvar").Copy Before:=Sheets(3)
Sheets("AL-Monroe County Hospit-Fva (2").Select
Sheets("AL-Monroe County Hospit-Fva (2").Name = "Summary-Fvar2"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Summary"
Range("G12").Select
ActiveCell.FormulaR1C1 = _
    "=SUM('AL-Monroe County Hospit-Fvar'!RC,'AL-Riverview Regional M-Fvar'!RC,'FL-Bethesda Hospital Ea-Fvar'!RC,'FL-Bethesda Hospital We W-Fvar'!RC,'FL-Jackson Health Syste-Fvar'!RC,'FL-Mount Sinai Medical -Fvar'!RC,'FL-Mount Sinai Medical-Fvar'!RC,'KY-Barbourville ARH Hos-Fvar'!RC,'KY-Hazard ARH Regional-Fvar'!RC,'KY-Highlands Regional M-Fvar'!RC,'KY-Mary Breckinridge A" & _
    "R-Fvar'!RC,'KY-McDowell ARH Hospita-Fvar'!RC,'KY-Middlesboro ARH Hosp-Fvar'!RC,'KY-Morgan County ARH Ho-Fvar'!RC,'KY-Tug Valley ARH Hospi-Fvar'!RC,'KY-Twin Lakes Regional-Fvar'!RC,'KY-Whitesburg ARH Hospi-Fvar'!RC,'MS-Baptist Medical Cent -Fvar'!RC,)" & _
    ""

Range("G12").Select
Application.CutCopyMode = False
Selection.Copy
Range("F12:H17,N12:O17,S12:T17,X12:Y17,F21:H22,N21:O22,S21:T22,X21:Y22,F28:H35,N28:O35,S28:T35,X28:Y35,F40:H42,N40:O42,S40:T42,X40:Y42,F50:H52,N50:O52,S50:T52,X50:Y52,F59:H59,N59:O59,S59:T59,X59:Y59,F67:H67,N67:O67,S67:T67,X67:Y67,F63:H65,N63:O65,S63:T65,X63:Y65").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

Range("A2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select

End Sub

Ok so I've updated the code to compress the ranges selection now my only question is there a formula or a vba way to insert my ActiveCell formula summing across all FVar sheets by manual selection to more of a automated one. So basically is there a formula to add all worsheets with Fvar at the end? Or a VBA solution?

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.302191 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO