Link to home
Start Free TrialLog in
Avatar of jfdinneen
jfdinneenFlag for Switzerland

asked on

Excel: Copy Range To Indirect Address In Different Range

I have a web query result in defined name "WebQueryResult" and a destination address in defined name "WebQueryDestination" (e.g., 'Output'!$G$6"). I need to copy WebQueryResult to WebQueryDestination using VBA. How do I use the equivalent of Indirect to return the range.value of WebQueryDestination?
Avatar of Norie
Norie

If you want to use WebQueryDestination in code you can use Range("WebQueryDestination").
Avatar of jfdinneen

ASKER

imnorie,

Thanks for the quick follow-up.
Here is the code I am currently using:
Private Sub cmdPaste_Click()

  Call CopyValues(wksht_Query.Range("WebQueryResult"), wksht_Query.Range("WebQueryDestination"))
  
End Sub

Sub CopyValues(rngSource As Range, rngTarget As Range)

  rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
 
End Sub

Open in new window

However, the result of the VBA call is to paste WebQueryResult to the address of WebQueryDestination ($E$6) rather than its value ('Output'!$G$6)?
Best wishes,

John
John

Try
 Range(Range("WebQueryDestination").Value)

Open in new window

imnorie,

I had already tried that option but it returned an error message:
"<Method 'Range' of object '_worksheet' failed>"

Best wishes,

John
John

Where exactly do you want to use the named range in the code you posted?
Inmorie,

In simple terms, I want to copy the value of the first named range (WebQueryResult = "Any text...") to the address calculated in the second named range(WebQueryDestination =  'Output'!$G$6").

Best wishes,

John
SOLUTION
Avatar of coreconcepts
coreconcepts
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
User generated image@Imnorie, I got this when running your solution above
Works for me.

I did get a but mixed up in the last post though.

Quote should be Output.

ie 'Output'!$G$6
imnorie / coreconcepts,
The following procedures produced the required result:
Private Sub cmdPaste_Click()

  Dim varNames As Variant
  Dim strTargetSheet As String
  Dim strTargetDefinedName As String
  Dim rngTarget As Range
  Dim rngSource As Range
  
  varNames = Split(wksht_Source.Range("WebQueryDestination"), "!", -1, vbTextCompare)
  strTargetSheet = varNames(0)
  strTargetDefinedName = varNames(1)
  Set rngTarget = Worksheets(strTargetSheet).Range(strTargetDefinedName)
  Set rngSource = wksht_Source.Range("WebQueryResult")
  
  Call CopyValues(rngSource, rngTarget)
  
End Sub

Sub CopyValues(rngSource As Range, rngTarget As Range)

  rngTarget.Resize(rngSource.Rows.Count, rngSource.Columns.Count).Value = rngSource.Value
 
End Sub

Open in new window

Best wishes,

John
John

The code I posted will also work, though it would need to be changed for the Value part.

I'm still wondering where 'Output'!$G$6 comes from.

Is it not possible to have it in 2 cells, one for the sheet and one for the range?

That would save you having to use Split in the code.
imnorie / coreconcepts,

Thank you both for your expert contributions. I trust you will view the points distribution as fair.

Best wishes,

John