Macro cell reference help

[Guest]

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?

 

[Charles Harmon]

justaguyfromk

What does the formula look like?
Charles

 

[Guest]

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

 

[Dick Kusleika]

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.

 

[Charles Harmon]

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

 

[Guest]

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]

..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.

 

[Guest]

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

 

[Charles Harmon]

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

 

[Dick Kusleika]

That should not be happening. There must be something else going on there.
Post all the code that you're using.