using named range in VBA
 

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

using named range in VBA
 

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

"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

using named range in VBA
 

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

using named range in VBA
 

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

"Barb Reinhardt" wrote:

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

"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

using named range in VBA
 

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

using named range in VBA
 

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

using named range in VBA
 

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

using named range in VBA
 

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" wrote:

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

using named range in VBA
 

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" wrote:

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" wrote:

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

using named range in VBA
 

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" wrote:

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" wrote:

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" wrote:

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

using named range in VBA
 

Perfect! it worked! Thanks you very much!

"Sheeloo" wrote:

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" wrote:

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" wrote:

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" wrote:

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