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
|