Formula that defines a cell range

[kevinmda]

How can I get a macro to copy a cell that defines a cell range using a formula so that the macro will copy and paste that cell "range" into another worksheet and not just the formula?

Goto.Range (" ").value
Goto.reference

 

[offthelip]

I am not quite clear what your problem is, does this sort of code work?
Assuming F2 is where your cells with the range address in it in the format "A3:B10"

celladd = Cells(2, 6)
Range(celladd).Copy
Worksheets("sheet2").Paste

 

[kevinmda]

I do not think that will work?
I am using a formula to define a cell range. I need a macro to copy and paste the cell range defined by the formula. Would that cell need to be defined as text in the macro coding? (range (“cell containing the formula”).dim as text? Trying to define a copy and paste range along with the print range automatically using a cell formula.

 

[offthelip]

I do not think that will work?

Have you tried it, I did with a simple formula that concatenated 4 cells, with letters in two and numbers in two and included a ":" betwen them and it worked fine

 

[kevinmda]

Formula
=IF(MIN(T60:T1017)=0,"","C"&MIN(T60:T1017)&":K"&MAX(T60:T1017))
resultC60:K71

 

[JrayK2]

You should be able to just do something like:

Dim urrange As String


urrange = Sheet1.Range("A1").Value 'Replace sheet1 with correct sheet and A1 with formula cell


Sheet1.Range(urrange).Copy 'replace sheet1 with what sheet has the range you want to copy
Sheet2.Range("A1").PasteSpecial xlPasteValues 'replace Sheet2 with sheet to paste to and A1 with the cell to paste range to

 

[kevinmda]

The below code works correctly Thank You
But I will need to modify it to include about 20 (if - end if) statements and may need to call on your expertise again.

Sub Macro1()

Sheets("Wt Rpt").Select
celladd = Cells(59, 20)
Range(celladd).Copy
Worksheets("sheet1").Select
Range("B60").Select
Selection.PasteSpecial Paste:=xlValues

End Sub

 

[offthelip]

if you are thinking about using 20 if end if statement , it is often easier to define a couple of arrays and do the "if" check in a loop.
here is an example that check cells A2 for variaous values and writes "output" is one of the cells in column J, the row depends on what the numbe in A2 is

arr = Array(1, 4, 6, 8, 10, 203)
outarr = Array(1, 2, 3, 4, 5, 6)
addres = "not found"
inpt = Cells(2, 1)
For i = 0 To 5
 If inpt = arr(i) Then
  Cells(outarr(i), 10) = "output"
 End If
Next i

You can use all sort a variations of this to check different cells in the IF test, or write different value to the output or do all at once, i.e 20 totally different if tests.