I would like to be able to return the row number of the active cell then assign that value a variable. It is saying “Object not Defined” for ActiveCell.Row when I attempt to run the Macro. I would like to have cells copied to another sheet based on the value of the active row.
This is the erroneous code
Sub CopyPaste()
Set CurrentRow = ActiveCell.Row
Select Case test_expression
Case CurrentRow = 2
Sheets("Drop-In").Range("A2").EntireRow.Copy Sheets("CompletedV2").Range("A2")
Case Else
MsgBox "This is a sample box"
End Select
End Sub
New contributor
Aaron Sharples is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
This will assign the active row to the CurrentRow variable.
If the CurrentRow is two it will paste that into row 2 on the second sheet.
If it’s between 3 and 5 it will paste it into the relevant row.
Not sure why you’re using the Select Case statement though.
Public Sub CopyPaste()
Dim CurrentRow As Long
CurrentRow = ActiveCell.Row
Select Case CurrentRow
Case 2
ThisWorkbook.Worksheets("Drop-In").Rows(CurrentRow).Copy Destination:=ThisWorkbook.Worksheets("CompletedV2").Rows(2)
Case 3 To 5
ThisWorkbook.Worksheets("Drop-In").Rows(CurrentRow).Copy Destination:=ThisWorkbook.Worksheets("CompletedV2").Rows(CurrentRow)
Case Else
MsgBox "This is a sample box", vbInformation + vbOKOnly
End Select
End Sub
Copy Entire Row If String in Column Matches
- Here is a simple scenario that might get you on your feet.
- To keep things relatively safe, I’ve implemented a few restrictions that unfortunately complicated the code. Best, create a copy of your workbook and try the code there. Don’t forget to close the original.
- If the value in column
Aof the active cell’s row isTest, it copies the entire (active cell’s) row to the first available cell in columnAof the destination sheet. Otherwise, it displays a message explaining why it ‘failed’. In a nutshell, if you have selected a row where the cell in columnAis equal toTest, the row will be copied. - To get an accurate answer, you need to explain what you’re trying to do in more detail e.g. as I did above. Edit your post (question) adding more information. If my code is close, you could paste it into another question and explain what it does, what it should do, and what you have tried to achieve the desired functionality.
Option Explicit
Sub CopyIfMatch()
' Define constants.
Const SRC_SHEET_NAME As String = "Drop-In"
Const DST_SHEET_NAME As String = "CompletedV2"
Const TEST_STRING As String = "Test" ' the string to match
' Source
Dim scell As Range: Set scell = ActiveCell
If scell Is Nothing Then Exit Sub ' no active cell
Dim sws As Worksheet: Set sws = scell.Worksheet
If StrComp(sws.Name, SRC_SHEET_NAME, vbTextCompare) <> 0 Then ' (A=a)
MsgBox "This only works for sheet """ & SRC_SHEET_NAME & """!", _
vbExclamation
Exit Sub
End If
Dim SourceString As String: ' the value in column 'A' of the active row
SourceString = CStr(scell.EntireRow.Columns("A").Value)
If SourceString <> TEST_STRING Then ' is equal (case-sensitive i.e. 'a<>A')
MsgBox "The Source string """ & SourceString _
& """ is different than the Test string """ & TEST_STRING & """!", _
vbExclamation
Exit Sub
End If
' Destination (different sheet but same workbook as source)
Dim dws As Worksheet: Set dws = sws.Parent.Sheets(DST_SHEET_NAME)
Dim dcell As Range: ' first 'available' cell in column 'A'
Set dcell = dws.Cells(dws.Rows.Count, "A").End(xlUp).Offset(1)
' Caution, the previous doesn't work if column 'A' in source could be empty!
' Keep in mind that you can copy entire rows only to column 'A'!
' Copy
scell.EntireRow.Copy dcell
' Inform.
MsgBox "Matching source row " & scell.Row _
& " copied to destination row " & dcell.Row & ".", vbInformation
End Sub