using named range in VBA

A

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
 
B

Barb Reinhardt

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

Barb Reinhardt

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

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)
 
S

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)
 
S

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
 
S

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
 
A

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'
 
A

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'
 
S

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
 
S

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
 
A

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!
 
A

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!
 
S

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
 
S

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
 
A

Atiq

Perfect! it worked! Thanks you very much!

Sheeloo said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use a range value in place of sheet name 1
Automatically create new sheets... 1
VBA in shared workbook 5
Named Range Macro 1
Copying data to the next blank row 0
VBA to fix a Dim 2
Paste to array of sheets 2
Copy Sheet to new Sheet and clear cells on original sheets 7

Top