Move Data from Multiple Cells to New Workbook

[TrainerMan]

Hello all. Just FYI, I'm not really experienced with VBA but know a few of the basics. First of all, here's my original data:

Excel 2010

	B	C	D	E	F	G
60010	SALARY AND WAGES
60020	EMPLOYEE BENEFITS
60101	DUES AND MEMBERSHIPS
60102	WORKSHOPS/TRAINING
60199	MISC STAFF DEVELOPMENT
60201	RENT
60202	UTILITIES
60230	TELEPHONE
60232	CELL PHONES/PAGERS
60233	INTERNET/DATA
60240	FACILITY CLEANING
60250	RENOVATIONS
60261	REPAIRS/MAINTENANCE
60262	BLDG MAINTENANCE CONTRACTS
60299	MISC FACILITY EXPENSE

<COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: center"]DESCRIPTION[/TD]
[TD="align: center"]1009[/TD]
[TD="align: center"]1101[/TD]
[TD="align: center"]1201[/TD]
[TD="align: center"]1202[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]339,845[/TD]
[TD="align: right"]493,334[/TD]
[TD="align: right"]218,351[/TD]
[TD="align: right"]100,176[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]86,276[/TD]
[TD="align: right"]124,250[/TD]
[TD="align: right"]77,608[/TD]
[TD="align: right"]29,495[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]2,475[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]15,177[/TD]
[TD="align: right"]22,028[/TD]
[TD="align: right"]10,574[/TD]
[TD="align: right"]3,483[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2,636[/TD]
[TD="align: right"]3,827[/TD]
[TD="align: right"]1,837[/TD]
[TD="align: right"]545[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]967[/TD]
[TD="align: right"]1,403[/TD]
[TD="align: right"]674[/TD]
[TD="align: right"]197[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]20,652[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1,100[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]1,335[/TD]
[TD="align: right"]1,936[/TD]
[TD="align: right"]929[/TD]
[TD="align: right"]219[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]561[/TD]
[TD="align: right"]812[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]44[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]233[/TD]
[TD="align: right"]338[/TD]
[TD="align: right"]162[/TD]
[TD="align: right"]58[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]801[/TD]
[TD="align: right"]1,163[/TD]
[TD="align: right"]559[/TD]
[TD="align: right"]122[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]36[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]11[/TD]

</TBODY>

Sheet1​

There are several things i need to do with this data. First, I need to create a 13 digit number in this format: 0000-00000-0000. The first four digits would come from cell D5 (and E5 the next time, etc)... the next five digits would come from cell B6 (and B7 the next time, etc)... the last four digits will always be 0000. I know I can use the following to accomplish that: =D5&"-"&B6&"-"&0000 but I can never get the four zeros to stay... it always reduces it to one zero. When I change the cell formatting, that just makes the cell display the formula instead of the result.

I'll try to be succinct, but I have several other things that need to happen. Let me show you what the desired output:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Account	Description	Beginning Balance - 2013	Period 1 - 2013	Period 2 - 2013	Period 3 - 2013	Period 4 - 2013	Period 5 - 2013	Period 6 - 2013	Period 7 - 2013	Period 8 - 2013	Period 9 - 2013	Period 10 - 2013	Period 11 - 2013	Period 12 - 2013	Total
1009-60101-0000	DUES AND MEMBERSHIPS

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.37[/TD]
[TD="align: right"]100[/TD]

</TBODY>

SAMPLE_BUDGET​

As you will notice, I need that 13 digit number in column A. Then column B needs to contain data from column C in the original worksheet. Then the number in D6 on the original worksheet is the total budgeted for one year, so that needs to go in column P on the new worksheet and then split 12 ways in columns D - O. Unfortunately, it doesn't usually split evenly to the cent, so the 12th month will probably need to be entered manually (unless there's a way to make this happen automatically as well).

So that's one row done on the new worksheet. Then that process needs to be repeated with data from E5, B6, C6, E6... then on down the line. I'm just looking for a way to have this happen automatically, or at least more quickly than manually entering everything. Sorry, I feel like I'm struggling to explain this well, so please ask if you have any questions. I'd be forever grateful if you could help at all! Thanks!

TrainerMan

 

[Momentman]

Maybe if you can at least put 3 or 4 rows of data (the expected output)

Cos am a little ocnfused here - (D5-b6,E5-b7,f5-b8) or how does that go? and which column appears in the Total column, i sit always from column D on the old spreadsheet??

 

[TrainerMan]

I knew I wasn't explaining things in the most clear of ways. Sorry! So the number generated in cell A4 of SAMPLE_BUDGET comes from cells D5 and B8 of Sheet1 with 4 zeros added on. Then the text in B4 of SAMPLE_BUDGET comes from cell C8 of Sheet1. Then cell P4 of SAMPLE_BUDGET comes from cell D8 of Sheet1. And the last thing that needs to happen is that value in P4 needs to be split 12 ways in cells D4 - O4. It's a dollar amount, so it needs to come out evenly to the cent... that's why cell O4 may have to be manually input because if I just put in the formula =$P$4/12 in cells D4 - O4, the total comes out to 99.96, but it needs to be 100.

Does that explanation help at all? Here's more data for the expected output:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Account	Description	Beginning Balance - 2013	Period 1 - 2013	Period 2 - 2013	Period 3 - 2013	Period 4 - 2013	Period 5 - 2013	Period 6 - 2013	Period 7 - 2013	Period 8 - 2013	Period 9 - 2013	Period 10 - 2013	Period 11 - 2013	Period 12 - 2013	Total
1009-60101-0000	DUES AND MEMBERSHIPS
1009-60102-0000	WORKSHOPS/TRAINING
1009-60199-0000	MISC STAFF DEVELOPMENT
1009-60201-0000	RENT
1009-60202-0000	UTILITIES

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.33[/TD]
[TD="align: right"]8.37[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.67[/TD]
[TD="align: right"]416.63[/TD]
[TD="align: right"]5000.00[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]206.25[/TD]
[TD="align: right"]2475.00[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]1264.75[/TD]
[TD="align: right"]15177.00[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0.00[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.67[/TD]
[TD="align: right"]219.63[/TD]
[TD="align: right"]2636.00[/TD]

</TBODY>

SAMPLE_BUDGET​

 

[TrainerMan]

One other thing to mention is that once I'm done with 1009 and numbers from column B, I have to then do 1101 and numbers from column B... and so on. Does that make sense?

 

[Fandi]

try this :
=D1&"-"&B4&"-"&"0000"

 

[TrainerMan]

try this :
=D1&"-"&B4&"-"&"0000"

That worked... thanks!! So now I'm 1/100th of the way there!

 

[Momentman]

Lets see if we can get 90/100th of the way.

Give this macro a try:

Sub rearrangedata()
    Dim I As Integer
    Dim j As Integer
    lastrow = Range("C1").End(xlDown).Row
    lastcol = Range("A1").End(xlToRight).Column
    lastrow2 = 2
    Set wksold = ActiveWorkbook.ActiveSheet
    Set wks = Worksheets.Add()
    wks.Name = "Newwks"
    wks.Range("A1:P1").Value = Array("Account", "Description", "Beginning Balanace - 2013", "period 1 - 2013" _
    , "Period 2 - 2013", "Period 3 - 2013", "Period 4 - 2013", "Period 5 - 2013", "Period 6 - 2013", "Period 7 - 2013", _
    "Period 8 - 2013", "Period 9 - 2013", "Period 10 - 2013", "Period 11 - 2013", "Period 12 - 2013", "Total")
    
    For I = 2 To lastrow
        For j = 4 To lastcol
            With wks
                .Cells(lastrow2, 1).Value = wksold.Cells(1, j).Value & "-" & wksold.Cells(I, 2).Value & "-" & "00000"
                .Cells(lastrow2, "B").Value = wksold.Cells(I, 3).Value
                .Cells(lastrow2, "C").Value = 0
                
                With .Cells(lastrow2, "P")
                .Value = wksold.Cells(I, j).Value
                .NumberFormat = "#,##0.00"
                End With
                
                With .Range(Cells(lastrow2, "D"), Cells(lastrow2, "O"))
                .Value = wksold.Cells(I, j).Value / 12
                .NumberFormat = "#,##0.00"
                End With
                
            End With
            lastrow2 = lastrow2 + 1
        Next j
    Next I
    Columns("A:P").EntireColumn.AutoFit
End Sub