A macro to consolidate many budgets into summary sheet

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
Hello all. I have tried to take code from other board posts and make it work with what I am trying to do and am failing miserably. This macro is not that difficult, at least I don't think so compared to what I have seen done. I have 100 budgets that need to be consolidated into a seperate summary sheet. Column D5 on the first html, will determine what dept the budget comes from. So column b on the second html will change when the range has been exhausted on each budget. The range is B9:B132 from the first html. The directory in which the budgets are stored is g:\accounting\private\msdacct\planning\2011\budgets\budgets reviewed.

Thanks in advance

Excel Workbook
BCDEFGH
1
2
3
4
5DEPARTMENT: 69311INFORMATION SYSTEMS
6MANAGER:
7
8ACCOUNTDESCRIPTIONQ1Q2Q3Q42010
9502104 - UAOTH NON-OH EXP - TAXES0
10511101 - UASALARY & WAGE - INDIRECT LABOR96,98099,24199,24199,241394,703
11513004 - UAEMPLOYEE - TRAINING3,5003,5003,5003,50014,000
12520001 - UATRAVEL EXPENSE - TRANSPORT AIR3,0003,0003,0003,00012,000
13520002 - UATRAVEL EXPENSE - TRNSPORT LAND1,2001,2001,2001,2004,800
14520003 - UATRAVEL EXPENSE - ACCOMMODATION3,0003,0003,0003,00012,000
15520004 - UATRAVEL EXPENSE - MEALS1,0801,0801,0801,0804,320
16520005 - UATRAVEL EXPENSE - ENTERTAINMENT0
17520007 - UATRAVEL EXPENSE - GENERAL200200200200800
18531003 - UARENTS - OFFICE EQUIPMENTS0
19531012 - UAREPAIR & MAINTENANCE - MACH1,7501,7501,7501,7507,000
20531013 - UAREPAIR & MAINTENANCE0
21531091 - UACOMMODITY - PPV0
22531201 - UATELEPHONE10,04510,04510,04510,04540,180
23531203 - UAIT NETWORK12,87510,45010,45010,45044,224
24531204 - UASOFTWARE8,9148,3828,3829,38235,061
25531411 - UANON-OPERATING SUPPLIES4254254254251,700
26532103 - UAOUTSOURCED SERVICE - ACCOUNTIN0
27532104 - UAOUTSOURCED SERVICES - OTHER0
28532105 - UAOUTSOURCED SERVICE - CONSULTIN25,00023,00025,00020,00093,000
29552101 - UAFREIGHT - INBOUND75757575300
30552201 - UAFREIGHT - OUTBOUND150150150150600
31591501 - UADEPRECIATION - EQUIPMENT34,32831,79331,79329,355127,269
1090
1100
1110
1120
1130
1140
1150
1160
1170
1180
1190
1200
1210
122512001DIRECT - SOCIAL SECURITY00000
123512002DIRECT - UNEMPLOYMENT INSURANC00000
124512005DIRECT - MEDICAL00000
125512006DIRECT - LIFE INSURANCE00000
126512101INDIRECT - SOCIAL SECURITY7,4197,5927,5927,59230,195
127512102INDIRECT - UNEMPLOYMENT INSUR1,5451,5451,5451,5456,180
128512105INDIRECT - MEDICAL13,01713,01713,01713,01752,069
129512106INDIRECT - LIFE INSURANCE8948948948943,575
130512201DIRECT - 401K00000
131512301INDIRECT - 401K6,8376,9966,9966,99627,824
132TOTAL232,232227,335229,335222,896911,799
Budget




And consolidate into this

