CODEWITHSUNDEEP
excelvba
Dozens
Dozens

Reputation: 145

Copy the row basing on value in the in the column that equals defined name

I am trying to create the macro that will copy the entire row basing on the value in column C that equals one of my defined names (it is a date) and then, it will paste the values exactly in the same place - Sort of freezing function.

Here what I have done so far, but the code does not respond at all once it is run (no errors, literally nothing). I have been researching here within similar topics and below is kind of mixture that I found matching what I would like to get:

Sub testIt()
Dim r As Long, endRow As Long, pasteRowIndex As Long

endRow = 1000 ' preferably change for a function that will retrieve the last used row number via a function

For r = 7 To endRow 'Loop through sheet1 and search for your criteria

    If Cells(r, Columns("C").Column).Value = ThisWorkbook.Names("MyDate").Value Then Rows(r).Select

    For Each cell In Selection
    cell.Value = cell.Value
    Next cell
    End If
Next r
End Sub

I will be very grateful for some hints.

Upvotes: 2

Views: 34

Answers (1)

SJR
SJR

Reputation: 23081

When I tested this, I found that the Value of a Name returns its address, not its actual value, so use the Range instead. Also, there is no need to select and loop through each cell (although I doubt you need to operate on the whole row).

Sub testIt()

Dim r As Long, endRow As Long, pasteRowIndex As Long

endRow = 1000 ' preferably change for a function that will retrieve the last used row number via a function

For r = 7 To endRow 'Loop through sheet1 and search for your criteria
    If Cells(r, 3).Value = Range("MyDate").Value Then
        Rows(r).Value = Rows(r).Value
    End If
Next r
'Alternatively you could use ThisWorkbook.Names("MyDate").Referstorange.Value
End Sub

Upvotes: 1

Related Questions