Marco to copy and paste values based on cell value (cont)

[Nina]

Hi - I am reposting my question as I made a mistake on the ranges also I
think that momentum has been lost as the question was put last night ...
Thank you guys for your help!

Here is my post from last night with corrections ---

First of all thanks a lot for your assistance. I did spend some time
researching the excel community and trying few codes but I was not able to
find modify one to fit my needs, so I am hoping that someone can help figure
out why the code I wrote below does not work (I know it is not the best
coding possible but if works I am set Any other suggestions are welcome
of course

Cell A44 value changes (1 to 12) based on user selection (month of a date in
another worksheet). =MONTH('Direct Cat_Infl&Perf'!J3)

columns B to M are respectivelly, Jan to Dec and there are formulas in each
column cell( lines 48 to 74. )

I want to, copy/paste values on the specific column based on cell A44. i.e.
if cell A44 = 1 then copy/paste values for the Jan column (range B48:B74), if
A44 = 2 then copy/paste values for column Feb range C48:C74, the report will
change once month only .. I wrote the following but it is not working ....

Sub PVPrImpct()
'
Dim dmonth As Range

Set dmonth = Worksheets("Price Impact by Month").Range("A44")


' Jan

If dmonth = "1" Then

Range("B48:B74").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
else

If dmonth = "2" Then

Range("C48:C74").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

I repeat that till it reaches 12 (december)

Thanks a lot for your input!

 

[Rick Rothstein]

Are you simply copying the values from the calculated range back over the
formulas that produced them? If so, give this macro a try (it should handle
all of your months directly)...

Sub PVPrImpct()
With Range("B48:B74").Offset(, Worksheets( _
"Price Impact by Month").Range("A44"))
.Value = .Value
End With
End Sub

 

[JLGWhiz]

If you are trying to convert formulas to values only then

If dMonth = "1" Then
Range("B48:B74").Value = Range("B48:B74").Value
End If

 

[Nina]

Yes but the range will change accordingly to the value on A44. if value on
a44 = 1 then B48:B74 , if A44 = 2 then the range is C48:c74

I did try the code you provided but it does not work - it seems it runs but
the formulas don't became values. ...

 

[Rick Rothstein]

The code probably worked, but I forgot to adjust the offset value by one, so
more than likely you didn't see that it worked (on the wrong range). Use the
following code (just copy/paste it) instead of the code I posted earlier (it
should now handle the values in A44 correctly)...

Sub PVPrImpct()
With Range("B48:B74").Offset(, Worksheets( _
"Price Impact by Month").Range("A44") - 1)
.Value = .Value
End With
End Sub

 

[Nina]

Thank you all, I found the problem. It was related to the cell A44.
I created a msg box to show me what was the value for the variable dmonth,
and it returned 2/1/2010, though I had A44 cell as (=MONTH('Direct
Cat_Infl&Perf'!J3) and this formula was showing 1 or 2 or 3, etc.

I learned something today! ;-)

Thank you all for your time !!

 

[Nina]

Rick Thank you so much. It works perfectly ! Amazing!! I did find an
issue on cell A44 and I corrected it and now everything works smoothly!!
Thanks a bunch again!