Userform Spreadsheet - row to cols

[m0atz]

Hey all,

I've got the below code which pulls some data on the relevant row from a mini-customer database. The data is for example Range("A27:A45") but could be any row, hence the variable which looks up the customer name first.

The output I have set is rngtarget which is a spreadsheet on userform3. I've only a pretty basic knowledge of this add in and its controls, so can only 'repeat' the layout in my customers sheet, i.e. as if the data was copy and pasted from my customers sheet into A1 - R1.

What i would really want is for each cell in to be shown on my userform horizontally, i.e. in A1 - A18 - can this be done, i.e. transfer from a row in my actual spreadsheet into a column on my userform spreadsheet...?

Dim dogsname
Dim rngsource
Dim rngtarget
Dim date1, found
        dogsname = UserForm1.TextBox13.Value
 
        Set found = Columns("A").Find(what:=dogsname, lookat:=xlWhole, LookIn:=xlValues)
        If Not found Is Nothing Then found.Select
        date1 = ActiveCell.Offset(0, 27).Row
        'MsgBox date1
 
            Set rngsource = Sheets("Customers").Range((Cells(date1, 27)), (Cells(date1, 45)))
            Set rngtarget = UserForm3.Spreadsheet1.Cells.Range("A1:Z1")
 
            With UserForm3.Spreadsheet1.Sheets(1)
                rngtarget.Value = rngsource.Value 'rngtarget etc imports the table in full
                .Columns("A:Z").EntireColumn.AutoFit 'autofit resizes all cols a - z
                .Columns("A:Z").NumberFormat = "dd/mm/yyyy" 'sorts num format
            End With

 

[nirvana_]

You can use the transpose option with PasteSpecial function like given below. Hope this helps.

Sub RowsToColumns()
    Dim rn As Range
    Set rn = Worksheets(1).Range("A1:A27")
    
    'Copy from sheet 1
    rn.Copy
    
    'Paste in sheet 2.  rows changed to columns.
    Worksheets(2).Range("A1").PasteSpecial Paste:=xlPasteAll, Transpose:=True
End Sub

 

[m0atz]

Thanks for the response, I managed to sort by using a listbox in the end.! however, this is good to know moving forward.

Cheers

Colin