using named range in VBA

[Atiq]

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

 

[Barb Reinhardt]

I don't know if this is your problem, but are your range names global or
sheet specific?

 

[Sheeloo]

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]

How can I tell if it is global or sheet specific?

 

[Atiq]

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

 

[Sheeloo]

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]

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]

Pl. paste the names you have defined in the post
or send the workbook to me.

Insert->Name->Paste->PasteList

The code expects a range defined for each Dept

 

[Atiq]

These are the sheet names that are created in the code taken from sheet name
"Level"

Summary
Exec
Ops_Construct
Network_Strategy
Dist_Support
Finance

and below are the named range

Dist_Support =data!$E$1:$E$5
Exec =data!$B$1:$B$3
Finance =data!$F$1
Network_Strategy =data!$D$1:$D$5
Ops_Construct =data!$C$1:$C$6
Summary =data!$A$1:$A$5


Thanks for your help!

 

[Sheeloo]

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

 

[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