VBA Transpose from one worksheet to another

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
akatz66Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akatz66Author Commented:
worked perfectly! Thanks so much, I love the experts and love this site!
0
akatz66Author Commented:
thanks so much, you all are the best
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.