OxfordCurmudgeon
New Member
- Joined
- Oct 24, 2023
- Messages
- 21
- Office Version
- 2010
- 2003 or older
- Platform
- Windows
My goal is to copy a named range from one workbook to another. The fun arises because these are two generations of the same workbook. The named ranges and sheet names are the same in both workbooks. I need to copy the contents from the EmployeeInfo range in the older workbook into the same named range in the newer workbook. Should be simple, but I'm getting tripped up because there are spaces in the workbook names. I concocted a much simplified version in hopes of getting some insights here.
Two workbooks: OldBook.xlsm and Old Book.xlsm. The only difference is the space in the name. (I was going to upload the two files, but apparently XL2BB doesn't do workbooks.) If you want to follow along, create a new worksheet with at least two worksheets. On Sheet2, create a named range called EmployeeInfo. (Size and location are not important.) Save it as OldBook.xlsm, then again as Old Book.xlsm.
The code is below. Set up 8 variations on a string to specify a named range. r1, r3, and r5 refer to OldBook; r2, r4, and r6 refer to Old Book. Each string then gets passed to the Range command and the results reported -- either success (the range name) or ethe rror #. r1, r3, and r5 all work; the Range statements return the expected address range. r2 and r4 give error 1004; tripped up by the space in the workbook name. r6 works and gives us a rule that works in all cases: Put the workbook name in square brackets, and append the worksheet name. Put all that in single quiotes followed by an exclamation point and the range name. Finally put all that in double quotes.
But for a workbook-scope named range, we shouldn't need to specify the sheet name. R1 shows that workbook name and range name are sufficient when there are no spaces in the workbook name. R7 and r8 are my attempts to get the same outcome when there are spaces in the workbook name. Neither works.
Question #1: Is there a way to make the r1-style reference work when there are spaces in the workbook name?
Question #2: Am I missing a simpler solution?
RESULTS:
R1: $B$2:$C$3
R2: Err #1004
R3: $B$2:$C$3
R4: Err #1004
R5: $B$2:$C$3
R6: $B$2:$C$3
R7: Err #1004
R8: Err #1004
CODE:
Two workbooks: OldBook.xlsm and Old Book.xlsm. The only difference is the space in the name. (I was going to upload the two files, but apparently XL2BB doesn't do workbooks.) If you want to follow along, create a new worksheet with at least two worksheets. On Sheet2, create a named range called EmployeeInfo. (Size and location are not important.) Save it as OldBook.xlsm, then again as Old Book.xlsm.
The code is below. Set up 8 variations on a string to specify a named range. r1, r3, and r5 refer to OldBook; r2, r4, and r6 refer to Old Book. Each string then gets passed to the Range command and the results reported -- either success (the range name) or ethe rror #. r1, r3, and r5 all work; the Range statements return the expected address range. r2 and r4 give error 1004; tripped up by the space in the workbook name. r6 works and gives us a rule that works in all cases: Put the workbook name in square brackets, and append the worksheet name. Put all that in single quiotes followed by an exclamation point and the range name. Finally put all that in double quotes.
But for a workbook-scope named range, we shouldn't need to specify the sheet name. R1 shows that workbook name and range name are sufficient when there are no spaces in the workbook name. R7 and r8 are my attempts to get the same outcome when there are spaces in the workbook name. Neither works.
Question #1: Is there a way to make the r1-style reference work when there are spaces in the workbook name?
Question #2: Am I missing a simpler solution?
RESULTS:
R1: $B$2:$C$3
R2: Err #1004
R3: $B$2:$C$3
R4: Err #1004
R5: $B$2:$C$3
R6: $B$2:$C$3
R7: Err #1004
R8: Err #1004
CODE:
VBA Code:
Private Sub Test()
r1 = "OldBook.xlsm!EmployeeInfo"
r2 = "Old Book.xlsm!EmployeeInfo"
r3 = "[OldBook.xlsm]Sheet2!EmployeeInfo"
r4 = "[Old Book.xlsm]Sheet2!EmployeeInfo"
r5 = "'[OldBook.xlsm]Sheet2'!EmployeeInfo"
r6 = "'[Old Book.xlsm]Sheet2'!EmployeeInfo"
r7 = "'[Old Book.xlsm]'!EmployeeInfo"
r8 = "Old%20Book.xlsm!EmployeeInfo"
On Error Resume Next
Err.Number = 0
Debug.Print "R1: " & Range(r1).Address
If Err.Number <> 0 Then Debug.Print "R1: Err #" & Err.Number
Err.Number = 0
Debug.Print "R2: " & Range(r2).Address
If Err.Number <> 0 Then Debug.Print "R2: Err #" & Err.Number
Err.Number = 0
Debug.Print "R3: " & Range(r3).Address
If Err.Number <> 0 Then Debug.Print "R3: Err #" & Err.Number
Err.Number = 0
Debug.Print "R4: " & Range(r4).Address
If Err.Number <> 0 Then Debug.Print "R4: Err #" & Err.Number
Err.Number = 0
Debug.Print "R5: " & Range(r5).Address
If Err.Number <> 0 Then Debug.Print "R5: Err #" & Err.Number
Err.Number = 0
Debug.Print "R6: " & Range(r6).Address
If Err.Number <> 0 Then Debug.Print "R6: Err #" & Err.Number
Err.Number = 0
Debug.Print "R7: " & Range(r7).Address
If Err.Number <> 0 Then Debug.Print "R7: Err #" & Err.Number
Err.Number = 0
Debug.Print "R8: " & Range(r8).Address
If Err.Number <> 0 Then Debug.Print "R8: Err #" & Err.Number
On Error GoTo 0
End Sub
Last edited by a moderator:
