| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro cell reference help
I have a macro that I want to be able to copy and paste from and to different
cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
justaguyfromk
What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
The Cells that I want to copy from:
=CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
Maybe this
..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
This assumes your formula is in A1 thru B3.
If Range("V32") < 0 Then For i = 1 To 3 myrng = Cells(i, 1).Value myrng1 = Cells(i, 2).Value Range(myrng).Copy Destination:=Range(myrng1) Next End If Charles "Dick Kusleika" wrote in message ... Maybe this .Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
This formula works great!!!
But... I need it to paste just the values and not the formula... Any Ideas for that You have already saved me so much time with this help Thank you, Looking forward to your reply "Dick Kusleika" wrote: Maybe this ..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
..Range(.Range("B1").Text).Value = .Range(.Range("A1").Text).Value should do it for you if you just want values. If you want values and formatting, then you'll need to use a Copy and a PasteSpecial xPasteValues on two separate lines - but the range reference remains the same. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: This formula works great!!! But... I need it to paste just the values and not the formula... Any Ideas for that You have already saved me so much time with this help Thank you, Looking forward to your reply "Dick Kusleika" wrote: Maybe this ..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
This is now deleting the formula from the cell to be copied, the formula
needs to stay in the original location and just send the values to the destination cells. I think that we almost have this one down. PS. I added your web site to my list of favorites, found some useful information that I may be able to use as I try to continue my VBA skills. Thanks again "Dick Kusleika" wrote: ..Range(.Range("B1").Text).Value = .Range(.Range("A1").Text).Value should do it for you if you just want values. If you want values and formatting, then you'll need to use a Copy and a PasteSpecial xPasteValues on two separate lines - but the range reference remains the same. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: This formula works great!!! But... I need it to paste just the values and not the formula... Any Ideas for that You have already saved me so much time with this help Thank you, Looking forward to your reply "Dick Kusleika" wrote: Maybe this ..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
The formula I sent should not copy the formula in A1 it should only copy and
paste to location determined by your CONCATENATE formula. If value in V32 = 1 the =CONCATENATE("R",V32)= "R1" and =CONCATENATE("Y",V32)= "Y1" my formula now looks at R1 value and paste it to Y1. Charles "justaguyfromky" wrote in message ... This is now deleting the formula from the cell to be copied, the formula needs to stay in the original location and just send the values to the destination cells. I think that we almost have this one down. PS. I added your web site to my list of favorites, found some useful information that I may be able to use as I try to continue my VBA skills. Thanks again "Dick Kusleika" wrote: ..Range(.Range("B1").Text).Value = .Range(.Range("A1").Text).Value should do it for you if you just want values. If you want values and formatting, then you'll need to use a Copy and a PasteSpecial xPasteValues on two separate lines - but the range reference remains the same. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: This formula works great!!! But... I need it to paste just the values and not the formula... Any Ideas for that You have already saved me so much time with this help Thank you, Looking forward to your reply "Dick Kusleika" wrote: Maybe this ..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro cell reference help
That should not be happening. There must be something else going on there.
Post all the code that you're using. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: This is now deleting the formula from the cell to be copied, the formula needs to stay in the original location and just send the values to the destination cells. I think that we almost have this one down. PS. I added your web site to my list of favorites, found some useful information that I may be able to use as I try to continue my VBA skills. Thanks again "Dick Kusleika" wrote: ..Range(.Range("B1").Text).Value = .Range(.Range("A1").Text).Value should do it for you if you just want values. If you want values and formatting, then you'll need to use a Copy and a PasteSpecial xPasteValues on two separate lines - but the range reference remains the same. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: This formula works great!!! But... I need it to paste just the values and not the formula... Any Ideas for that You have already saved me so much time with this help Thank you, Looking forward to your reply "Dick Kusleika" wrote: Maybe this ..Range(.Range("A1").Text).Copy Destination:=.Range(.Range("B1").Text) where A1 and B1 have the concatentate formulas. I assume the concatenate formulas display something that looks like a cell address and you want to use what they return as the ranges to copy from and paste to. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com justaguyfromky wrote: The Cells that I want to copy from: =CONCATENATE("R",V32) =CONCATENATE("S",V32) =CONCATENATE("T",V32) The Cells that I want to paste to: =CONCATENATE("Y",V32) =CONCATENATE("Z",V32) =CONCATENATE("AA",V32) The "V32" is a number that is generated by another macro from 1 to 30. All Cells will not be copied every day, so I need it to be able to copy only the ones specified by the V32 Macro With ActiveSheet If .Range("V32").Value "0" Then Range("??").Select Selection.Copy Range("??").Select I want the range be read from the cell because it may change depending on the V32 Macro "Charles Harmon" wrote: justaguyfromk What does the formula look like? Charles "justaguyfromky" wrote in message ... I have a macro that I want to be able to copy and paste from and to different cells depending on other criteria. I have a cell with a concatenate formula that puts the actual cells that I want to copy from and to. My question is... How do I get the macro to read the cell that has the range I want to copy from and paste into to put the value of the cell as the range in the macro? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro using a cell reference | Excel Discussion (Misc queries) | |||
| Cell Reference Macro | Excel Discussion (Misc queries) | |||
| cell reference in macro | Excel Discussion (Misc queries) | |||
| Cell Reference in a Macro | Excel Programming | |||
| run a macro from a cell reference | Excel Programming | |||
Linear Mode
