I have had problems copying from one spreadsheet from another and ending up
with external references, so I am asking this question. Using Excel 2003.

I can create a simple spreadsheet B to update part of a criteria table in
spreadsheet A. The criteria table is the basis for a lot of lookup functions
in spreadsheet A. Users would copy B into the same folder as A, then just
click an "Update" button in B. This would unprotect the "Data" sheet in A,
copy a range from spreadsheet B to the "Data" sheet, then turn protection
back on for the "Data" sheet.

Issue 1 The users have renamed spreadsheet A, so when they click the
button in B I would like them to be able to choose from a list the name of
the spreadsheet they want to update. This would be simpler than typing in
the name. How do I program this?

Issue 2 I don't want to create external references or mess up the named
ranges in spreadsheet A. How can I avoid this?

I fhte user renamed a worksheet then the refer to in the names table will
also change. Therefore why have the user select a worksheet, instead just
get the sheet name from the named range. The named range will will not
change names.

"Steve" wrote:

I have had problems copying from one spreadsheet from another and ending up
with external references, so I am asking this question. Using Excel 2003.

I can create a simple spreadsheet B to update part of a criteria table in
spreadsheet A. The criteria table is the basis for a lot of lookup functions
in spreadsheet A. Users would copy B into the same folder as A, then just
click an "Update" button in B. This would unprotect the "Data" sheet in A,
copy a range from spreadsheet B to the "Data" sheet, then turn protection
back on for the "Data" sheet.

Issue 1 The users have renamed spreadsheet A, so when they click the
button in B I would like them to be able to choose from a list the name of
the spreadsheet they want to update. This would be simpler than typing in
the name. How do I program this?

Issue 2 I don't want to create external references or mess up the named
ranges in spreadsheet A. How can I avoid this?

For Issue 1 - you could, as an idea, create a CommandBar on the fly with a
DropDown Control & populate with all sheet names in activeworkbook.

Something like following may do what you want?

Sub CreateMenuBar()

DeleteBar

With Application.CommandBars.Add
.Name = "mysheets"
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating


With .Controls.Add(Type:=msoControlDropdown, temporary:=True)

.Tag = "sheetnames"
.Width = 200

For Each sh In ActiveWorkbook.Sheets

.AddItem sh.Name

Next

.OnAction = "testsheet"

.ListIndex = 0

End With

With .Controls.Add(Type:=msoControlButton, temporary:=True)

.Width = 50
.Caption = "Close"
.Style = msoButtonIconAndCaption
.Enabled = True
.OnAction = "DeleteBar"

End With

End With
End Sub

Sub testsheet()

Set ctrl = Application.CommandBars("mysheets").FindControl(Ta g:="sheetnames")
MsgBox ctrl.Text

End Sub

Sub DeleteBar()
On Error Resume Next
Application.CommandBars("mysheets").Delete
On Error GoTo 0
End Sub

--
jb


"Steve" wrote:

I have had problems copying from one spreadsheet from another and ending up
with external references, so I am asking this question. Using Excel 2003.

I can create a simple spreadsheet B to update part of a criteria table in
spreadsheet A. The criteria table is the basis for a lot of lookup functions
in spreadsheet A. Users would copy B into the same folder as A, then just
click an "Update" button in B. This would unprotect the "Data" sheet in A,
copy a range from spreadsheet B to the "Data" sheet, then turn protection
back on for the "Data" sheet.

Issue 1 The users have renamed spreadsheet A, so when they click the
button in B I would like them to be able to choose from a list the name of
the spreadsheet they want to update. This would be simpler than typing in
the name. How do I program this?

Issue 2 I don't want to create external references or mess up the named
ranges in spreadsheet A. How can I avoid this?

Joel & John & all,

Sorry, I guess I got my lingo mixed up. The user is saving workbook A (the
whole xls file) with a different name, not just a worksheet within a
workbook. I am using a range or ranges workbook B to update a range or
ranges in the "Data" sheet of workbook A. So with that clarification (I
hope) I will restate the questions ....

Issue 1 The users have renamed workbook A, so when they click the button
in workbook B I would like them to be able to choose from a list of Excel
files in that directory the name of the workbook (herein called "A") that
they want to update. This would be simpler than typing in the name. How do I
program this?

Issue 2 I don't want to create external references or mess up the named
ranges in workbook A. How can I avoid this?

Steve,
on a train at moment & just seen your respone - I have amended code a
submitted earlier to pick up all files in specified directory and show then
in dropdown.
Not tested but should work although you should be able to figure out any
changes needed.
Hope helpful

Sub CreateMenuBar()
Dim strFile
Dim strFolder
Dim mydata As String

'Folder where your workbooks are located
' change as required
strFolder = ThisWorkbook.Path & "\"

strFile = Dir(strFolder & "*.*", vbNormal)


DeleteBar

With Application.CommandBars.Add
.Name = "myfiles"
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating


With .Controls.Add(Type:=msoControlDropdown, temporary:=True)

.Tag = "filenames"
.Width = 200

Do While strFile < ""

.AddItem strFile


strFile = Dir


Loop


.OnAction = "testfile"

.ListIndex = 0

End With

With .Controls.Add(Type:=msoControlButton, temporary:=True)

.Width = 50
.Caption = "Close"
.Style = msoButtonIconAndCaption
.Enabled = True
.OnAction = "DeleteBar"

End With

End With
End Sub

Sub testfile()

Set ctrl = Application.CommandBars("myfiles").FindControl(Tag :="filenames")
MsgBox ctrl.Text

End Sub

Sub DeleteBar()
On Error Resume Next
Application.CommandBars("myfiles").Delete
On Error GoTo 0
End Sub



--
jb


"Steve" wrote:

Joel & John & all,

Sorry, I guess I got my lingo mixed up. The user is saving workbook A (the
whole xls file) with a different name, not just a worksheet within a
workbook. I am using a range or ranges workbook B to update a range or
ranges in the "Data" sheet of workbook A. So with that clarification (I
hope) I will restate the questions ....

Issue 1 The users have renamed workbook A, so when they click the button
in workbook B I would like them to be able to choose from a list of Excel
files in that directory the name of the workbook (herein called "A") that
they want to update. This would be simpler than typing in the name. How do I
program this?

Issue 2 I don't want to create external references or mess up the named
ranges in workbook A. How can I avoid this?