Transpose Macro on a Button

[MottPott]

So I am looking to make a button that when pressed, the macro will cut the values/ words (not the formatting, as this column has drop down lists) from worksheet 1 (Data Entry), and paste them onto worksheet 2 (Locked) in rows.
I don't want it overriding the existing rows in worksheet 2, rather I want it to make a list. So each time it pastes the transposed row onto a new/empty line.

ie. Cut the values/ words from Column B or Range B2:B11. then transpose paste the values on worksheet 2 in a Row, each time to a new/ empty row.

I am struggling to get this all to work, and would like any help I can get.

On a side note, is it possible to lock the worksheet 2, or prevent people from viewing it. As it will be collating information from the worksheet 1 survey.
I know I can lock the worksheet and prevent anyone from editing it, however I'd also like to make it so no one can see/ view its contents.

 

[Colo]

Hi,
here is a sample code for you.

Sub Transpose()
    Dim rFrom As Range, rTo As Range
    'If you want to make the range variable like a selection, change this to "Selection".
    Set rFrom = Sheets("Sheet1").Range("B2: B11")
    Set rTo = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    rTo.Resize(, rFrom.Rows.Count).Value = Application.Transpose(rFrom.Value)
End Sub

For hiding worksheet2, you can set xlSheetVeryHidden to the visible property from code/VBE so that no one can make it visible/editable from Excel.

Worksheets("Sheet2").Visible = xlSheetVeryHidden

 

[MottPott]

Hi,
here is a sample code for you.

Sub Transpose()
    Dim rFrom As Range, rTo As Range
    'If you want to make the range variable like a selection, change this to "Selection".
    Set rFrom = Sheets("Sheet1").Range("B2: B11")
    Set rTo = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    rTo.Resize(, rFrom.Rows.Count).Value = Application.Transpose(rFrom.Value)
End Sub

For hiding worksheet2, you can set xlSheetVeryHidden to the visible property from code/VBE so that no one can make it visible/editable from Excel.

Worksheets("Sheet2").Visible = xlSheetVeryHidden

Awesome, I love it. Thank you!!!

It copies/ transposes it all, thank you.

When I tried adding a clear contents line into it, I couldn't get it to work. I want to clear the content of the column after (Just the words, not the formatting).

 

[Colo]

When I tried adding a clear contents line into it, I couldn't get it to work. I want to clear the content of the column after (Just the words, not the formatting).

Glad it helps! To clear the content of the column, just add the following line above End Sub.

rFrom.ClearContents