using named range in VBA

[Atiq]

Perfect! it worked! Thanks you very much!

Use the macro given below
I have added the line
Sheets("data").Activate
before
Sheets("data").Range(ThisDept).Select

Also you should use
Sheets(LastSheet + 1).Select
instead of
Sheets(LastSheet).Select
I have not updated the above in the code...

'Start macro
Public Sub GasDist()


Sheets("Level").Select
' Determine how many Departments are on Data sheet
FinalRow = Range("A65000").End(xlUp).Row
' Loop through each department on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("Level").Select
ThisDept = Range("A" & x).Value
' Make a copy of template sheet and move to end
Sheets("Temp").Copy After:=Sheets(LastSheet)
' rename the sheet and set A1 = to the department name
Sheets(LastSheet + 1).Name = ThisDept
'Added the following line
Sheets("data").Activate
Sheets("data").Range(ThisDept).Select


Selection.Copy
'Following line should have LastSheet + 1
Sheets(LastSheet).Select
Range("V9").Select
ActiveSheet.Paste
Sheets(ThisDept).Select
Range("A1").Value = ThisDept

Next x
End Sub
'End macro