Copy data from one tab to another.

[philb99]

Hi

I have MI recorded on one tab going left to right and I wish to show this in table formats going down on tab 2

Looking for help on how best to achieve please

Thanks

Dropbox

www.dropbox.com

 

[alansidman]

Here is the link to your file with Power Query transposing the data.

drop box query6.xlsx | Powered by Box

app.box.com

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, lRow As Long
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    With desWS
        srcWS.Range("A2").Copy .Range("A2")
        .Range("A2:B2").Merge
        .Range("A2:B2").WrapText = True
        .Rows(2).RowHeight = 30
        srcWS.Range("B1:Q1").Copy
        .Range("A3").PasteSpecial Transpose:=True
        srcWS.Range("B2:Q2").Copy
        .Range("B3").PasteSpecial Transpose:=True
        .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A" & lRow + 1) = "Total"
        .Range("B" & lRow + 1).Formula = "=sum(B3:B" & lRow & ")"
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub