Hi,

I was wondering if you could help me out modify a code that currently paste
values to change the font color to automatic or black and to add multiple
ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81)

The current code is below and works Great! Thanks to everyone that
helped/will help me out!!

Sub PVPrImpct()

With Range("b48:b74").Offset(, Worksheets( _
"Summary by month MTD").Range("A4") - 1)
.Value = .Value
End With
End Sub

Thanks again!

I'm not sure if those ranges are before the offset or after...

Maybe something like this will give you an idea:

Option Explicit
Sub PVPrImpct()

Dim myArea As Range
Dim HowManyCols As Long
Dim myRng As Range

HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1

Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81")

For Each myArea In myRng.Offset(0, HowManyCols).Areas
With myArea
.Value = .Value
End With
Next myArea

End Sub


Nina wrote:

Hi,

I was wondering if you could help me out modify a code that currently paste
values to change the font color to automatic or black and to add multiple
ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81)

The current code is below and works Great! Thanks to everyone that
helped/will help me out!!

Sub PVPrImpct()

With Range("b48:b74").Offset(, Worksheets( _
"Summary by month MTD").Range("A4") - 1)
.Value = .Value
End With
End Sub

Thanks again!

--

Dave Peterson

Hi Dave - thanks for your help. I have not tested yet, but instead of pasting
values I would need it to change the font color to black or automatic. Thank
you so much again!

"Dave Peterson" wrote:

I'm not sure if those ranges are before the offset or after...

Maybe something like this will give you an idea:

Option Explicit
Sub PVPrImpct()

Dim myArea As Range
Dim HowManyCols As Long
Dim myRng As Range

HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1

Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81")

For Each myArea In myRng.Offset(0, HowManyCols).Areas
With myArea
.Value = .Value
End With
Next myArea

End Sub


Nina wrote:

Hi,

I was wondering if you could help me out modify a code that currently paste
values to change the font color to automatic or black and to add multiple
ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81)

The current code is below and works Great! Thanks to everyone that
helped/will help me out!!

Sub PVPrImpct()

With Range("b48:b74").Offset(, Worksheets( _
"Summary by month MTD").Range("A4") - 1)
.Value = .Value
End With
End Sub

Thanks again!

--

Dave Peterson
.

Record a macro when you change the font color to automatic and I bet you'll see
where to add it to your code.

If you need help, post back and explain what those ranges are (I'm still
confused).

Nina wrote:

Hi Dave - thanks for your help. I have not tested yet, but instead of pasting
values I would need it to change the font color to black or automatic. Thank
you so much again!

"Dave Peterson" wrote:

I'm not sure if those ranges are before the offset or after...

Maybe something like this will give you an idea:

Option Explicit
Sub PVPrImpct()

Dim myArea As Range
Dim HowManyCols As Long
Dim myRng As Range

HowManyCols = Worksheets("Summary by month MTD").Range("A4").Value - 1

Set myRng = ActiveSheet.Range("b48:b74,E8:E17,E24:E33,E40:E49, E56:E81")

For Each myArea In myRng.Offset(0, HowManyCols).Areas
With myArea
.Value = .Value
End With
Next myArea

End Sub


Nina wrote:

Hi,

I was wondering if you could help me out modify a code that currently paste
values to change the font color to automatic or black and to add multiple
ranges (new ranges are E8:E17, E24:E33,E40:E49 and E56:E81)

The current code is below and works Great! Thanks to everyone that
helped/will help me out!!

Sub PVPrImpct()

With Range("b48:b74").Offset(, Worksheets( _
"Summary by month MTD").Range("A4") - 1)
.Value = .Value
End With
End Sub

Thanks again!

--

Dave Peterson
.


--

Dave Peterson