VBA Transpose from one worksheet to another

Posted on 2014-08-22
Last Modified: 2014-08-22
Hi there,
    I am trying to create a macro to copy the contents of what an employee enters into a sheet (cells c4:c13), copy and transpose to another sheet that has the same column headings, but transposed. Both sheets are protected, so I need code to unprotect the sheets and re-protect them both after as well.  The copy sheet, productivitylog.xlsm should be opened and the data from the form should be pasted in (transposed) into the first empty row. I want the macro to be ctrl + t/

Both files will be located in:  
Form worksheet: s:\andy\form.xlsm (sheet name "Log")
Copy Worksheet to be transposed to  s:\andy\productivitylog.xlsm (sheet name "Booking Log"

The sheet that contains the form is cells c4:c13
copy sheet =  columns a-j

I also attached a screen shot of the sheet i want it to be copied to.   It should be transposed to a1:M1, but I really want it to find the first empty row and put it in there.

Question by:akatz66
    LVL 33

    Accepted Solution

    This code would go in the workbook 'form.xlsm'.

    Dim wbDst As Workbook
    Dim rngSrc As Range
    Dim rngDst As Range
        Application.ScreenUpdating = False
        Set rngSrc = Sheets("Form").Range("C4:C13")
        Set wbDst = Workbooks.Open("s:\andy\productivitylog.xlsm")
        With wbDst.Sheets("Booking Log")
            Set rngDst = .Range("A" & Rows.Count).End(xlUp).Offset(1)
            rngDst.PasteSpecial xlPasteValues, Transpose:=True
        End With
        wbDst.Close SaveChanges:=True
        Application.ScreenUpdating = True

    Open in new window


    Author Comment

    worked perfectly! Thanks so much, I love the experts and love this site!

    Author Closing Comment

    thanks so much, you all are the best

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now