Please help

[Guest]

Hi,

I need a macro that will copy a set of vertical data e.g. range A7:A25 from
a sheet called "PASTE TX HERE" and transpose paste it in the next free row
down (starting with row 2 on a sheet called "Leaver Master".

Thanks in advance.

JohnP

 

[PCLIVE]

I'm assuming that A1 will not be blank...possibly a header or something.
Also, by next available row, it is only looking at the first column. If the
cell in column A is blank, then this assumes the entire row to be available.

Sub TEST()

Sheets("PASTE TX HERE").Range("A7:A25").Copy
If Sheets("Leaver Master").Range("A1").End(xlDown).Row = 65536 _
Then
Sheets("Leaver Master").Range("A2").PasteSpecial Paste:=xlAll,
Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Else
Sheets("Leaver Master").Range("A1").End(xlDown).Offset(1,
0).PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
End If

End Sub


HTH,
Paul

 

[JE McGimpsey]

One way:

Public Sub CopyTransposePaste()
Worksheets("PASTE TX HERE").Range("A7:A25").Copy
With Worksheets("Leaver Master")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial _
Paste:=xlAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
End With
End Sub