copy tables, then transpose and paste into other sheet.

[dinokovac93]

hi everyone, I'm not the best in marcos and I've been searching for a solution to this for a while.

So what I'm doing is copying a table in a worksheet into another worksheet and transposing it. Some worksheets that contain the original tables can have a lot of tables which is way a marco would really be helpful.

The marco would:

1. Select the tables in the worksheet A (original) and paste the data into worksheet B and transpose it

2. after step 1, it would move down to the next available blank cell in column A

3. Repeat step 1 and 2 until all tables have been completed.

I'm including a GIF of doing this process manually. In the GIF I have a keyboard shortcut to copy, transpose and paste into a new worksheet. You can see all the steps I described which I must do manually.

Is there is a way to include this process into one entire marco so that I do not have to manually have to go and click each table and select each cell, so that the marco would do this entire process in one shot?

Any help is truly appreciated. Thanks!

 

[igold]

Re: copy tables, then transpose and paste into other sheet. marco help

Hi dinokovac93,

Welcome to the MrExcel Forum.

Quick question: Are these really tables that you are copying and transposing or are they separate ranges of data?

If they are tables, do you know the names of the tables in advance or does the macro need to pick up each table through the code.

 

[dinokovac93]

Re: copy tables, then transpose and paste into other sheet. marco help

Hi dinokovac93,

Welcome to the MrExcel Forum.

Quick question: Are these really tables that you are copying and transposing or are they separate ranges of data?

If they are tables, do you know the names of the tables in advance or does the macro need to pick up each table through the code.

Hi igold,

They are separate ranges of data not tables. Sorry for the mistake.

 

[igold]

Re: copy tables, then transpose and paste into other sheet. marco help

See if this does anything for you. A couple of notes...

Your gif shows your source data starting in row 2, the code uses Row 1 as the first row of source data. Also the gif shows you pasting two empty rows lower on the second paste and then three empty rows on the third paste and then two again on the fourth paste. The code will leave only one blank row in between pastes. Please do not forget change the names of the Sheets in the code to what your source and destination sheets are. I used "Sheet1" and "Sheet2" respectively. Please test on a backup copy of your data.

Sub copytranspose()


    Application.ScreenUpdating = False
    Dim wsSrc As Worksheet: Set wsSrc = Worksheets("Sheet1")
    Dim wsDst As Worksheet: Set wsDst = Worksheets("Sheet2")
    Dim lrow As Long, lsRow As Long, nxtpstRow As Long


        lrow = wsSrc.Cells(Rows.Count, 1).End(xlUp).Row
        wsSrc.Range("A1").CurrentRegion.Copy
        wsDst.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        lsRow = wsSrc.Range("A1").CurrentRegion.Rows.Count
        nxtpstRow = wsSrc.Range("A1").CurrentRegion.Columns.Count
    Do Until lsRow >= lrow
        lsRow = wsSrc.Cells(lsRow, 1).End(xlDown).Row
        wsSrc.Cells(lsRow, 1).CurrentRegion.Copy
        wsDst.Range("A" & nxtpstRow + 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        lsRow = wsSrc.Cells(lsRow, 1).End(xlDown).Row
        nxtpstRow = 1 + nxtpstRow + wsSrc.Range("A" & lsRow).CurrentRegion.Columns.Count
    Loop
    wsDst.Select
    Range("A1").Select
    wsSrc.Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

I hope this helps

 

[dinokovac93]

Re: copy tables, then transpose and paste into other sheet. marco help

igold, thank you so much! Works perfectly.

 

[igold]

Re: copy tables, then transpose and paste into other sheet. marco help

Great, I was happy to help. Thanks for the feedback!