howCopy data on sheet1 to sheet1 Transpose it using loop metod

[alex0405]

Hi every one, i am beginner in Vba,

I have problem resolving my VBA code. for my office task that my superior ask me to
i wont to copy data in sheet1 to sheet2 and Transpose it.
i try to Use VBA code to solve it.

this is the code that i made :

Sub macro()
Dim i As Integer
Dim x As Integer
Dim j As Integer
Dim b As Integer
Worksheets("Sheet1").Range("A1").Select
Worksheets("Sheet1").Range("A2:A33").Copy
Worksheets("Sheet2").Range("B1").PasteSpecial Transpose:=True
Worksheets("Sheet2").Range("A1") = "EMITEN"
i = 1
x = 1
j = 2
b = 4
Do While Worksheets("Sheet1").Range("Ai").Value <> ""
If Range("ai").Value <> "" Then
j = j + 1
x = x + 32
Worksheets("Sheet1").Range("A").Copy
Worksheets("sheet1").Range("B").PasteSpecial Paste:=xlValues
Worksheets("Sheet1").Range("B:D[x]").Copy
Worksheets("Sheet2").Range("A[j]").PasteSpecial Transpose:=True
Worksheets("Sheet2").Range("A[j]:A").Value = Worksheets("Sheet2").Range("A[j]")
x = x + 2
i = i + 34
j = j + 2
b = j + 2
Else: End If
Loop
End Sub

 

[AlphaFrog]

Create a range reference using something like this...

.Range("A" & i)

.Range("B" & i & ":D" & x)