Excel 2007 : copy one sheet columns to new sheet rows

[BigGPL]

Hi all,

I need some help on this;

On Sheet1, I have many rows of data;
Row1: A1 = 1, B1= 219, C1 = Oranges, D1 = etc for a total of eight columns.
Row2: A1 = 2, B1= 545, C1 = Apples, D1 = etc

What I need is a vba script to copy Sheet1 to Sheet2, BUT copy the columns to rows, ie,;

Sheet2:
R1 A1= 1 (Sheet1 R1A1)
R2 A1= 219 (Sheet1 R1B1)
R3 A1= Oranges (Sheet1 R1C1)
R4 A1= stuff.
R5 A1 = stuff
...
R8 A1 = stuff

R9 (skip this row)

R10 A1 = 2 (Sheet1 R2A1)
R11 A1= 545(Sheet1 R2B1)
R12 A1= Apples (Sheet1 R2C1)

for a total of eight rows, THEN skip a row then copy the the next row's data from Sheet1 and so on.

I hope what I explained makes sense.(It does to me, but I've had way too much coffee today)
I would really appreciate help on this.

George

[MarvinP]

Hi George,

There is a Copy and Paste Special using Transpose, which does what I think you are discribing.

See http://www.techonthenet.com/excel/fo.../transpose.php

Does this solve your question without VBA?

[BigGPL]

Hi George,

There is a Copy and Paste Special using Transpose, which does what I think you are discribing.

See http://www.techonthenet.com/excel/fo.../transpose.php

Does this solve your question without VBA?

Hey MarvinP,

Thanks both for the reply and link.
The Copy, Paste using Transpose works fine if I had only several lines, but you have to copy, paste-transpose one row at a time. I tried copying several rows and the result after paste-transpose was the new sheet having several columns. I have many sheets, some with 50 rows, some with 75 rows or more, so doing each row individually would be very time consuming.

I really need vba code to accomplish this task.

Again, thanks for the assist anyway.

George.

[BigGPL]

Okay, I found this code somewhere on the web:

Please Login or Register  to view this content.

It will do what I need, but only the first row.
Could someone take a look and see how to modify it so after it does the first row to move to the next row and so on while the value of a cell in column B <> " ".

George

[MarvinP]

Hi George,
To even attempt this we need a sample of your data. Click on "Go Advanced" and then the Paper Clip Icon above the message area to attach a sample of what you are working with. Please include what you have and your expected outcome.

[BigGPL]

Thanks,

In the attached file, Sheet1 has the original data. On Sheet2 you will find what I hope to do.

George

[MarvinP]

Hi George,

See if this does what you need. I recorded it and put in some variables. Set a breakpoint and run it from Sheet 1. Step though the code and see if you can follow the logic. You will be able to write these with a little practice.

Please Login or Register  to view this content.

[BigGPL]

Hey MarvinP,

Thanks for the code but I get an 1004 error when I run it. I'm a relative noobie to excel and vba, so here is what I did;
I copy - pasted the code you supplied into Sheet1(Sheet1) in the vb editor. Hit the Run/Run Sub button and got the 1004.
I hit the Dubug/Step Into and F8'ed to the line: Cells(LastRowSht2, "A").Select.
When I hit the F8 again I got the 1004 error window.

Now I can follow the logic easily enough, I think, but its the verbage and syntax that I'm working on.

Question: the code is setting the variable LastRowSht2 to the last row in Sheet2, but if there is no data yet in Sheet2, how can it find the last line. Or am I reading this wrong?

George

[BigGPL]

OPPS!

When it said to run from sheet1 I literally ran it from sheet1.

I put the code into a module and ran it rom there and it works.

Told you I was a noobie.

Thanks so much for your help, MarvinP, it is truely is appreciated.

George

[MarvinP]

Hi George,
The first time it is run on a blank sheet the last row will be 1.

Here is thw whole file with macro included. I should have put it in before. Figure why this works and yours didn't. Did yoj put the code in a Module or behind Seet1?

[BigGPL]

I guess you were writing your last response just about the same time as I was writing my OPPS msg.

Yup I initially put it behind Sheet1.