| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#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 |
| Reply |
| 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) | |||
Linear Mode
