vba for rearranging the data

Hi All,

I want to rearranging the data in year wise for each company. My data (Sheet 1) is in this order.

Year Company A Company B

1996 Data 1 Data 1
.......
2006 Data 11 Data 2

I wan to rearrange the data (Sheet 2) in to the following order.

Company A 1996 Data 1
........
2006 Data 11

Company B 1996 Data 1
.........
2006 Data 11

Is there any possibility for vba for this soloution. If i will increase the no of companies, whether the vba will work or not.

I have attached a file for the same

Waiting for ur Suggestions,

Which sheet is Before and which sheet is After?

Hi Jb,

Sheet 1 is before and Sheet 2 is after.

Regards,

Try this, start the macro with the raw data sheet active, it will create an Output sheet for you.

Option Explicit

Sub ReArrangeYearly()
'JBeaucaire  (8/28/2009)
Dim LR As Long, LC As Long, NR As Long, i As Long
Dim ws As Worksheet

If ActiveSheet.Name = "Output" Then
    MsgBox "Please start the macro from the data sheet"
    Exit Sub
End If

Set ws = ActiveSheet
If Not Evaluate("ISREF(Output!A1)") Then
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Output"
    Range("A2") = "Company Name"
    Range("B2") = "Year"
    Range("C2") = "S.R."
    Range("A2:C2").Interior.ColorIndex = 15
    Range("A2:C2").Font.Bold = True
    Range("A2:C2").HorizontalAlignment = xlCenter
    Range("A2:C2").Borders.LineStyle = xlContinuous
    Range("A3").Select
    ActiveWindow.FreezePanes = True
    ws.Activate
Else
    Sheets("Output").Range("A3:D" & Rows.Count).ClearContents
End If

LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(2, Columns.Count).End(xlToLeft).Column

Set ws = Sheets("Output")
NR = 3

    For i = 2 To LC
        ws.Cells(NR, "A") = Cells(2, i)
        Range(Cells(3, "A"), Cells(LR, "A")).Copy
        ws.Range("B" & NR).PasteSpecial xlPasteValues
        Range(Cells(3, i), Cells(LR, i)).Copy
        ws.Cells(NR, "C").PasteSpecial xlPasteValues
        NR = NR + Range(Cells(3, "A"), Cells(LR, "A")).Cells.Count
    Next i
    
ws.Activate
Columns("B:C").AutoFit
Range("A3").Select
End Sub

Hi Jb,

Thanks a lot. I have a doubt that If I am going to increase the no of companies whether the macro will automatically takes care of that in arranging the columns or not ?

Thanking you.

Hi,

It seems the macro is not rearranging the data from the sheet 1. I want the output file should rearrange the data like sheet 2, where i have done for two companies.

Hope i am clear this time.

Regards,

Here's your own sheet back with the macro in it. I've made no changes to your sheet. Run it, it makes the OutPut sheet just fine.

here's another one:
Adjusted closing price(bvj).xls

Thanks JB,

Your Macro has saved a lot of time of mine.

with sincere regards,

Hi JB,

I am going to ask ur suggestion regarding one small problem. I want to add data on some more variable from Sheet 2. As my no of companies is not necessary equal all the time. But I want to add more variables from the sheet 2. Which formula i should use to pull the data of only those companies exists on output sheet and "Sheet 2" as well.

I have attached the file for the reference.

Thanks and Regards,
Upananda

Please start a new thread rather than tacking onto already solved ones. Include links to prior threads only when it seems it would be relevant, which it probably isn't in this case.