Is there a way to copy named ranges to another workbook?

[Burrgogi]

I have 2 workbooks that are similar to each other. Each workbook contains several worksheets.

Workbook #1:
sheet 1
sheet 2
sheet 3
sheet 4
etc.

Workbook #2:
sheet 1
sheet 2
sheet 3
sheet 4

There are named ranges all over the workbook in various locations. The only one that I care about is sheet 4. I want to copy all the named ranges into Workbook #1 'sheet 4' over to Workbook #2 'sheet 4'

I know I can just as easily use the copy worksheet feature in Excel but that poses a separate problem for me. I have hyperlinks in 'sheet 1' pointing to these named ranges and when I copied 'sheet 4' from one workbook to another, I found that it really messes up the hyperlinks.

I've googled for an answer but I'm not getting anything useful.

 

[kevin9999]

See if the following comes close to what you want. You'll need to adjust the actual workbook & worksheet names to suit your actual case, and the code assumes you wanted the named ranges copied to the same locations in workbook 2 where they came from in workbook 1. Assumes both workbooks are open when you run the code, and that the code is housed in workbook 1.

Option Explicit
Sub Burrgogi()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook Two")
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb1.Worksheets("Sheet4")
    Set ws2 = wb2.Worksheets("Sheet4")
    
    Dim RngNm, c
    For Each RngNm In wb1.Names
        ws1.Activate
        If RngNm.RefersToRange.Parent.Name = ActiveSheet.Name Then
            c = ws1.Range(RngNm).Cells(1).Address
            ws1.Range(RngNm).Copy ws2.Range(c)
        End If
    Next RngNm
End Sub

 

[Burrgogi]

See if the following comes close to what you want. You'll need to adjust the actual workbook & worksheet names to suit your actual case, and the code assumes you wanted the named ranges copied to the same locations in workbook 2 where they came from in workbook 1

Sorry I should have mentioned that the cell addresses will be slightly different. Maybe it will help if I upload a brief screenshot of the 2 worksheets in question.

The named ranges are contained in column A only - nowhere else.

Each DLC has a named range. In a case with multiple DLCs such as "Bioshock Infinite" shown at the top of the screenshot, I grouped all 3 and named it as "Bioshock Infinite". I hope this makes it more clear what I'm trying to do.

 

[kevin9999]

Sorry I should have mentioned that the cell addresses will be slightly different. Maybe it will help if I upload a brief screenshot of the 2 worksheets in question.

The named ranges are contained in column A only - nowhere else.

Each DLC has a named range. In a case with multiple DLCs such as "Bioshock Infinite" shown at the top of the screenshot, I grouped all 3 and named it as "Bioshock Infinite". I hope this makes it more clear what I'm trying to do.

So is it the case that you want each named range copied to the next available empty row in the destination sheet? And is it the case that even "DLC's" with a single row in the source sheet would also be a named range? Does the destination sheet need to be cleared of existing data first?

 

[Burrgogi]

So is it the case that you want each named range copied to the next available empty row in the destination sheet? And is it the case that even "DLC's" with a single row in the source sheet would also be a named range? Does the destination sheet need to be cleared of existing data first?

Good questions. I can see how this is going to be quite difficult due to the varying contents of each cell.

The more I think about this, I'm beginning to think I should make the edits by hand. To answer your questions:

1) So is it the case that you want each named range copied to the next available empty row in the destination sheet?

No. I need the contents of the cell or cells matched. In the example of the "Europa Universalis IV" the 3 DLCs are contained in rows 26-28. However in the destination worksheet, they are in rows 31-34 because there were new entries created above these rows.

2) And is it the case that even "DLC's" with a single row in the source sheet would also be a named range?
Yes

3) Does the destination sheet need to be cleared of existing data first?
Yes

Like I said, I can see how this could be quite difficult. If it's too laborious or time consuming - please don't bother writing a VBA code. This is strictly for my personal game library record keeping and nothing related for work so I would hate for you to spend a lot of time on it. Thanks.

 

