Formula that defines a cell range

kevinmda

New Member
Joined
May 29, 2019
Messages
4
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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"


Code:
celladd = Cells(2, 6)
Range(celladd).Copy
Worksheets("sheet2").Paste
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top