I want this statement to select the named range from data sheet which is same
as newly created sheet name. I tried it with "Sheets("data").Range
(ThisDept).Select" and still getting run time error '1004'

"Sheeloo" wrote:

Following statement is not doing anything... what do you expect it to do?
Sheets("data").Range (ThisDept)

Did you mean to do the following
Sheets("data").Range (ThisDept).Select
"Atiq" wrote:

I removed the quotes, still get the run time error.

"Sheeloo" wrote:

If you are using ThisDept to store the range name then do not use quotes
around it...
Sheets("data").Range ("ThisDept")
tries to find the rande ThisDept

Try
Sheets("data").Range (ThisDept)

"Atiq" wrote:

I am trying to copy sheets using a template, and in each sheet I have to fill
the department names. I have saved department names in a sheet called "data"
and named range for each Department. My code below works as far as creating
new sheets. But gives me a run time error as I mention below.

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
Sheets("data").Range ("ThisDept") <<< Here I get run time error my
named range is same as the sheet name, and I am trying to use that to call
the range.

Selection.Copy
Sheets(LastSheet).Select
Range("V9").Select
ActiveSheet.Paste
Sheets(ThisDept).Select
Range("A1").Value = ThisDept

Next x
End Sub

Any help will be greatly appreciated.

Atiq