Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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! :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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! :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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" wrote in message ... 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! :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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" wrote: 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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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! :-) . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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! :-) . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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 !! "JLGWhiz" wrote: 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" wrote in message ... 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! :-) . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Marco to copy and paste values based on cell value (cont)
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! "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Nina" wrote in message ... 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! :-) . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy paste values based on cell input ... | Excel Programming | |||
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 | |||
Marco button, Copy and paste depending on criteria | Excel Programming | |||
Marco to copy and paste column as well as edit link | Excel Programming |