Using a Macro to Copy and move data! - HELP!

[Hopeless inChicago]

Hello,

This is my first time doing this, so please forgive my lack of knowledge.

I am trying to copy information from a sheet in excel to another, so I did a
record macro and performed the operations....problem is, I need this repeated
until all the data is done.

Present Layout:
Column A ColumnB ColumnC, etc
SalesOrder 1234 5678
PONumber 6678 1111
InvoiceDate 12/22/07 12/22/47
RequiredSipDate 08/12/07 01/01/2008

And I'd like it to look like this
SalesOrder PONumber InvoiceDate RequiredSipDate
1234 6678 12/22/07 08/12/07
5678 1111 12/22/47 01/01/07

and I'd like the Macro/Program to run until there is no more info in the
sales order
row of the original layout.

Help!

 

[Greg Glynn]

Try this one-step approach - Using Paste-Transpose

Give your Sales Info in the Original Data sheets a range name of
"SalesInfo".

I've used "Original Data" and "New Data" as sheet names ... you'll
need to change these to your sheet names. Then:

Sub Switcharoo()
WorkSheets("Original Data").Range("SalesInfo").Copy
Worksheets("New Data").Range("A1").PasteSpecial Paste:=xlPasteAll,
_
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End sub

Hope this helps

Greg

 

[Dave Unger]

Hi Hopeless inChicago

Here's one possible way. This macro assumes 4 headers as shown in
your example. Change the worksheet names to match your sheets.

regards,

Dave

Sub Arrnge()
Dim rng1 As Range, x As Integer, cnt As Integer
Dim wk1 As Worksheet, wk2 As Worksheet

Set wk1 = Worksheets("sourcesheet")
Set wk2 = Worksheets("targetsheet")

wk1.Activate
Set rng1 = Intersect(Rows(2), ActiveSheet.UsedRange)
x = rng1.Count
cnt = 1

With wk2
Do While rng1(cnt) <> ""
For x = 1 To 4
.Cells(cnt, x) = Cells(x, cnt).Value
Next x
cnt = cnt + 1
Loop
End With

End Sub