Transpose table data to another sheet

[pushyamiv]

Below is the code which i used. The only problem with this code is, only the 1st column rows data is getting transposed to next sheet. I want the complete table data need to be transposed to another sheet. Like, if we have 20 tables data in a sheet all the tables data need to be transposed in the next sheet.

Code: Select all

Sub Transpose()
Dim a As Long
'Dim b As Long
Dim C As Long
Dim i As Long
a = Sheets("Sheet1").UsedRange.Rows.Count
C = 1
For i = 1 To a Step 14
Sheets("Sheet1").Range("a" & i & ":a" & i + 13).Copy Sheets("Sheet2").Range("a" & C).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True C = C + 1 Next i End Sub

In the above code 14 is the row count in my excel sheet.

Please help on the code.

[Don]

Hi and welcome to the forum!

Please use code tags around the vba code. (updated for you)

As far as the macro goes, I'm confused as to exactly what you want to do. Are you trying to transpose multiple columns, i.e. column A, column B, etc., or multiple rows, i.e. row 1, row 2, etc.? Because, in this part of your macro Sheets("Sheet1").Range("a" & i & ":a" & i + 13).Copy you have hard-coded in to only copy data from column A. If you wanted to copy rows, you could change this line to something like this Sheets("Sheet1").Range("a" & i & ":f" & i).Copy which would give you this macro:

Code: Select all

Sub Transpose()
Dim a As Long
'Dim b As Long
Dim C As Long
Dim i As Long

a = Sheets("Sheet1").UsedRange.Rows.Count
C = 1

For i = 1 To a Step 14
    Sheets("Sheet1").Range("a" & i & ":f" & i).Copy
    Sheets("Sheet2").Range("a" & C).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
   
    C = C + 1
Next i

End Sub


Note that I updated the indenting in the macro, which makes it much easier to read. Also, I didn't test this yet, just updated it to use row references in that one line.

hope this helps!

[joe_pilgrim]

Hello!

I have a scenario much the same as the original post and am hoping someone knowledgeable in VBA can aid me with my task and save me hours of copy and pasting. I currently have a report that needs to be transposed unto a new worksheet ("Summary"). The data begins on row 7 with its proper headings. I will attempt to outline or picture:
A7 = Employee Last Name, First Name
B7 = Project Site: Employee Last Name
C7 = Labor Hours
F7 = Type of Hours ("Reg1", "Reg2", "Reg3", "Reg4" or "Reg5")

A8 = Last Name, First Name of employee
everything on row 8 after column A is empty with no data
B9 = Project Name:Last Name of the project assigned
C9 = Labor Hours eg 8
F9 = "Reg1" (identifies the type of hours)
A10 = "Subtotal" (the label)
C10 = Labor Hours eg 40 (sum of all hours)
Then after every "Subtotal" label, a new block of data will start. I'd like to transpose all this data into rows on the "Summary" worksheet and define my own headings on A2, so A3 is the location of where to start transposing. My defined headings are as follows:
A2 = Employee
B2 = Project
C2 = Reg1
D2 = Reg2
E2 = Reg3
F2 = Reg4
G2 = Reg5

During the transpose, match Reg1 through Reg5 accordingly if labeled in the report. Additionally, remove any data after the colon from column B where the report labels the Project Site with the Employee's Last Name. I hope I have provided a picture of what I am facing with everyday and am hoping someone can me cut some time so I can focus on more important matters. Should you need any additional information, please do not hesitate contacting me. Thanks in advance!


JP

[joe_pilgrim]

Sisyphus - Apologies for the confusion and thank you for your interest as I am unable to PM you as I am a new user. Also, the "Summary" sheet is already created and should not be created in the process. When transposing the "Report" unto the "Summary", I am currently copying data from the "Report" unto its respective column on the "Summary" tab:
A2 = Employee
B2 = Project (without the data after the colon)
C2 = Reg1
D2 = Reg2
E2 = Reg3
F2 = Reg4
G2 = Reg5

Again, apologies for not creating a new topic. Please advise.

JP

[Sisyphus]

Hi,
Can you post the two sheets you have, Report and Summary, with some sample data in them. If you like, post it under a new title. You can access your own posts by pressing the Edit button. Copy what you wrote before and post it together with the worksheet. I'll look at it.
Have a pleasant day!