CopyIntoFirstEmptyRow

M

Mike

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
Click to expand...
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.
 
M

mudraker

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
 
S

steve smallman

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. row starting with
row A2. HORIZONTALLY in cells
A2:T2
The next times the macro is activated, it will again
Click to expand...
copy cells b1:b20 from worksheet a,
 
A

Alan Beban

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
 
S

Serkan

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. row starting with
row A2. HORIZONTALLY in cells
A2:T2
The next times the macro is activated, it will again
Click to expand...
copy cells b1:b20 from worksheet a,
 
A

Alan

Tried you subroutine, works well for first paste only
Will not paste onto next row when macro is executed again

Any idea
thanks for your hel
Mike
 
Top