Excel and VBA

[Rina]

Hi all.
I have a question here regarding MS Excel.
I have a data (see VSM1.jpg) that I loaded from MS Access to MS Excel.
Can VBA change the layout of the data in VSM1.jpg into VSM2.jpg?

Thanks in advance.

[adurga]

I dont think so.

[Ecniv]

You could.

You might want to read up on pivot table first though...

[Rina]

How? Can u show me some example?

[nagasrikanth]

Hi..

If i understand correctly...this is what the code that helps u...

Just select the cells that u want ...and say "Copy"..
ofter run the fallowing code..

Range("a1").PasteSpecial xlPasteAll, xlPasteSpecialOperationNone, , True

thanx & regards
Anu.

[zaza]

Hi,

What nagasrikanth is actually saying there is to use the Transpose method - that is the purpose of the "True" in the pastespecial parameters. Transpose swaps rows and columns, but only when pasting. So, you'd need to copy the cells and paste using this.

The alternative, if it was all to be done in a single workbook and you didn't want to get involved with cutting and pasting would be to stick it all in an array and then write it back out again. Slower for large grids, but pretty easy.

Dim myarray(3,3) as variant
 for i = 1 to 3
  for j = 1 to 3  
   myarray(i,j) = cells(i,j)
  next j
 next i
 
 for i = 1 to 3
  for j = 1 to 3  
   cells(j,i) = myarray(i,j)
  next j
 next i

You can make myarray as big as you like in rows and columns. You can't combine the loops because you're overwriting the same cells.

HTH

zaza

[nagasrikanth]

thats good 'n' gr8 code by u zaza