Copy and transpose paste from one worksheet to another

[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".

I need this to run at the end of a macro I have already written so please
give me some advice on where variable declaration should go or if there is a
code that I can put at the end of my macro to initiate the macro I hope you
can provide.

Thanks in advance.

JohnP

 

[Guest]

How about this, you could hide column A on the second sheet.
What this does is inserts a new row 1 on the second sheet, copies the data
in column A on the first sheet and pastes it in the new row 1, starting in
column B.
It also adds 1 to the maximum value in colum A and pastes this value in cell
A1. It then sorts all of the data on the second sheet by column A.

Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "=MAX(R[1]C:R[47]C)+1"
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("A2:A26").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Cells.Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D14").Select