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
5		DESCRIPTION	1009	1101	1201	1202
6	60010	SALARY AND WAGES	339,845	493,334	218,351	100,176
7	60020	EMPLOYEE BENEFITS	86,276	124,250	77,608	29,495
8	60101	DUES AND MEMBERSHIPS	100	0	0	0
9	60102	WORKSHOPS/TRAINING	5,000	500	200	400
10	60199	MISC STAFF DEVELOPMENT	2,475	50	50	0
11	60201	RENT	15,177	22,028	10,574	3,483
12	60202	UTILITIES	2,636	3,827	1,837	545
13	60230	TELEPHONE	967	1,403	674	197
14	60232	CELL PHONES/PAGERS	20,652	0	0	1,100
15	60233	INTERNET/DATA	1,335	1,936	929	219
16	60240	FACILITY CLEANING	561	812	390	44
17	60250	RENOVATIONS	0	0	0	0
18	60261	REPAIRS/MAINTENANCE	233	338	162	58
19	60262	BLDG MAINTENANCE CONTRACTS	801	1,163	559	122
20	60299	MISC FACILITY EXPENSE	36	53	25	11

<COLGROUP><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</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
3	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
4	1009-60101-0000	DUES AND MEMBERSHIPS	0.00	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.37	100

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</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
3	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
4	1009-60101-0000	DUES AND MEMBERSHIPS	0.00	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.33	8.37	100
5	1009-60102-0000	WORKSHOPS/TRAINING	0.00	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.67	416.63	5000.00
6	1009-60199-0000	MISC STAFF DEVELOPMENT	0.00	206.25	206.25	206.25	206.25	206.25	206.25	206.25	206.25	206.25	206.25	206.25	206.25	2475.00
7	1009-60201-0000	RENT	0.00	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	1264.75	15177.00
8	1009-60202-0000	UTILITIES	0.00	219.67	219.67	219.67	219.67	219.67	219.67	219.67	219.67	219.67	219.67	219.67	219.63	2636.00

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</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