copy tables, then transpose and paste into other sheet.

dinokovac93

New Member
Joined
Oct 10, 2017
Messages
11
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!



 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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

New Member
Joined
Oct 10, 2017
Messages
11
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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.

Code:
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

New Member
Joined
Oct 10, 2017
Messages
11
Re: copy tables, then transpose and paste into other sheet. marco help

igold, thank you so much! Works perfectly.

 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
Re: copy tables, then transpose and paste into other sheet. marco help

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

Watch MrExcel Video

Forum statistics

Threads
1,099,491
Messages
5,468,926
Members
406,620
Latest member
Gitani123

This Week's Hot Topics

Top