Vba code to rearrange data

A

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
Hi All

i have this "table"

in sheet1 starts in cell A1

the mumber of rows will increase substantially as there other costs apart from Postage and courier


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 309"]
<tbody>[TR]
[TD="class: xl66, width: 309"]OTHER FIXED COSTS TABLE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"]Jan-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl66, width: 49"]Feb-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl66, width: 50"]Mar-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl66, width: 48"]Apr-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 52"]
<tbody>[TR]
[TD="class: xl66, width: 52"]May-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl66, width: 47"]Jun-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 43"]
<tbody>[TR]
[TD="class: xl66, width: 43"]Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 50"]
<tbody>[TR]
[TD="class: xl66, width: 50"]Aug-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl66, width: 49"]Sep-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl66, width: 47"]Oct-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 51"]
<tbody>[TR]
[TD="class: xl66, width: 51"]Nov-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 49"]
<tbody>[TR]
[TD="class: xl66, width: 49"]Dec-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 309"]
<tbody>[TR]
[TD="class: xl66, width: 309"]Postage and courier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 46"]
<tbody>[TR]
[TD="class: xl66, width: 46"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



i want to create a new "table" in Sheet 2 starting cell A1
following this style (in the example is only showing to February but i need all the moths obviously)


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 199"]
<tbody>[TR]
[TD="class: xl66, width: 199"]OTHER FIXED COSTS TABLE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]MONTH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]COST[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<tbody>[TR]
[TD="width: 199"]Postage and courier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Jan-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 199"]
<tbody>[TR]
[TD="width: 199"]Postage and courier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Feb-18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"] 347 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


thanks in advance

AC
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
You can use the following code.

Code: 
Sub transferXY()
Dim irow As Integer
Dim icolumn As Integer
Dim a As Worksheet
Dim b As Worksheet


Set a = Sheets("sheet1")
Set b = Sheets("sheet2")
b.Cells(1, 1) = a.Cells(1, 1)
b.Cells(1, 2) = "Month"
b.Cells(1, 3) = "Cost"
For irow = 1 To 12
    b.Cells(irow + 1, 1) = a.Cells(2, 1)
    For icolumn = 1 To 12
        b.Cells(irow + 1, icolumn + 1) = a.Cells(icolumn, irow + 1)
    Next
Next


End Sub

If you need to transfer more value, change both 12 in the two loop if you need a bigger range.

There is an Excel tool to do something like this, but I personally don't recommend it as it create some problem for me in the past.
 
Upvote 0
[TABLE="width: 984"]
<colgroup><col><col><col><col><col><col span="8"></colgroup><tbody>[TR]
[TD]hi your code doen't work well


sheet1=where data is populated:







OTHER FIXED COSTS TABLE[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[TD] 347[/TD]
[/TR]
</tbody>[/TABLE]




sheet2 has to display this:

[TABLE="width: 299"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]OTHER FIXED COSTS TABLE[/TD]
[TD]Month[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Postage and courier[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Environmental certificates[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Operating licences[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Stationery and printing[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Termination of operators[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Freight costs[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Fines and penalties[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Test and analyses[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Mystery shopper incentives[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]External warehouse[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Announcements for new operators[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Hotel costs for school enrolment new operators[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Service charge cash register[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Case of damage[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Jan-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Feb-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Mar-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Apr-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]May-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Jun-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Jul-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Aug-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Sep-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Oct-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Nov-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
[TR]
[TD]Miscellaneous[/TD]
[TD="align: right"]Dec-18[/TD]
[TD="align: right"]347[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance
 
Upvote 0
OOOO, I didn't know you had all that data in which format you wanted them.

In that case, use this
Code: 
Sub transferXY()
Dim irow As Integer
Dim icolumn As Integer
Dim a As Worksheet
Dim b As Worksheet




Set a = Sheets("sheet1")
Set b = Sheets("sheet2")
b.Cells(1, 1) = a.Cells(1, 1)
b.Cells(1, 2) = "Month"
b.Cells(1, 3) = "Cost"


For irow = 1 To 15
    For icolumn = 1 To 12
        b.Cells((irow - 1) * 12 + icolumn + 1, 1) = a.Cells(irow + 1, 1)
        b.Cells((irow - 1) * 12 + icolumn + 1, 2) = a.Cells(1, icolumn + 1)
        b.Cells((irow - 1) * 12 + icolumn + 1, 3) = a.Cells(irow + 1, icolumn + 1)
    Next
Next




End Sub
 
Upvote 0
it is working for the current data but if i increase the number of rows in sheet one doesn't work . the number of rows in sheet 1 can increase substantially....
 
Last edited:
Upvote 0
Try this.
I put the result in sheet2.

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]
Code: 
[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018562a[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] ra [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] j [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] n [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] va[B],[/B] qa[B],[/B] vb[B],[/B] vc[B],[/B] qc
ra [B]=[/B] Range[B]([/B][B][COLOR=brown]"A"[/COLOR][/B] [B]&[/B] Rows.count[B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]).[/B]row
va [B]=[/B] Range[B]([/B][B][COLOR=brown]"A2:A"[/COLOR][/B] [B]&[/B] ra[B])[/B]
[B][COLOR=Royalblue]ReDim[/COLOR][/B] qa[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]*[/B] [B][B][COLOR=crimson]12[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B]
 
vb [B]=[/B] Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]13[/COLOR][/B][/B][B]))[/B]
vc [B]=[/B] Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]2[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] Cells[B]([/B]ra[B],[/B] [B][B][COLOR=crimson]13[/COLOR][/B][/B][B]))[/B]
 
[B][COLOR=Royalblue]ReDim[/COLOR][/B] qc[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]*[/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
 
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        [B][COLOR=Royalblue]For[/COLOR][/B] j [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] [B][B][COLOR=crimson]12[/COLOR][/B][/B]
        n [B]=[/B] n [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
            qa[B]([/B]n[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
            qa[B]([/B]n[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B] [B]=[/B] vb[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] j[B])[/B]
        [B][COLOR=Royalblue]Next[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
   
     [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        [B][COLOR=Royalblue]For[/COLOR][/B] j [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]vc[B],[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B]
            k [B]=[/B] k [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
            qc[B]([/B]k[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] vc[B]([/B]i[B],[/B] j[B])[/B]
        [B][COLOR=Royalblue]Next[/COLOR][/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
 
[FONT=trebuchet ms][I][COLOR=Lightseagreen]'put the result in sheet2[/COLOR][/I][/FONT]
Sheets[B]([/B][B][COLOR=brown]"sheet2"[/COLOR][/B][B]).[/B]Activate
 
Range[B]([/B][B][COLOR=brown]"A2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]qa[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B][B])[/B] [B]=[/B] qa
Range[B]([/B][B][COLOR=brown]"C2"[/COLOR][/B][B]).[/B]Resize[B]([/B]UBound[B]([/B]qc[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B]),[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] qc
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]
 
Upvote 0
How does it know that is in sheet1? i don;t want the macro to run in other sheets it is a big file..thanks in advance
 
Upvote 0
When you run the macro above sheet1 must be the activesheet OR you can add the blue line here:

Code: 
Dim va, qa, vb, vc, qc
 
[COLOR=#0000ff]Sheets("sheet1").Activate[/COLOR]
ra = Range("A" & Rows.count).End(xlUp).row
va = Range("A2:A" & ra)
 
Upvote 0
it works very well thanks! the only is if add an extra cost in sheet 1 , i run the macro and appears which is what has to do

however if i delete that cost from sheet 1 afterwards and run the macro again , the cost remains in sheet2

it should delete if possible. thanks in advance
 
Upvote 0
ANTONIO1981 said:
it works very well thanks! the only is if add an extra cost in sheet 1 , i run the macro and appears which is what has to do

however if i delete that cost from sheet 1 afterwards and run the macro again , the cost remains in sheet2

it should delete if possible. thanks in advance
Click to expand...

Do you mean you want to clear previous data in sheet2 every time you run the macro?
Add the blue line here:

Code: 
Sheets("sheet2").Activate
[COLOR=#0000ff]Cells.Clear[/COLOR]
Range("A2").Resize(UBound(qa, 1), 2) = qa
Range("C2").Resize(UBound(qc, 1), 1) = qc
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
652
Peter_SSs
Peter_SSs
F
  • Solved
Allocation of cost to value of savings.
Replies
4
Views
305
fayo2009
F
nburaq
  • Solved
Conditional Formatting last n rows as data entered
Replies
6
Views
349
Fluff
Fluff
B
  • Question Question
calculate monthly value
Replies
0
Views
239
BrutalLogiC
B
ricafonyat
  • Solved
Total Revenue according certain periods
Replies
4
Views
149
Phuoc
P

Forum statistics

Threads
1,223,984
Messages
6,175,786
Members
452,670
Latest member
nogarth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top