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
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
Last edited: