I hope someone can help.
I need to write a macro to do the following:
I have worksheet A, which I want to copy the values of Cells B1:B20
The macro will search worksheet B for the first empty row starting with
row A2.
Then copy worksheet A, cells b1:b20 into worksheet B HORIZONTALLY in cells
A2:T
The next times the macro is activated, it will again copy cells b1:b20 from worksheet a
find the next empty row in worksheet B and then copy.
thanks
Mike

Mike, the following code is untested

private sub Mikes_copy()
Sheets("A").activate
Range("b1:b20").select
selection.copy
Sheets("B").activate
Range("A2").select
do until activecell.value="" then
activecell.offset(1,0).select
loop
activecell.pastespecial transpose:=true
end sub

The logic here is :
Activate Sheet A
Select the range
Copy the range
Activate Sheet B
Goto A2
Find a blank cell
use paste special to transpose the range

Steve


-----Original Message-----
I hope someone can help.
I need to write a macro to do the following:
I have worksheet A, which I want to copy the values of
Cells B1:B20
The macro will search worksheet B for the first empty
row starting with
row A2.
Then copy worksheet A, cells b1:b20 into worksheet B
HORIZONTALLY in cells
A2:T2
The next times the macro is activated, it will again
copy cells b1:b20 from worksheet a,
find the next empty row in worksheet B and then copy.
thanks
Mike


.

Watch the word wrap in this code




sub aaa

Sheets("a").Range("b1:b20").Copy

Sheets("b").Cells(Sheets("b").Cells(Rows.Count, "a") _
& .End(xlUp).Row + 1, "a"). _
& PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

end su

--
Message posted from http://www.ExcelForum.com

Thank
mudrake
I figured out word wrap and it works great

Appreciate your help

Here's one way:

Sub test3001()
Dim rng As Range
Set rng = Sheets("B").Range("A65536").End(xlUp)(2)
Sheets("A").Range("b1:b20").Copy
rng.PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Alan Beban

Mike wrote:
I hope someone can help.

I need to write a macro to do the following:
I have worksheet A, which I want to copy the values of Cells B1:B20
The macro will search worksheet B for the first empty row starting with

row A2.

Then copy worksheet A, cells b1:b20 into worksheet B HORIZONTALLY in cells

A2:T2
The next times the macro is activated, it will again copy cells b1:b20 from worksheet a,
find the next empty row in worksheet B and then copy.

thanks

Mike

This is a short and easy way of doing it...

With Worksheets("B")
k=2
While .cells(k,1)<""
k=k+1
Wend
End With

Variable k will have the first empty row number every time
this macro is run.

Regards


-----Original Message-----
I hope someone can help.
I need to write a macro to do the following:
I have worksheet A, which I want to copy the values of
Cells B1:B20
The macro will search worksheet B for the first empty
row starting with
row A2.
Then copy worksheet A, cells b1:b20 into worksheet B
HORIZONTALLY in cells
A2:T2
The next times the macro is activated, it will again
copy cells b1:b20 from worksheet a,
find the next empty row in worksheet B and then copy.
thanks
Mike


.