| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Threaded Mode
