VBA Copy paste special values based on cell reference

[benwork]

Hi all,

I'm a bit stuck on some code where I'm trying to copy paste special (remove the formulas) of a range based on a cell value.

If the cell value (DB626:DL626) is "Yes" then I want to copy paste special (or another way to lock in the values) of the column with a row range of 51:625

As an example, if DD597 = yes, then I want DD51:DD625 to be copy paste values to remove the formulas.

Is there a way to do this?

Thanks in advanced

 

[KeepTrying]

Hi,

I need followings to be refined:
- "If the cell value (DB626:DL626) is "Yes" then..." I suppose if one or more cell= "Yes" in that range then VBA code should do what you requested. Is that right?
- "As an example, if DD597 = yes, then...": Earlier you mentioned range DB626:DL626. What is the exact range where you want to check "Yes"?
- "...another way to lock in the values": which solution do you prefer: copy-paste formulas as values or lock those cells and protect worksheet?

Thanks.

 

[benwork]

Hi,

I need followings to be refined:
- "If the cell value (DB626:DL626) is "Yes" then..." I suppose if one or more cell= "Yes" in that range then VBA code should do what you requested. Is that right?
- "As an example, if DD597 = yes, then...": Earlier you mentioned range DB626:DL626. What is the exact range where you want to check "Yes"?
- "...another way to lock in the values": which solution do you prefer: copy-paste formulas as values or lock those cells and protect worksheet?

Thanks.

Thanks for the reply, I've been working long hours and I feel like my brain is mush and apparently Im tying that way too

1) There could be multiple columns with the yes value and all the columns with yes would then need to be copy paste values
2) Typo on my behalf, range is DB626:DL626 - the 597 comment was incorrect
3) Copy paste values is what is required

Hope this clarifies the original post.

Thanks in advanced

Cheers

Ben

 

[KeepTrying]

Hi Ben,

Please see code below:

Sub CopyPasteValues()


Dim cRange As Range, c As Range, actSheet As Worksheet


Application.ScreenUpdating = False


Set actSheet = ThisWorkbook.Worksheets("Sheet1") 'change sheet name if necessary
Set cRange = actSheet.Range("DB626:DL626") 'change range if necessary


For Each c In cRange.Cells
    If c.Value = "Yes" Then
        actSheet.Range(Cells(51, c.Column), Cells(625, c.Column)) = _
        actSheet.Range(Cells(51, c.Column), Cells(625, c.Column)).Value
    End If
Next c


Application.ScreenUpdating = True


End Sub

Cheers,

 

[benwork]

Hi Ben,

Please see code below:

Sub CopyPasteValues()


Dim cRange As Range, c As Range, actSheet As Worksheet


Application.ScreenUpdating = False


Set actSheet = ThisWorkbook.Worksheets("Sheet1") 'change sheet name if necessary
Set cRange = actSheet.Range("DB626:DL626") 'change range if necessary


For Each c In cRange.Cells
    If c.Value = "Yes" Then
        actSheet.Range(Cells(51, c.Column), Cells(625, c.Column)) = _
        actSheet.Range(Cells(51, c.Column), Cells(625, c.Column)).Value
    End If
Next c


Application.ScreenUpdating = True


End Sub

Cheers,

Thank you very much,

worked perfectly

Cheers

 

[KeepTrying]

You're welcome