Copy Range of cell and Transpose paste Macro

[GERALDRAHMAN]

Hi All

I need a macro that will copy data in range A1:Q1, Transpose to W2, and from A13:Q13 transpose paste to X2 and A1:Q1 transpose paste to M14 and A8:Q8 transpose paste to N14. This action should be for every worksheet in the workbook as the layout is uniform.

Many Thanks

 

[Jeffrey20]

May I ask, why use a macro? Why not just make simple references?

W2: =A1
W3: =B1
W4: =C1

etc...

 

[GERALDRAHMAN]

You're Right! Dont know why I didnt think of that, thanks

 

[Jeffrey20]

You're welcome.

 

[kevatarvind]

if you want vb code then you can try below one

Sub Test()
my = ThisWorkbook.Worksheets.Count
Sheets(1).Select
Do Until ActiveSheet.Name = my
Range("A1:Q1").Copy
Range("W2").PasteSpecial xlPasteValues, Transpose:=True
Range("A13:Q13").Copy
Range("X2").PasteSpecial xlPasteValues, Transpose:=True
Range("A1:Q1").Copy
Range("M14").PasteSpecial xlPasteValues, Transpose:=True
Range("A8:Q8").Copy
Range("N14").PasteSpecial xlPasteValues, Transpose:=True
On Error GoTo GetLost
ActiveSheet.Next.Select
Loop
GetLost:
End Sub

 

[Jeffrey20]

Aren't you missing the two lines (or at least the first one)?

Sub Test()
my = ThisWorkbook.Worksheets.Count
Sheets(1).Select
Do Until ActiveSheet.Name = my
Range("A1:Q1").Copy
Range("W2").PasteSpecial xlPasteValues, Transpose:=True
Range("A13:Q13").Copy
Range("X2").PasteSpecial xlPasteValues, Transpose:=True
Range("A1:Q1").Copy
Range("M14").PasteSpecial xlPasteValues, Transpose:=True
Range("A8:Q8").Copy
Range("N14").PasteSpecial xlPasteValues, Transpose:=True
[SIZE=3][COLOR=#ff0000][B]Application.CutCopyMode = False
Range("A1").Select[/B][/COLOR][/SIZE]
On Error GoTo GetLost
ActiveSheet.Next.Select
Loop
GetLost:
End Sub

 

[kevatarvind]

that is optional if you will not use those line even macro work perfects

 

[Jeffrey20]

Well yeah, "optional" as in not needed for the code to work, but still, the code should clean after itself, shouldn't it?

(Sorry if I came out as a pain in the @ss, that wasn't intended)

 

[GERALDRAHMAN]

Thanks Kevatarvind. Fantastic Macro. You have both being very helpful Thanks again Jeffrey

 

[kevatarvind]

Your Welcome