Macro to value Data

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,743
Office Version
  1. 2019
Platform
  1. Windows
I have data with Formula's in the following columns and rows



B18:M18 (Text in A18 Dept1)
B31:M31 (Text in A31 Dept2)
B45:M45 (Text in A31 Dept3)
B61:M61 (Text in A31 Dept4)

I would like to know if there is a better way to write the code, possible using range names as the rows may change from time to time so the ranges would have to be amended in the code


Your assistance in this regard is most appreciated


Code:
Sub Range_ValPRofits_BR1()
With Sheets("BR1")
.Range("B18:M18").Copy
.Range("B19").PasteSpecial Paste:=xlPasteValues
.Range("B31:M31").Copy
.Range("B32").PasteSpecial Paste:=xlPasteValues
.Range("B45:M45").Copy
.Range("B46").PasteSpecial Paste:=xlPasteValues
.Range("B61:M61").Copy
.Range("B62").PasteSpecial Paste:=xlPasteValues
   .Application.CutCopyMode = False
   End With
  
End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Are there any more future changes to be expected?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,743
Office Version
  1. 2019
Platform
  1. Windows
There will no future changes
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Right, somehow misunderstood. Most likely this is what you're after ...

VBA Code:
Sub Range_ValPRofits_BR1()

    Dim arr As Variant
    Dim n   As Variant
    Dim c   As Range

    arr = Array("NVNP_BR1", "UVNP_BR1", "SVCNP_BR1", "totalNP_Br1")

    Application.ScreenUpdating = False
    With Sheets("BR1")
        For Each n In arr
            For Each c In .Range(n)
                If Not InStr(1, .Cells(5, c.Column).Value, "current", vbTextCompare) > 0 Then
                    c.Copy
                    c.Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next c
        Next n
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

    Range("a1").Select
End Sub
 

howard

Well-known Member
Joined
Jun 26, 2006
Messages
5,743
Office Version
  1. 2019
Platform
  1. Windows
Thanks very much. This is perfect
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,970
Messages
5,621,889
Members
415,864
Latest member
cybid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top