Macro to value Data

[howard]

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

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]

You could consider placing the affected addresses in an array. If you want to avoid future necessary changes to your code, the use of named ranges is recommended.

Sub Range_ValPRofits_BR1()
    Dim arr As Variant
    Dim n   As Variant

    arr = Array("B18:M18", "B31:M31", "B45:M45", "B61:M61")

    With Sheets("BR1")
        For Each n In arr
            .Range(n).Copy
            .Range(n).Offset(1, 0).PasteSpecial xlPasteValues
        Next n
    End With
    Application.CutCopyMode = False
End Sub

 

[howard]

Thanks for the Help. Instead in using B18:M18 in the array, could I named the range say BR1_Dep1 and the use BR1_Dep1 in the array ?

 

[howard]

Just let you know that I have sorted out the range names in the array

 

[GWteB]

Your are welcome and thanks for letting me know. Glad it's sorted

 

[howard]

Hi GWteB

I have used the following code with you kindly helped me with to range value certain rows and columns using range values


Where row 5 eg B5 contains "Current", then the row in that column must not be range valued i.e copied and paste valued


It would be appreciated if you could kindly amend my formula to accommodate this

 Sub Range_ValPRofits_BR1()

 Dim arr As Variant
    Dim n   As Variant

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

   With Sheets("BR1")
For Each n In arr
.Range(n).Copy
.Range(n).Offset(1, 0).PasteSpecial xlPasteValues
Next n

End With
Application.CutCopyMode = False
Range("a1").Select

   
End Sub

 

[howard]

See link below containing sample data

Range Value Data Except Current.xlsm

 

[howard]

I have also posted on Macro to Range Value specific columns

 

[GWteB]

If I understood correctly, you are looking for something like this ...

Sub Range_ValPRofits_BR1()

    Dim arr As Variant
    Dim n   As Variant
    
    arr = Array("NVNP_BR1", "UVNP_BR1", "SVCNP_BR1", "totalNP_Br1")
    
    With Sheets("BR1")
        For Each n In arr
            If Not InStr(1, .Range("B5").Value, "current", vbTextCompare) > 0 Then
                .Range(n).Copy
                .Range(n).Offset(1, 0).PasteSpecial xlPasteValues
            End If
        Next n
    End With
    Application.CutCopyMode = False
    
    Range("a1").Select
End Sub

 

[howard]

Thanks or the help. Where row 5 contains "Current" which in the example is B5 , then only Col B i.e B9 must not be copied to B10. C9:M9 must still be copied and pasted to C10 onwards (C10:M10) using pastespecial Values

If B5, C5 contained "Current" then these colulunns must be ignored and D9:M9 to be copies and pasted as values into D10 onwards


Kindly amend your code accordingly