Excel Workbook
ABCDEFGHI
2UA11111503201LABOR ABSORPTION(128,262)(120,008)(109,641)(111,789) (469,700.00)
3UA11111511001SALARY & WAGE - DIRECT LABOR96,67490,45366,11267,406 320,645.50
4UA11111511003VACATION - DIRECT LABOR2,91310,19614,72712,462 40,298.16
5UA11111511007SAL&WAGE PREMIUM-DIRECT LABOR4,3944,1123,0053,064 14,574.80
6UA11111511101SALARY & WAGE - INDIRECT LABOR15,52115,98715,98715,987 63,481.02
7UA11111511107SAL&WAGE PREMIUM-INDIRECT LAB1,0001,0001,0001,000 4,000.00
8UA11111531012REPAIR & MAINTENANCE - MACH250250250250 1,000.00
9UA11111531306NON-CAPITALIZED TOOLS800800800800 3,200.00
10UA11111531399CHEMICAL - OPERATING SUPPLIES3,0003,0003,0003,000 12,000.00
11UA11111531401OPERATING SUPPLIES3,0003,0003,0003,000 12,000.00
12UA11111531411NON-OPERATING SUPPLIES2,0002,0002,0002,000 8,000.00
13UA11111532104OUTSOURCED SERVICES - OTHER150150150150 600.00
14UA11111591101DEPRECIATION - MACHINES35,88035,33535,33435,335 141,882.68
15UA11111512001DIRECT - SOCIAL SECURITY7,9558,0146,4146,344 28,727.16
16UA11111512002DIRECT - UNEMPLOYMENT INSURANC2,7812,7812,1632,163 9,887.20
17UA11111512005DIRECT - MEDICAL23,43123,43118,22418,224 83,309.65
18UA11111512006DIRECT - LIFE INSURANCE1,6091,6091,2511,251 5,720.00
19UA11111512101INDIRECT - SOCIAL SECURITY1,2641,2991,2991,299 5,162.30
20UA11111512102INDIRECT - UNEMPLOYMENT INSUR309309309309 1,235.90
21UA11111512105INDIRECT - MEDICAL2,6032,6032,6032,603 10,413.71
22UA11111512106INDIRECT - LIFE INSURANCE179179179179 715.00
23UA11111512201DIRECT - 401K7,3307,3855,9115,846 26,471.80
24UA11111512301INDIRECT - 401K1,1651,1971,1971,197 4,757.01
25UA11121503201LABOR ABSORPTION (234,120.99)(219,016)(200,136)(203,912) (857,184.90)
26UA11121511001SALARY & WAGE - DIRECT LABOR 203,945.14190,787174,340177,630 746,702.36
27UA11121511003VACATION - DIRECT LABOR 2,858.812,2402,0462,085 9,229.84
28UA11121511007SAL&WAGE PREMIUM-DIRECT LABOR 2,970.072,3272,1262,166 9,589.07
29UA11121511101SALARY & WAGE - INDIRECT LABOR 22,851.0023,53623,53623,536 93,459.00
30UA11121511107SAL&WAGE PREMIUM-INDIRECT LAB 200.00200200200 800.00
31UA11121520001TRAVEL EXPENSE - TRANSPORT AIR 500.00500 1,000.00
32UA11121520003TRAVEL EXPENSE -ACCOMODATIONS 500.00500 1,000.00
33UA11121520004TRAVEL EXPENSE -MEALS 250.00250 500.00
34UA11121531012REPAIR & MAINTENANCE - MACH 350.00350350350 1,400.00
35UA11121531013REPAIR & MAINTENANCE 1,000.001,0001,0001,000 4,000.00
36UA11121531303OFFICE SUPPLIES 50.00505050 200.00
37UA11121531399CHEMICAL - OPERATING SUPPLIES 1,000.001,0001,0001,000 4,000.00
38UA11121531400PAINT - OPERATING SUPPLIES 250.00250250250 1,000.00
39UA11121531401OPERATING SUPPLIES 3,500.003,5003,5003,500 14,000.00
40UA11121531404OUTSIDE OPERATION EXPENSES 5,000.005,0005,0005,000 20,000.00
41UA11121531411NON-OPERATING SUPPLIES 2,500.002,5002,5002,500 10,000.00
42UA11121552201FREIGHT - OUTBOUND 500.00500500500 2,000.00
43UA11121591101DEPRECIATION - MACHINES 29,439.0029,43929,43929,057 117,374.00
44UA11121512001DIRECT - SOCIAL SECURITY 16,047.7114,94513,65613,914 58,562.38
45UA11121512002DIRECT - UNEMPLOYMENT INSURANC 5,870.535,8715,8715,871 23,482.10
46UA11121512005DIRECT - MEDICAL 49,465.1149,46549,46549,465 197,860.43
47UA11121512006DIRECT - LIFE INSURANCE 3,396.253,3963,3963,396 13,585.00
48UA11121512101INDIRECT - SOCIAL SECURITY 1,763.401,8161,8161,816 7,210.81
49UA11121512102INDIRECT - UNEMPLOYMENT INSUR 617.95618618618 2,471.80
50UA11121512105INDIRECT - MEDICAL 5,206.855,2075,2075,207 20,827.41
51UA11121512106INDIRECT - LIFE INSURANCE 357.50358358358 1,430.00
52UA11121512201DIRECT - 401K 14,787.8113,77112,58412,822 53,964.66
53UA11121512301INDIRECT - 401K 1,624.961,6731,6731,673 6,644.69
54UC11123502104OTH NON-OH EXP - TAXES 750.00750750750 3,000.00
Data
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
It looks like a simple copy of data ... maybe. I know you want the value from D5 copied as a column value into the target area, column B. So columns B:H of source area copy to columns C:I of the target area. Where does column A of the target area come from?
 

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
It is just a copy function, it's just that I don't want to have to copy 100 budgets one at a time and I knew a macro would do it. column A is just the department number which shows in d:5. So it isn't important for what I am trying to do. i thought it would be easier to pull from d5, although each budget will have the department number in it column A

