Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a list of names and need one worksheet for each of the names. The "ANC" worksheet is what I would like to use as the "template". Is there an easy/fast way to create new worksheets in the same workbook using that original (ANC) worksheet and the names from the "loc" tab as the sheet name?
    I have attached the spreadsheet with the "loc" tab listing all the names for worksheets and the "ANC" sheet which is the one I want to copy.
    thanks
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts
    [quote name='CCJ' post='770721' date='15-Apr-2009 18:29']I have a list of names and need one worksheet for each of the names. The "ANC" worksheet is what I would like to use as the "template". Is there an easy/fast way to create new worksheets in the same workbook using that original (ANC) worksheet and the names from the "loc" tab as the sheet name?
    I have attached the spreadsheet with the "loc" tab listing all the names for worksheets and the "ANC" sheet which is the one I want to copy.
    thanks[/quote]

    Click on the tab of the "ANC" wksht. Then Ctrl-click to drag to the right (notice the small plus sign at the cursor?), to make a copy of it next to the orig. Select the two wkshts, by Shift-click; don't worry about wksht names yet. Ctrl-click to drag a copy of the two of them, and now there are four copies. Shift-click from the orig. to the fourth, and Ctrl-click to the right and now you have eight. They're multiplying like rabbits. Get the idea?

    Then, go back to each wksht and double-click on each wksht tab, editing the name of each.

    Hope that helps.

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    This Macro will copy the sheets for you.

    Code:
     Sub copyANCsheet()
     Dim rngName As Range
     
    	 With ActiveWorkbook
    		 For Each rngName In .Worksheets("loc").UsedRange
    			 If rngName.Value <> "ANC" Then
    				 .Worksheets("ANC").Copy after:=.Worksheets(.Worksheets.Count)
    				 .Worksheets(.Worksheets.Count).Name = rngName
    			 End If
    		 Next rngName
    	 End With
     End Sub

  4. #4
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='StuartR' post='770761' date='16-Apr-2009 01:39']This Macro will copy the sheets for you.

    Code:
     Sub copyANCsheet()
     Dim rngName As Range
     
    	 With ActiveWorkbook
    		 For Each rngName In .Worksheets("loc").UsedRange
    			 If rngName.Value <> "ANC" Then
    				 .Worksheets("ANC").Copy after:=.Worksheets(.Worksheets.Count)
    				 .Worksheets(.Worksheets.Count).Name = rngName
    			 End If
    		 Next rngName
    	 End With
     End Sub
    [/quote]

    Did the job perfectly.
    thanks much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •