Macro to copy paste values based on cell input ...

[Nina]

Hi,

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("B4874").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
else

If dmonth = "2" Then

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

.....

Thanks a lot for your input!

 

[dan dungan]

Hi Nina.

What's not happening?
Are you stepping through the code in the VBE?
Do you see an error message?
Which line is highlighted?

Dan

 

[Jef Gorbach]

Need more details/samples to work with, but did spot a couple of
things:
1. your code copies several column while your description says the
range is within a single column,
2. it sounds like your coping the selected rang to an unspecified
someplace else.
3. consider using a Select Case rather than an unwieldy 12-stage
If...then...else construct.

Sub PVPrImpct()
'Dim dmonth As Range
Dim SourceRange as Range

Set dmonth = Worksheets("Price Impact by Month").Range("A44")
select case dmonth
case 1 : SourceRange = Range("B48:B74")
case 2 : SourceRange = Range("C48:C74")
'etc
end case
SourceRange.copy Destination:= Range("xxxx").PasteSpecial
Paste:=xlPasteValues
Application.CutCopyMode=False

....

 

[Nina]

Hi Dan - thank you for willing to help out. Actually nothing happens, i don't
get an error message, absolutely nothing happens . I was wondering if it
could be related to cell A44 returning a number instead of a text ?

 

[Nina]

Hi Jef, Thanks your your assistance as well.
Actually based on the cell value on cell A44 the macro should choose the
column to copy and paste the values,

So if A44 is 1 it should copy the January column if A44 = 2 then it should
copy the february column .... as I mention I don't get an error message that
I could try to debug. :-(

Any help is really appreciated! If I were to use the case code you posted
.... How can I create the correlation between the cell A44 (which can be 1 to
12) to the column that I want to copy and paste. I want copy and paste
values on the same range (so A44 is = to 1 then copy and paste values for the
january range which is b48:B74).

Thank you so much again!