Column names and multi-cells to single cell (1 Viewer)

Multi-cells to single cell

Hi,

I'm working on an excel file in which I apply a VBA to copy multiple cells (LastName, FirstName) & Paste them into a cell (Name) of a new worksheet.

However, I have "Run-time error '438'; Object doesn't support this property or method" for the following scripts


Sheets("Assign").Ranges(i, "D:E").Copy ' "D" for Lastname, "E" for FirstName

I don't know what approach in the following scripts I can work out multi-cell values into a single cell value.

Welcome any suggestions

Thanks in advance.


The following is the scripts

Sub RowToCol()

Dim i As Long, dstRW As Long, lastrow1 As Long
Dim mySelect As Integer

lastrow1 = Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row

Sheets("STutor").Cells.Clear

For i = 5 To lastrow1

'Column AU
mySelect = Sheets("Assign").Cells(i, "AU").Value 'the cell contains the selection
If mySelect = 1 Then
Sheets("STutor").Activate
dstRW = Sheets("STutor").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Assign").Activate
Sheets("Assign").Cells(i, "B").Copy ' the cell contain TRef
Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "A").Select ' the cell in which TRef value is placed
ActiveSheet.Paste

Sheets("Assign").Activate
Sheets("Assign").Ranges(i, "D:E").Copy ' the cells contain Last Name and First Name
Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "B").Select ' the cell in which Full Name value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "C").Value = 1 ' the cell in which Session 1 value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "D").Value = 3 ' the cell in which Rotation 3 value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "E").Value = 2017 ' the cell in which year 2017 value is placed
ActiveSheet.Paste

End If


'Column AV
mySelect = Sheets("Assign").Cells(i, "AV").Value 'the cell contains the selection
If mySelect = 1 Then
Sheets("STutor").Activate
dstRW = Sheets("STutor").Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Assign").Activate
Sheets("Assign").Cells(i, "B").Copy ' the cell contain TRef
Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "A").Select ' the cell in which TRef value is placed
ActiveSheet.Paste

Sheets("Assign").Activate
Sheets("Assign").Ranges(i, "D:E").Copy ' the cells contain Last Name and First Name
Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "B").Select ' the cell in which Full Name value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "C").Value = 3 ' the cell in which Session 3 value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "D").Value = 3 ' the cell in which Rotation 3 value is placed
ActiveSheet.Paste

Sheets("STutor").Activate
Sheets("STutor").Cells(dstRW, "E").Value = 2017 ' the cell in which year 2017 value is placed
ActiveSheet.Paste

End If

Application.CutCopyMode = False

Next i

Sheets("Assign").Activate
Sheets("Assign").Range("A1").Select
End Sub

Does anybody have suggestons?

Excel has a very useful macro recorder feature.
Run that whilst you do exactly what you want your vba code to do.
When done, examine the macro code, modify as necessary and you're done

There are times I wish Access had the same feature

Rather than use copy & paste (which can be problematic if the code takes long enough to run for you to do something else on your computer) why not just use formula?

eg

Sheets("STutor").Cells(dstRW, "B") = Sheets("Assign").Cells(i, "D") & " " & Sheets("Assign").Cells(i, "E")