Solved: Help - Macro to convert column values into row values for various branches

[mogambo]

Hello dear

I am attaching an excel file which would rather explain what I want to accomplish in Excel. As I am not so good with English, the attachment will explain my requirement in more detail.

Something about the attachment:

I am attaching 1 file. The file has 4 sheets. First 3 sheets namely (B1, B2 & B3) is the data what I have. The last worksheet is what I want to accomplish. I could not think over any sort of vba code or something like it to get me started. I am doing it manually right now and spending hours of time to create it like an idiot. Only God knows how many mistakes my boss will find when checks it

Thank you very much for any help.

[GTO]

Dear Mogambo,

I thought this was a fine explanation, and really only have but one question. In your example workbook, the source data is consistent layout-wise. That is to say, there are not five products for one of the expense types, while four products for all others.

Can we depend upon this?

The reason I am asking is that this would make a substantial difference in the way we look at a code and/or formula response, as we don't need to worry about searching, finding, or otherwise making our best guess at what to "grab up" and change the layout to.

Hope that made sense,

Mark

[mogambo]

First of all, thank you again for your wonderful help, Mark. You are a great help to this forum.

Okay. Your question is correctly placed. In response to your question, I would provide a very accurate answer. My answer is:

No, the number of Products will remain at a constant for all the Expense types. In other words, for example, there will not be 12 products for one of the expense types and 15 for all the other expense types.

HOWEVER - I prefer some solution which can accomodate a change to the number of Products, AND/OR, a change to the number of Expenses but again that will be applicable to an altogether New Report. Needless to say, the branches will differ.

A little explanation in detail.

I will have to do this work everytime my Boss or the management wants a new report. So I would like to tell you that the number of Products and Expenses will remain at a constant for a particular report.

The number of Products AND/OR Expenses can increase or decrease in the future reports but they will again remain at a constant, the minute, I start creating that report.

I hope you can understand what I explained above and help me with something. Please tell me if you want to ask me something in relation to this topic. I mean, any questions then I will be happy to answer it.

Thank you, sir.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim NextRow As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet
Dim target As Worksheet

Set target = Worksheets.Add
target.Range("A11").Value = Array("Branch Code", "Expense Code", "Product Names", "Product Figures")
NextRow = 1

For Each sh In ActiveWorkbook.Worksheets

If sh.Name <> target.Name And sh.Name <> "What I Want" Then

LastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
LastCol = sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column

For j = 4 To LastCol

For i = 2 To LastRow

NextRow = NextRow + 1
sh.Cells(i, "A").Resize(, 2).Copy target.Cells(NextRow, "A")
target.Cells(NextRow, "C").Value = sh.Cells(1, j).Value
target.Cells(NextRow, "D").Value = sh.Cells(i, j).Value
Next i
NextRow = NextRow + 2
Next j
End If
Next sh

End Sub
[/vba]

[mogambo]

Yes, thanks for the code but what will this macro do ? I think you have forgotten to tell this in your reply.

[Bob Phillips]

It will do what you asked it to do, what is the point of me repeating that?

[mogambo]

I am sorry, I have asked the question incorrectly.

I mean to ask, if your code is flexible enough to work if there is a change in the number of Products or number of Expenses, as I have mentioned up and above in my earlier post ?

This change in the number of Products & Expenses can occur in the future reports. For example, assume the number of Products were increased by 2 to the total number of Products now being 6.

So my question is, what part of code will I have to modify in order to incorporate the change in number of Products or Expenses in a report ?

xld, Thank you for the code and your help. One more thing, GTO (Mark) is a very kind member, always a help. Thanks to him too.

Please stay with me in order to solve this problem of mine.

[Bob Phillips]

Try it and see.

[mogambo]

Thank you for all your help. I think I can play around with the code in order to suiting my requirements.