VBA> Copy data from open WB to a closed one + transpose

[williamsacc]

Hello everyone,

I am just starting to learn VBA code so please be patient with me. There is a form that my colleagues need to fill out and submit to a master workbook. I would like their answers to be pasted into the master file transposed. Basically, I need a code that will copy a specified range of answers in an open workbook and paste into the next available line in a closed workbook transposed (also saving and closing that workbook) when they hit a submission button. Right now, I have a code that will copy a range and paste into a closed work book however, it is not transposing. I have tried several things at the end of my pasting line and I keep erroring out.

Can someone help me with the code to transpose? Also, any instruction on how to make this process run by clicking a button would be great.

Thank you!!

Sub Submittodatabase()

Range("B7:B15").Copy
Dim xl0 As New Excel.Application
Dim xlw As New Excel.Workbook
Set xlw = xl0.Workbooks.Open("C:\Users\sabuali\Documents\Contract Review\Database.xlsx")
xl0.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
xlw.Save
xlw.Close
End Sub

 

[Yongle]

Try

xlw.Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlvalues, Transpose:=True

 

[Yongle]

Also, any instruction on how to make this process run by clicking a button would be great

To add a button to run the macro
On Developer tab \ Insert \ select button at top
Assign macro popup appears automatically
Select Submittodatabase
Click OK