CopyIntoFirstEmptyRowHorizontally

  • 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:B2
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

thank
Mike
 
Sub Macro1()
' Macro1 Macro
' Macro recorded 02/18/2004 by James M. May
'
Sheets("Sheet1").Activate
Range("B1:B20").Select
Selection.Copy
Sheets("Sheet2").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A2").Select
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1").Select
Sheets("Sheet2").Activate
Range("A2").Select
End Sub
 
Mike

Try this

Sub Test()
Dim c As Variant
Dim rng1 As Range
Dim dest As Long, i As Long, r As Long
Set rng1 = Sheets("A").Range("B1:B20")

With Sheets("B")
dest = Application.WorksheetFunction.CountA(Range
("A:A")) + 1
End With
i = 1
For Each c In rng1
Cells(dest, i).Value = c
i = i + 1
Next c
End Sub


Regards
Peter
 
HiJayMa
I tried to copy your code into my macro and received a syntax error, compile error on the following segment of code
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,

any suggestions
thanks for your help
Mike


----- JMay wrote: -----

Sub Macro1()
' Macro1 Macro
' Macro recorded 02/18/2004 by James M. May
'
Sheets("Sheet1").Activate
Range("B1:B20").Select
Selection.Copy
Sheets("Sheet2").Activate
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Range("A2").Select
Application.CutCopyMode = False
Sheets("Sheet1").Activate
Range("A1").Select
Sheets("Sheet2").Activate
Range("A2").Select
End Sub
 
Have you tried the code I previously posted? Here it is again.

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