This probablt due to the "wrap round" that occurs in NG. The lines marked
should be one line not two. The "_" at the end of the second line is a
continuation symbol so the third line should be OK.

ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, transpose:=True


"Savage" wrote:

I get an error when I run the code, Compile error syntax error. It
highlights ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll,

Kevin

"Toppers" wrote:

This is VBA code which has to be placed in a module.

In your EXCel w/book, do ALT+F11 which bring up the VB editor (VBE).

In the Project "box" on the LH side, you should see an icon with "VBA
Project ( <bookname) where <bookname is the name of your spreadshhet. Right
click==Insert=Module and copy the code intpo the module.

To run the code, place the cursor in the code then click the grren arrowhead
in (normally) second toolbar.

HTH

"Savage" wrote:

I am not sure what you mean?

"Toppers" wrote:

Hi,

Try..

Sub transpose()
Dim lastrow As Long, r As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
r = 2 '<== assumes data starts in row 2 ....change
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
.Cells(r, "a").Resize(4, 1).Copy
ws2.Cells(1, "A").Resize(1, 4).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, transpose:=True
Do
.Cells(r, "B").Resize(4, 1).Copy
ws2.Cells(Rows.Count, "A").End(xlUp)(2).Resize(1, 4).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, transpose:=True
r = r + 5
Loop Until r lastrow
End With
End Sub

HTH

"Savage" wrote:

sorry, above formatting got messed up

company Category URL Listing
3Com Comm. Product N/A Electrical
3M Tape, Slice kits www.3m.com Electrical

"Savage" wrote:

What I have is

Company 3COM
Category Communication Products
URL URL: N/A
Listing Electrical

Company 3M
Category Tape, Splice Kits
URL http://www.3m.com/
Listing Electrical

Company ADVANCE
Category Sign Ballast
URL http://www.advanceelectric.com/
Listing Electrical

What I need to get is
Company Category URL
Listing
Advance Sign Ballast www.advanceelectric.com
Electrical
3M Tape, Slices www.3m.com
Electrical
3com Communication Products N/A
Electrical

Thank you for any help,

Kevin