Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy paste values based on cell input ...
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("B48:D74").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! :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy paste values based on cell input ...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy paste values based on cell input ...
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 .... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy paste values based on cell input ...
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 ? "dan dungan" wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy paste values based on cell input ...
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! "Jef Gorbach" wrote: 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 .... . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with macro to copy, paste, and clear cell values | Excel Discussion (Misc queries) | |||
Macro copy data based on cell formatting and paste special values | Excel Programming | |||
Identify row based on criteria and copy paste special values only | Excel Programming | |||
run macro with input msg based on cell input | Excel Discussion (Misc queries) | |||
Copy / Paste Specifc Row based on Input Box Value | Excel Programming |