Transpose columns to row and offset

[murthy_v_s]

Hello all,

I am hoping to get some help from the members. I have data in Sheet1 C4:C34 - when a user hits a button -Increment A2 (starts with 1) and transpose Sheet1 C4:C34 to Sheet2 B2:AF2.

Thanks a lot!

 

[jbeaucaire]

Something like:

Sub TransposeIt()

With Sheets("Sheet1")
    .[A2].Value = .[A2].Value + 1
    .Range("C4:C34").Copy
    Sheets("Sheet2").Range("B2").PasteSpecial xlPasteAll, Transpose:=True
End With

End Sub

 

[murthy_v_s]

Thanks for looking jbeaucaire. Appreciate it.

I have tried something like this (with your input):

(Sheet1 -> Data input; Sheet2 -> Log)

Sub TransposeIt()

With Sheets("Data input")
.[A2].Value = .[A2].Value + 1
.Range("C4:C34").Copy
Sheets("Log").Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
End With

End Sub

My intent increment was to increment A2 in Sheet2 (Log) by 1.

Ouput in Sheet2 (Log) may look like:

1. User hits a button on Shee1 (Data input) to enter the data into log sheet.

2. The data in Sheet1 (Data input) C4:C34 is now transposed in Sheet2 (Log) B2:AF2. A2 = 1 to start with.

Row 2: 1- B2- C2- D2- E2- F2- G2- H2- I2- J2- K2- L2- M2- N2- O2- P2- Q2- R2- S2- T2- U2- V2- W2- X2- Y2- Z2- AA2- AB2- AC2- AD2- AE2- AF2

3. User hits a button again on Shee1 (Data input) to enter the data into log sheet.

4. The data in Sheet1 (Data input) C4:C34 is now transposed in Sheet2 (Log) B3:AF3. A3 = 2.

Row 3: B3- C3- D3- E3- F3- G3- H3- I3- J3- K3- L3- M3- N3- O3- P3- Q3- R3- S3- T3- U3- V3- W3- X3- Y3- Z3- AA3- AB3- AC3- AD3- AE3- AF3

etc.

Sorry I wasnot clear in my first post.

Thanks again!



Row 3:

 

[murthy_v_s]

Edited:

Thanks for looking jbeaucaire. Appreciate it.

I have tried something like this (with your input):

(Sheet1 -> Data input; Sheet2 -> Log)

Sub TransposeIt()

With Sheets("Data input")
.[A2].Value = .[A2].Value + 1
.Range("C4:C34").Copy
Sheets("Log").Range("B2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
End With

End Sub

My intent increment was to increment A2 in Sheet2 (Log) by 1.

Ouput in Sheet2 (Log) may look like:

1. User hits a button on Shee1 (Data input) to enter the data into log sheet.

2. The data in Sheet1 (Data input) C4:C34 is now transposed in Sheet2 (Log) B2:AF2. A2 = 1 to start with.

Row 2: 1- B2- C2- D2- E2- F2- G2- H2- I2- J2- K2- L2- M2- N2- O2- P2- Q2- R2- S2- T2- U2- V2- W2- X2- Y2- Z2- AA2- AB2- AC2- AD2- AE2- AF2

3. User hits a button again on Shee1 (Data input) to enter the data into log sheet.

4. The data in Sheet1 (Data input) C4:C34 is now transposed in Sheet2 (Log) B3:AF3. A3 = 2.

Row 3: 2- B3- C3- D3- E3- F3- G3- H3- I3- J3- K3- L3- M3- N3- O3- P3- Q3- R3- S3- T3- U3- V3- W3- X3- Y3- Z3- AA3- AB3- AC3- AD3- AE3- AF3

etc.

Sorry I wasnot clear in my first post.

Thanks again!



Row 3: