using named range in VBA

  • Thread starter Thread starter Atiq
  • Start date Start date
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
 
I don't know if this is your problem, but are your range names global or
sheet specific?
 
I don't know if this is your problem, but are your range names global or
sheet specific?
 
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)
 
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)
 
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
 
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
 
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'
 
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'
 
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
 
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
 
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!
 
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!
 
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
 
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
 
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
Run-time error 1004 paste method of worksheet class failed 3
Automatically create new sheets... 1
VBA to fix a Dim 2
Paste to array of sheets 2
Copying data to the next blank row 0
Copy Sheet and rename it. 11
Excel VBA - Problem with Closing Print Preview 0

Back
Top