Thanks
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Account and description appear to be going into cols C and D of your target sheet. You say the department number is going into column A ( from D5 ). In that case what goes into column B of your target sheet?
 

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182

ADVERTISEMENT

Sorry for the confusion, column A hold the department number in the budget worksheet which is the same department number as column B in the data worksheet. d5 doesn't have to be used. I just thought it would be easier. Maybe not, as and alternative, column A in the budget sheet does store the dept number as well.

Thanks
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Here's a guess, that would be a good starting point for you:
Code:
Sub multicopy()
    ' get first file
    fname = Dir("g:\accounting\private\msdacct\planning\2011\budgets\budgets reviewed\*.xl*")
    Do Until fname = ""
        Workbooks.Open (fname)
        lastrow = ActiveWorkbook.Sheets(1).Range("B65536").End(xlUp).Row
        targetrow = ThisWorkbook.Sheets(1).Range("B65536").End(xlUp).Offset(1).Row
        ActiveWorkbook.Sheets(1).Range("B9:H" & lastrow).Copy _
            Destination:=ThisWorkbook.Sheets(1).Range("B" & targetrow)
        ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(targetrow - 9 + 1).Value = _
            ActiveWorkbook.Sheets(1).Range("D5").Value
        ActiveWorkbook.Close savechanges:=False
        fname = Dir
    Loop
        
End Sub
 

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182

ADVERTISEMENT

Thanks, just one quick note, the summary sheet will rest in another directory labeled g:\accounting\private\msdacct\planning\2012\budget entry. Sorry for that oversight.

Also I will not have the opportunity to test this for a bit, so if you don't hear from me, please do not think i am ungrateful.

thanks again.
 

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
Great start!!

I am getting a run time error 1004:application defined or object defined error on this line. Also the budget info is on a tab labeled budget. There are other tabs in the budget worksheets. And it is only picking up the first budget in the directory, maybe the error has something to do with that.

Sub multicopy()
' get first file
fname = Dir("g:\accounting\private\msdacct\planning\2011\plan\budgets\budgets reviewed\*.xl*")
Do Until fname = ""
Workbooks.Open (fname)
lastrow = ActiveWorkbook.Sheets(1).Range("B65536").End(xlUp).Row
targetrow = ThisWorkbook.Sheets(1).Range("B65536").End(xlUp).Offset(1).Row
ActiveWorkbook.Sheets(1).Range("B9:H" & lastrow).Copy _
Destination:=ThisWorkbook.Sheets(1).Range("B" & targetrow)
ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(targetrow - 9 + 1).Value = _
ActiveWorkbook.Sheets(1).Range("D5").Value
ActiveWorkbook.Close savechanges:=False
fname = Dir
Loop

