PasteSpecial - Defining a range that is variable...?

[VBAMePlease]

New- and by new I mean virgin-new - to VBA on Excel; with that in mind sorry if this seems excruciatingly easy.

I'm utilizing a Copy & Paste VBA that runs on an ActiveX Command Button.

When executed, it copies information from C18:C123 and pastes it into a column (H17:BY17) that matches the header for C17. It currently pastes the formulas, and I need it to paste values and source formatting.

Here is the current code I am running with:

Private Sub Dashboard_Base_Case_Update_Button_Click()


Set lookup_rng = Range("H17:BY17")


For Each cell In lookup_rng
    If cell.Value = Range("C17").Value Then
        Range("C18:C123").Copy
        cell.Offset(1, 0).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End If
Next cell




End Sub

Do I need to assign the lookup value a range name and then .pastespecial from there? At a loss and would appreciate the help.

 

[JoeMo]

Try:

If cell.Value = Range("C17").Value Then
        Range("C18:C123").Copy
        cell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If

 

[VBAMePlease]

Got it!

Appreciate the help!

 

[JoeMo]

Got it!

Appreciate the help!

You are welcome - thanks for the reply.

 

[MARK858]

I need it to paste values and source formatting.

Untested...

Private Sub Dashboard_Base_Case_Update_Button_Click()
    Set lookup_rng = Range("H17:BY17")
    For Each cell In lookup_rng
        If cell.Value = Range("C17").Value Then
            Range("C18:C123").Copy
            With cell.Offset(1, 0)
                .PasteSpecial Paste:=xlPasteValues
                .PasteSpecial Paste:=xlPasteFormats
            End With
            Application.CutCopyMode = False
        End If
    Next cell
End Sub