[kevin9999]

I think you may be in danger of overcomplicating this. Try the following code (change workbook/worksheet names as appropriate) on a copy of your files and check whether the links problem still exists.

Option Explicit
Sub Burrgogi_v2()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook Two")
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb1.Worksheets("Sheet4")
    Set ws2 = wb2.Worksheets("Sheet4")
    
    'First clear workbook 2, sheet 4
    ws2.UsedRange.Offset(1).ClearContents
    
    'Copy new data next
    ws1.UsedRange.Offset(1).Copy ws2.Cells(2, 1)
End Sub

 

[Burrgogi]

I think you may be in danger of overcomplicating this. Try the following code (change workbook/worksheet names as appropriate) on a copy of your files and check whether the links problem still exists.

Option Explicit
Sub Burrgogi_v2()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook Two")
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb1.Worksheets("Sheet4")
    Set ws2 = wb2.Worksheets("Sheet4")
  
    'First clear workbook 2, sheet 4
    ws2.UsedRange.Offset(1).ClearContents
  
    'Copy new data next
    ws1.UsedRange.Offset(1).Copy ws2.Cells(2, 1)
End Sub

I just tried your code and it's getting caught up by the "Sheet4" reference. The actual name of the worksheet is: DLC Sheet (Epic only)

I said it was "Sheet 4" in my original post to make things quicker and simplify things - I guess I shouldn't have done that.

Anyways, I tried modifying your code to this & it's still not working.

Set ws1 = wb1.Worksheets("'DLC Sheet (Epic only)'")

 

[kevin9999]

I tried modifying your code to this & it's still not working.

I what way is is not working? Are you getting an error message when you try to run the code, and if so, what does it say?

I changed the sheet 4 name to "DLC Sheet (Epic only)" on my test file & the copy worked fine for me.

Option Explicit
Sub Burrgogi_v3()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook Two")
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb1.Worksheets("DLC Sheet (Epic only)")
    Set ws2 = wb2.Worksheets("Sheet4")
    
    'First clear workbook 2, sheet 4
    ws2.UsedRange.Offset(1).ClearContents
    
    'Copy new data next
    ws1.UsedRange.Offset(1).Copy ws2.Cells(2, 1)
End Sub

If it still doesn't work for you, I don't think there's much more I can suggest without seeing your actual file - which you could possibly share via Dropbox, Google Drive etc?

 

[Burrgogi]

I what way is is not working? Are you getting an error message when you try to run the code, and if so, what does it say?

I changed the sheet 4 name to "DLC Sheet (Epic only)" on my test file & the copy worked fine for me.

Option Explicit
Sub Burrgogi_v3()
    Dim wb1 As Workbook, wb2 As Workbook
    Set wb1 = ThisWorkbook
    Set wb2 = Workbooks("Workbook Two")
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb1.Worksheets("DLC Sheet (Epic only)")
    Set ws2 = wb2.Worksheets("Sheet4")
  
    'First clear workbook 2, sheet 4
    ws2.UsedRange.Offset(1).ClearContents
  
    'Copy new data next
    ws1.UsedRange.Offset(1).Copy ws2.Cells(2, 1)
End Sub

If it still doesn't work for you, I don't think there's much more I can suggest without seeing your actual file - which you could possibly share via Dropbox, Google Drive etc?

The error message says: Subscript out of range

Here's a screenshot.

Oh and 1 more thing. I want to clarify that both of the workbooks have a worksheet with the name "DLC Sheet (Epic only)". I don';t know if that is why the error is happening or not.

 

[kevin9999]

The error message says: Subscript out of range

Here's a screenshot.

Oh and 1 more thing. I want to clarify that both of the workbooks have a worksheet with the name "DLC Sheet (Epic only)". I don';t know if that is why the error is happening or not.

Is that a space you've got between the closing parenthesis and the double quotation mark in your version of the code? If it is - there's your problem, the code is looking for a sheet name with a space at the end of its name.