Change sheet reference in new workbook

[jallin]

I am hoping someone can help with this problem. I have a workbook which
is called Forecast for 2005.xlt which has 13 sheets. One sheet,
"Months", is used as a reference for a drop down list (forms toolbar)
on all of the other sheets. This is set by Right Click > Format Control
and then enter Months!$A$1:$A$13. Everything works fine until I try to
create a new workbook. Using VBA I copy the last sheet in the old
workbook, the "Months" sheet, and the code module to the new workbook.
The problem is that the drop down list in the new workbook still refers
to the "Months" sheet from the old workbook ( '[Forecast for
2005.xlt]Months'!$A$1:$A$13 ). Is there anyway that I can set the
reference to the workbook that the drop down is in? I have looked at a
lot of these postings and have not been able to find a problem like
this one. Thank you in advance for any help offered.

 

[Dave Peterson]

Are you copying the stuff using code or manually?

If manually, you can change the range by rightclicking on each of the dropdowns.

Or you could use some code.

I assumed that the dropdown was always named "drop down 1" on each sheet.

Option Explicit
Sub testme()
Dim myDD As DropDown
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Set myDD = Nothing
On Error Resume Next
Set myDD = wks.DropDowns("drop down 1")
On Error GoTo 0

If myDD Is Nothing Then
'skip this worksheet
Else
myDD.ListFillRange _
= ActiveWorkbook.Worksheets("months").Range("a1:a13") _
.Address(external:=True)
myDD.LinkedCell = wks.Range("a1").Address(external:=True)
End If
Next wks
End Sub

I am hoping someone can help with this problem. I have a workbook which
is called Forecast for 2005.xlt which has 13 sheets. One sheet,
"Months", is used as a reference for a drop down list (forms toolbar)
on all of the other sheets. This is set by Right Click > Format Control
and then enter Months!$A$1:$A$13. Everything works fine until I try to
create a new workbook. Using VBA I copy the last sheet in the old
workbook, the "Months" sheet, and the code module to the new workbook.
The problem is that the drop down list in the new workbook still refers
to the "Months" sheet from the old workbook ( '[Forecast for
2005.xlt]Months'!$A$1:$A$13 ). Is there anyway that I can set the
reference to the workbook that the drop down is in? I have looked at a
lot of these postings and have not been able to find a problem like
this one. Thank you in advance for any help offered.

 

[jallin]

You're my hero! Thank you so much for you reply. It works like a charm!