Moving cell values based on Titles

[Paul Bray-Boyce]

Hello everyone, I know just enough about VBA Macros to fumble through. I have a question about Macro I found. the Macro copies data from sheet 1 "Main" to sheet 2 "Scoretracker" Based on the matching titles in both sheets. So if there is only one matching title it would only copy that columns data from Sheet 1 "Main". to Sheet 2 "ScoreTracker" The Macro does jcopy, I would like to have it do a special paste and just move the value from sheet 1 into Sheet 2.

There are formulas in sheet 1. so when Macro runs it is copying the Formulas and as you can guess mess up the Scoretracker.

thank you for any help you can provide.

Sub MoveScores()
        Dim r As Range, c As Range, msg As String
        With Sheets("Main").Range("D1").CurrentRegion
            For Each r In Sheets("ScoreTracker").Range("D1:BW1")
           
                Set c = .Rows(1).Find(r.Value, , , xlWhole, , 0)
               
                If Not c Is Nothing Then
                   .Columns(c.Column).Copy r
                   
                Else
                    msg = "ScoreTracker has been updated"
                End If
            Next
           
            Application.CutCopyMode = False
        End With
       If Len(msg) Then MsgBox msg
    End Sub

 

[Hui]

I think you need to change:

If Not c Is Nothing Then
                   .Columns(c.Column).Copy r

to:

If Not c Is Nothing Then
                   .Columns(c.Column).Copy
                  r.PasteSpecial Paste:=xlPasteValues

 

[Paul Bray-Boyce]

Thanks Hui, that did the trick.... issue solved

 

[Hui]

You were very close