Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Transpose from one worksheet to another

Posted on 2014-08-22
3
Medium Priority
?
1,610 Views
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.



Thanks,
Andy
form.jpg
Copy-Sheet.jpg
0
Comment
Question by:akatz66
  • 2
3 Comments
 
LVL 36

Accepted Solution

by:
Norie earned 2000 total points
ID: 40279100
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")
        .Unprotect
        Set rngDst = .Range("A" & Rows.Count).End(xlUp).Offset(1)
        rngSrc.Copy
        rngDst.PasteSpecial xlPasteValues, Transpose:=True
        .Protect
    End With

    wbDst.Close SaveChanges:=True

    Application.ScreenUpdating = True

Open in new window

0
 

Author Comment

by:akatz66
ID: 40279588
worked perfectly! Thanks so much, I love the experts and love this site!
0
 

Author Closing Comment

by:akatz66
ID: 40279592
thanks so much, you all are the best
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

571 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