Trouble editing code to copy sheet instead of making a blank one

[YenteDS]

Hey guys,

I'm working on a sheet where I can use a macro to generate new sheets and rename them to names in a list. I have managed to do this. Yet I want to edit the code to instead of creating a blank sheet. The macro has to copy the sheet named "Blanco" and rename that with the predefined list. The code so far is as followed:

[COLOR=#011993][FONT=Menlo]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] nieuwesheets()[/FONT][/COLOR][FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] ws [COLOR=#011993]As[/COLOR] Excel.Worksheet[/FONT][/COLOR]

[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] MyCell [COLOR=#011993]As[/COLOR] Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Dim[/COLOR] MyRange         [COLOR=#011993]As[/COLOR] Range[/FONT][/COLOR]

[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Set[/COLOR] MyRange = Sheets("Abonnees").Range("A2")[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Set[/COLOR] MyRange = Range(MyRange, MyRange.End(xlDown))[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]'// Ignore any errors for the moment[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]OnErrorResumeNext[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Loop each used cell in range[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]For[/COLOR] [COLOR=#011993]Each[/COLOR] MyCell [COLOR=#011993]In[/COLOR] MyRange[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Try and refer to worksheet named in cell[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]      [COLOR=#011993]Set[/COLOR] ws = Sheets(MyCell.Value)[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// If the worksheet does not exist then this will be Nothing[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]If[COLOR=#000000] ws [/COLOR]IsNothingThen[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Add a new worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]         [COLOR=#011993]Set[/COLOR] ws = Worksheets.Add[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Move to the end and rename[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]         [COLOR=#011993]With[/COLOR] ws[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .Move After:=Sheets(ActiveWorkbook.Sheets.Count)[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Clear any errors before attempting to rename[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            Err.Clear[/FONT][/COLOR]
[COLOR=#008F00][FONT=Menlo]'// Rename the worksheet[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            .Name = MyCell.Value[/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Check that was successful[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            [COLOR=#011993]If[/COLOR] Err.Number <> 0 [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]               MsgBox "The worksheet " & ws.Name & " could not be renamed to " & MyCell.Value & "." & _[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]                        vbCrLf & vbCrLf & "Possibly because of illegal characters or more than 31 characters in the name.", vbExclamation, "Error"[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]               Err.Clear[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]            [COLOR=#011993]End[/COLOR] [COLOR=#011993]If[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]         [COLOR=#011993]End[/COLOR] [COLOR=#011993]With[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]      [COLOR=#011993]End[/COLOR] [COLOR=#011993]If[/COLOR][/FONT][/COLOR]

[COLOR=#008F00][FONT=Menlo]'// Clear ws to ensure the attempt to refer to the next worksheet succeeds[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]      [COLOR=#011993]Set[/COLOR] ws = [COLOR=#011993]Nothing[/COLOR][/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo]   [COLOR=#011993]Next[/COLOR] MyCell[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]

If anyone could help me out here? Thanks in advance!

 

[Norie]

Change this,

 Set ws = Worksheets.Add

to this.

 Set ws = Worksheets("Blanco")

 

[YenteDS]

Hmm.. I tried that and now when i run the macro nothing happens anymore.

 

[Norie]

What happens if you remove this?

On Error Resume Next

 

[YenteDS]

i get this error:

Error 9 during runtime:
The subscript is out of range

 

[Norie]

That suggests there isn't a sheet with the exact name 'Blanco' in the workbook that is active when you run the code.

Have you checked that?

 

[YenteDS]

Yeah i checked that.. The sheet "Blanco" contains a table with data so i don't get it why it says that.. :/

 

[YenteDS]

when i try finding out the error it highlights this as the cause:

[COLOR=#000000][FONT=Menlo] 
[/FONT][/COLOR][COLOR=#008F00][FONT=Menlo]'// Try and refer to worksheet named in cell[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]      Set ws = Sheets(MyCell.Value)[/FONT][/COLOR]

 

[Norie]

Are you trying to create sheets and name them using the values from MyRange or do those sheets already exist?

If it's the former, ie create the sheets, you'll need to get rid of this part of the code as it's trying to refer to nonexistent sheets.

  Set ws = Sheets(MyCell.Value)