I don't see red in the NGs.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"SamuelT" wrote in
message ...

Thanks Bob.

Just tried to run the macro. I get a 'syntax error' on the line
coloured red.

Sub TeamChange()
Const sOtherBook As String = "Services Synergy 2 - Sell RAC_BSM to NUGI
Base.xls "

ActiveSheet.Unprotect
Windows("Monthly Status Report Template v.2.0.xls").Activate
With Workbooks(sOtherBook)
Worksheets("Teams").Unprotect
Selection.Copy .Worksheets(1).Range("F1")
Names.Add Name:="team", _
RefersToR1C1:="=Teams!R1C1:R1C18"
Range("A1:R19").CreateNames Top:=True
Names.Add Name:="look_up", _
RefersToR1C1:="=Teams!R164C1:R262C5"
Sheets("Resources").Select
With .Range("B6").Validation
Delete
Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:= _
xlBetween, Formula1:="=team"
ShowInput = True
ShowError = True
End With
Range("B6").AutoFill Destination:=.Range("B6:B102"),
Type:=xlFillDefault
Range("T6").FormulaR1C1 = "=VLOOKUP(RC3,look_up,2,FALSE)"
Range("T6").AutoFill Destination:=.Range("T6:W6"),
Type:=xlFillDefault
Range("T6:W6").AutoFill Destination:=.Range("T6:W102"),
Type:=xlFillDefault
Range("C102").ClearContents
Range("B102").ClearContents
Sheets("Teams").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
End Sub

Cheers,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=504822