Replace certain cells in rows with values from same rows but in different column

[1819]

I have attached a sample worksheet which works to overwrite the values in column A if there is a value in the adjacent cell in column B.

So it copies and pastes all values in column B to the adjacent cell in column A. It ignores blanks.

The code I have used to select the cells which contain values in column B is:

Option Explicit

Sub Selector()

'5 - Overwrite cells in column C where the row has value from column M

'PURPOSE: Selects only cells with values or formulas and excludes blank values (ie ="" or +"")
'AUTHOR: Rick Rothstein (MrExcel MVP)
'SOURCE: www.TheSpreadsheetGuru.com

Dim rng As Range
Dim UnusedColumn As Range
Dim Target As Range
Dim Cancel As Boolean
  
With ActiveSheet
Set rng = Range("B1:B3000")

'Find a column with nothing in it
  Set UnusedColumn = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).EntireColumn.Offset(0, 1)

'Create temporary calculation column to determine which cells to select (marked by an X)
  Intersect(rng.EntireRow, UnusedColumn) = Evaluate("IF(" & rng.Address & "="""","""",""X"")")

'Make Selection
  Intersect(UnusedColumn.SpecialCells(xlConstants).EntireRow, rng.EntireColumn).Select

'Remove Temporary Blank Calculations
  UnusedColumn.Clear

End With

End Sub

and the code to overwrite column A is:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:B").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2, Criteria1:="<>"
    Range("B1:B367").Select
    Selection.Copy
    Application.CutCopyMode = False
    Columns("A:A").Select
    Selection.Copy
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$B$3000").AutoFilter Field:=2
    Columns("A:A").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Unfortunately I need to use all this code in a much larger spreadsheet, where column A in the sample is at column C, and column B in the sample is at column M.
There are also 21,000 rows of data.

I simply cannot get the code in the sample file to work in the larger spreadsheet. What changes do I need to make to the code to make it run properly?

Many thanks.

Test Me.xlsm

[Paul_Hossler]

I think I understand the question

I'd just keep if simple. If this is too slow, there's probably ways to speed it up

Option Explicit

Sub CopyOver()
    Dim rSrc As Range, rDest As Range, rCell As Range
    
    Set rDest = ActiveSheet.Columns(3)   '   <------ change as needed
    Set rSrc = ActiveSheet.Columns(13)   '   <------ change as needed
    
    On Error GoTo NiceExit
    For Each rCell In rSrc.SpecialCells(xlCellTypeConstants).Cells
        Call rCell.Copy(rDest.Cells(rCell.Row))
    Next
NiceExit:
    
    
End Sub

[1819]

Thanks Paul, another incredibly useful answer for me (you have been terrific answering my questions).

The "On Error" trap was causing problems, in that I had some #VALUE!s in the cells, so I simply put

Cells.Replace "#VALUE!", "", xlWhole

in front and took out the error trap, and it works very well.

Speed's not a material concern.

Thank you again.