CopyIntoFirstEmptyRow

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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,
 
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
 
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,
 
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
 
Back
Top