Copying data from one sheet to the next

[LEG_END]

I am trying to run a report. Basically I use an active x control to initialise a sequence of looking through column B in "MASTER" and creating new sheets for each value that is in column B. This part works fine. What I want to do is for each line i want to copy the data in MASTER to its relevant sheet as per its designation in column B.

This is my code so far it runs but doesn't paste any information:

Sub CreateSheetsFromAList()Dim MyCell As Range
Dim MyRange1 As Range
Dim MyRange As Range
Dim WasVisible As Boolean
Dim wsTEMP As Worksheet
Dim wsMASTER As Worksheet
Dim ws As Worksheet
Dim wsRng As Range
Dim i As Long
Dim NextRow As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim LastRow As Long


Application.ScreenUpdating = False                          'Speeding up Macro
Application.DisplayAlerts = False                           'Turning off alerts


For Each ws In Sheets
     If ws.Name <> "MASTER" And ws.Name <> "Template" Then
            ws.Delete
     End If
Next


Application.DisplayAlerts = True


Set wsTEMP = Sheets("Template")                             'Sheet to be copied
WasVisible = (wsTEMP.Visible = xlSheetVisible)              'Checking if sheet is visible
If Not WasVisible Then wsTEMP.Visible = xlSheetVisible      'If NOT visible making the sheet visible


Set wsMASTER = Sheets("MASTER")
Set MyRange = Sheets("MASTER").Range("B3")
Set MyRange1 = Range(MyRange, MyRange.End(xlDown))


    For Each MyCell In MyRange1
            If CreateSheetIf(MyCell.Value) Then             'creates a new worksheet
                wsTEMP.Copy Before:=Sheets(wsMASTER)
                Sheets(Sheets.Count).Name = MyCell.Value    'renames the new worksheet
            End If
            
        wsTEMP.Select
        Rows("1:2").Select
        Selection.Copy
        Sheets(MyCell.Value).Select
        ActiveSheet.Paste
        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
            
        Sheets(MyCell.Value).Activate
                With ActiveSheet
                    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                End With
        
                For i = 1 To LastRow
                    If Sheets("MASTER").Cells(i, 1).Value = MyCell.Value Then
                        ActiveSheet.Row.Value.Copy _
                        Destination:=Sheets(MyCell.Value)
                    End If
                Next i
             
    Next MyCell


wsMASTER.Activate                                           'Returning to main sheet
If Not WasVisible Then wsTEMP.Visible = xlSheetHidden       'Making template invisible
Application.ScreenUpdating = True                           'Returning Screen updating


End Sub
Function CreateSheetIf(strSheetName As String) As Boolean
Dim wsTest As Worksheet
CreateSheetIf = False


Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    CreateSheetIf = False
    Worksheets.Add.Name = strSheetName
End If


End Function

Any ideas on this would be greatly appreciated

 

[Fluff]

How about

Sub CreateSheetsFromAList()
   Dim Cl As Range
   Dim WasVisible As Boolean
   Dim wsTEMP As Worksheet, wsMASTER As Worksheet, ws As Worksheet
   
   Set wsTEMP = Sheets("Template")                             'Sheet to be copied
   Set wsMASTER = Sheets("MASTER")
   WasVisible = (wsTEMP.Visible = xlSheetVisible)              'Checking if sheet is visible
   If Not WasVisible Then wsTEMP.Visible = xlSheetVisible      'If NOT visible making the sheet visible

   Application.ScreenUpdating = False                          'Speeding up Macro
   Application.DisplayAlerts = False                           'Turning off alerts
   
   For Each ws In Sheets
      If ws.Name <> "MASTER" And ws.Name <> "Template" Then
         ws.Delete
      End If
   Next
   Application.DisplayAlerts = True
   
   If wsMASTER.AutoFilterMode Then wsMASTER.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In wsMASTER.Range("B3", wsMASTER.Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            wsTEMP.Copy Sheets(wsMASTER)
            ActiveSheet.Name = Cl.Value
            wsMASTER.Range("A3:Z3").AutoFilter 2, Cl.Value
            ws.AutoFilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If
      Next Cl
   End With
   wsMASTER.AutoFilterMode = False
   
   wsMASTER.Activate                                           'Returning to main sheet
   If Not WasVisible Then wsTEMP.Visible = xlSheetHidden       'Making template invisible
End Sub

 

[LEG_END]

Thanks for the prompt response however, This now says that name already exists

[COLOR=#333333]ActiveSheet.Name = Cl.Value[/COLOR]

 

[Fluff]

Does column B contain either "Master" or "Template"?
Also noticed a couple of errors, use

         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            wsTEMP.Copy wsMASTER
            ActiveSheet.Name = Cl.Value
            wsMASTER.Range("A3:Z3").AutoFilter 2, Cl.Value
            wsMASTER.AutoFilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
         End If

 

[LEG_END]

I use column b to name my sheets as they are created during the report. MyCell.Value provides me this variable

 

[Fluff]

You haven't answered my question

 

[LEG_END]

No it doesnt contain either, MASTER and Template are my main sheet names

 

[Fluff]

In that case I don't understand how you get that message.
What sort of values do you have in col B?