Rearranging data in a spreadsheet

[Aaron Bean]

Hi, I am trying to write a macro to rearrange data in one spreadsheet into the format of another (see JPEG attached). This involves creating a row for each value in the top table so it is presented in the format in the bottom table. Please help as I haven't got a clue how to do this.

Thanks, Aaron

[Ecniv]

What code do you have?

[Aaron Bean]

I do not have any code at present as I don't really have a clue of where to start.

I was think I need to use the For loop statement and offset but am not too sure how to sue these.

Any pointers or code examples would be gratefully received,

Thanks, Aaron

[Ecniv]

Assuming that the layout will not change, but the lines of data might...

you'd need
- a variable for the title
- a variable for the section
- a variable for the end (last row of data)
- a variable for the current row on the dest sheet that you are at

You'd also need a reference to the workbook, sheet, and a variable for the range.

Pseudocode would be something like:
Read in the title, the section
Loop the following
write to the destination sheet the variables held
read in the item and write it to the dest sheet (hold in a variable)
read in the values and write them as new rows including the held variables
move output pointer as each line of data is complete
move to next line of data
Exit if no more data rows to read


Have a go first, and post up some of your code with any error messages. If you are doing this for a course, they should cover some of the different ways to do this in it.

[Aaron Bean]

Hi, this is the code I have to far, but somehow the for next isn't working. How do I get the macro to write to the new worksheet? For example, for each value I want it to write the column headings from the original sheet into rows in the new sheet?

Please help!

c = Application.WorksheetFunction.CountA(Range("A:A"))
r = Application.WorksheetFunction.CountA(Range("10:10"))

For c = 3 To 3 + c - 1
For r = 11 To 11 + r - 1
Sheets("ProfitAndLoss").Cells(Range("D2")) = Sheets("Base Plan South(2)").Cells(1, r)

Next c
Next r

End Sub

[Ecniv]

c = Application.WorksheetFunction.CountA(Range("A:A")) 
r = Application.WorksheetFunction.CountA(Range("10:10")) 

For c = 3 To 3 + c - 1 
For r = 11 To 11 + r - 1 
Sheets("ProfitAndLoss").Cells(Range("D2")) = Sheets("Base Plan South(2)").Cells(1, r)

Next c
Next r

First off it would be better to hold a reference pointer (variable) to the sheets in question. You will probably need something pointing to the current workbook too so create a variable for that too. Also you would need to set the VBA editor to declare all variabls so it can assist you in trapping mistyped names for the variables.

so something like:

'---- dim area
    dim wrk as workbook
    dim shtSrc as worksheet, shtDest as worksheet

Then you need to set them:

'---- set pointers to sheets etc
    set wrk = application.activeworkbook
    set shtsrc = wrk.sheets("Base Plan South(2)")
    set shtdest = wrk.sheets("ProfitAndLoss")

'---- other code here

Then you'd do your coding... and finally a clean up

'---- Clean up
    set shtsrc = nothing
    set shtdest = nothing
    set wrk=nothing

Ok to the code!
I do not know how your data is laid out, as in whether the next section has a gap or not before it starts. So I will guess and you will have to tweak teh code to suit.

In the dim area at the top of the sub

    dim lngMaxSrcRow as long, lngSrcRow as long
    dim lngDestRow as long

then in the coding area ('other code here') put something like:

    lngMaxSrcRow = shtsrc.cells(4,2).end(xldown).row
    lngDestRow = 2

Also sets the destination output to row number.

This goes to the fourth row and the second column (B) then moves to the end (as if you pressed End then Down) and returns the row number to the variable.

You can then loop from the starting row (4) to the returned row.

To write to the other sheet you can copy n paste or you can read from one and write to the other... something like:

    shtDest.cells(lngDestRow,3)=shtSrc.cells(lngSrcRow,3)
    
'---- move the destination pointer to the next line
    lngDestRow = lngDestRow+1

See if that gives you some ideas. Post up your code if you make it work, or if you are having problems.