End Sub

Thanks again
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
When the error occurs, can you go into Debug mode and hover the mouse over the targetrow variable? What does the tooltip say when you do that?

... EDIT: change targetrow-9+1 to lastrow-9+1. ( sorry this was edited twice )

As for selecting a particular sheet when the budget workbooks open, maybe a line of:
Sheets("Budget").Select
is needed after the Workbook Open statement.
 
Last edited:

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
Even though I changed the code to pick only the budget tab, it still looks at the instructions tab and doesn't seem to be picking up the $ columns.


Here is the result

Sheet2


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Tahoma,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 129px"><COL style="WIDTH: 323px"><COL style="WIDTH: 80px"><COL style="WIDTH: 81px"><COL style="WIDTH: 141px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD></TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41105</TD><TD>APPS MIDWEST - IL</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>Based upon the information, enter 2011 budget amounts for each account each quarter. Formulas may be used if desired.</TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41106</TD><TD>APPS SOUTH - NC</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD></TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41107</TD><TD>APPS SOUTH - TN</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>If there are additional accounts needed for the department, they can be entered in rows 112-121 using the drop down list.</TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41108</TD><TD>APPS SOUTH - TX</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD style="FONT-FAMILY: ">If "duplicate" appears in the YTD column after selecting an additional account, it means the account is already present; reselect "ADD NEW ACCOUNT".</TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41109</TD><TD>APPS WEST - WA</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>The "LX Account Listing" tab may be used to find the desired account.</TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">41111</TD><TD>APPS WEST - CA</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD></TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">44301</TD><TD>APPLICATIONS - ARMS</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>Enter quarterly headcount in rows 134-135. This will populate the payroll accounts listed in rows 122-131.</TD><TD>A CHIASSON</TD><TD style="TEXT-ALIGN: right">52201</TD><TD>SALES AFTERMARKET</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD></TD><TD>A STEIGER</TD><TD style="TEXT-ALIGN: right">28106</TD><TD>FACILITY - NC</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>Note for production departments only: Enter information in yellow in the "Production Worksheet" tab.</TD><TD>A STEIGER</TD><TD style="TEXT-ALIGN: right">51101</TD><TD>SALES EAST - RI</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>This tab calculates the "For Budget Entry" section, which is to be entered in the "Budget" tab.</TD><TD>A STEIGER</TD><TD style="TEXT-ALIGN: right">51106</TD><TD>SALES SOUTH - NC</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD></TD><TD>A STEIGER</TD><TD style="TEXT-ALIGN: right">55101</TD><TD>SALES - LEICA - RI</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left; FONT-FAMILY: Arial Unicode MS">A CHIASSON</TD><TD>Enter the applicable quarterly allocation percentages for the department in rows 138-146.</TD><TD>A STEIGER</TD><TD style="TEXT-ALIGN: right">55301</TD><TD>SALES - ARMS - RI</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

here is the code
Sub multicopy()
' get first file
fname = Dir("g:\accounting\private\msdacct\planning\2011\plan\budgets\budgets reviewed\*.xl*")
Do Until fname = ""
Workbooks.Open (fname)
Sheets("Budget").Select
lastrow = ActiveWorkbook.Sheets(1).Range("B65536").End(xlUp).Row
targetrow = ThisWorkbook.Sheets(1).Range("B65536").End(xlUp).Offset(1).Row
ActiveWorkbook.Sheets(1).Range("B9:H" & lastrow).Copy _
Destination:=ThisWorkbook.Sheets(1).Range("B" & targetrow)
ThisWorkbook.Sheets(1).Range("A" & targetrow).Resize(lastrow - 9 + 1).Value = _
ActiveWorkbook.Sheets(1).Range("D5").Value
ActiveWorkbook.Close savechanges:=False
fname = Dir
Loop

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,653
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top