copy data from one file to another using matching range names

[Ken Cowen]

I have a file that is populated with some specific cells in a master file. Rather than copying each cell by explicitly coding for the cell range names, I am trying to copy the value of only those cells that have a "hdr_" at the beginning of their range names.

I can't get the following code to copy anything

For Each nm In wb.Names

If nm.Name Like "hdr_*" Then

On Error Resume Next
' wb.Sheets("Variables").Range(nm.Name).Value = tmp.Sheets("Rates").Range(nm.Name).Value
wb.Range(nm.Name).Value = tmp.Range(nm.Name).Value
On Error GoTo 0

End If


Next nm

When I debug.print the qualifying range names from each workbook the lists match. For some reason the way I am addressing the cells in both worksheets fails. If there is a range name "hdr_item" in both the wb and the tmp workbooks, it seems like the value in the tmp workbook should go to the corrosponding range name in the wb workbook. But it doesn't work. Any ideas? thanks

Ken

 

[JLGWhiz]

did you try it with just Range(nm).Value

 

[Ken Cowen]

It recognizes that as the value in the range with the name hdr_ something in the new workbook that I am creating, so half the problem is solved. I have the range name on both sides of the equation, and now I just need to know how to address the value in the range of the same name in my template workbook

Range(nm.Name).Value = tmp.Range(nm.Name).Value

the left side is now recognized; how to get the right side?

Thanks

Ken

 

[JLGWhiz]

It recognizes that as the value in the range with the name hdr_ something in the new workbook that I am creating, so half the problem is solved. I have the range name on both sides of the equation, and now I just need to know how to address the value in the range of the same name in my template workbook

Range(nm.Name).Value = tmp.Range(nm.Name).Value

the left side is now recognized; how to get the right side?

Thanks

Ken

I think that if you used the syntax

 wb.Sheets("Variables").Range(nm) = tmp.Sheets("Rates").Range(nm).Value

it would work. Provided that both sheets have that named range.

 

[Ken Cowen]

That is what I thought. I left that line in my original snippet post, commented out. If I debug.print all the range names for each workbook I get the same list of names. I do that using

For each nm in wb.names
debug.print nm.name
next nm
and
For each nm in tmp.names
debug.print nm.name
next nm

That prints just the the range names, doesn't show the sheet. They are all workbook level names.

I am back to doing it in two steps, which works like below
Range("hdr_ship").Value = TextBox1.Value
Range("hdr_MHI_no").Value = TextBox2.Value
Range("hdr_start").Value = TextBox3.Value
Range("hdr_finish").Value = TextBox4.Value

where both workbooks are populated from the userform. I was trying to make work so that I did not have to explicitly list all the range names in the code.

Writing this just gave me an idea about something I need to check.
thanks
Ken

I think that if you used the syntax

 wb.Sheets("Variables").Range(nm) = tmp.Sheets("Rates").Range(nm).Value

it would work. Provided that both sheets have that named range.

 

[JLGWhiz]

I don't believe we are communicating very well. Look at the difference between this:
Range(nm.Name)
and this:
Range(nm)

My question was did you try it with the Range(nm.Name) changed to just range(nm)?

 

[JLGWhiz]

I don't believe we are communicating very well. Look at the difference between this:
Range(nm.Name)
and this:
Range(nm)

My question was did you try it with the Range(nm.Name) changed to just range(nm)?

It is really odd. Yesterday I had no problem getting values using that syntax. Today, I cannot even get it to return the range or range name in the For...Next statement. It is giving me ="Sheet1!#Ref!" for a couple of sheets, then gives the referstorange value.

Oho! Got this to work.

Sub t()
Dim nm As Variant
    For Each nm In Name
        MsgBox nm.RefersToRange.Value
    Next
End Sub

 

[JLGWhiz]

This eliminates the bad RefersToRange values.

Sub t()
Dim nm As Variant
    For Each nm In Names
        If InStr(nm, "#") = 0 Then
            MsgBox nm.RefersToRange.Value
        End If
    Next
End Sub

 

[Ken Cowen]

You are correct, we weren't communicating well and it was on my side. sorry. I tried it and I swear is did not work, then, I tried a few other things and they didn't work, then I tried

Range(nm).Value = cwb.Sheets("Rates").Range(nm.Name).Value

and it works. Then I tried

Range(nm).Value = cwb.Sheets("Rates").Range(nm).Value

and it works (note the truncation of nm.name to nm)

then I tried

wb.range("summary").Range(nm).Value = cwb.Sheets("Rates").Range(nm).Value

and it worked. Not sure exactly what is going on, but I am going to assume it will keep on working.

Thank you for your help.

Ken

 

[JLGWhiz]

You are correct, we weren't communicating well and it was on my side. sorry. I tried it and I swear is did not work, then, I tried a few other things and they didn't work, then I tried

Range(nm).Value = cwb.Sheets("Rates").Range(nm.Name).Value

and it works. Then I tried

Range(nm).Value = cwb.Sheets("Rates").Range(nm).Value

and it works (note the truncation of nm.name to nm)

then I tried

wb.range("summary").Range(nm).Value = cwb.Sheets("Rates").Range(nm).Value

and it worked. Not sure exactly what is going on, but I am going to assume it will keep on working.

Thank you for your help.

Ken

I don't work with the Names collection hardly at all, obviously, so I had to do some experimenting myself. Glad you got it working.
Regards, JLG