help me revised this vba. copy sheet macro

[Dan88]

hi everyone. I am currently running this macro below that lets me copy a sheet named template to the end and rename it based on a cell value.

Sub End()
Dim ws As Worksheet, wb As Workbook
Dim rngData As Range

Set wb = ActiveWorkbook

With wb.Sheets("Template")
Set rngData = Range(.Range("J2"), .Range("J2").End(xlDown))
End With

For Each cell In rngData
If cell.Value <> "" Then
On Error Resume Next

Set ws = Worksheets(cell.Value)
wb.Sheets("Template").Copy after:=wb.Sheets(wb.Sheets.Count)
wb.Sheets(wb.Sheets.Count).Name = cell.Value
Else
Set ws = Nothing
End If

On Error GoTo 0
End If
Next cell
End If
End Sub


is there something im missing? the code just wouldn't work. the value in J1 is a date.

thanks in advance.

 

[Andrew Poulsom]

Try:

Sub copy()
    Dim ws As Worksheet, wb As Workbook
    Dim rngData As Range
    Dim cell As Range
    Set wb = ActiveWorkbook
    With wb.Sheets("main")
        Set rngData = Range(.Range("J2"), .Range("J2").End(xlDown))
    End With
    For Each cell In rngData
        If cell.Value <> "" Then
            On Error Resume Next
            Set ws = Worksheets(Format(cell.Value, "MMM-yy"))
            If ws Is Nothing Then
                wb.Sheets("main").copy after:=wb.Sheets(wb.Sheets.Count)
                wb.Sheets(wb.Sheets.Count).Name = Format(cell.Value, "MMM-yy")
            Else
                Set ws = Nothing
            End If
        End If
    Next cell
End Sub

 

[Dan88]

Andrew Poulsom, this is still copying 2 sheets? what giveS?
i spoke to fast, texasalynn code didnt work...

Thanks for your time.

 

[Andrew Poulsom]

Andrew Poulsom, this is still copying 2 sheets? what giveS?
i spoke to fast, texasalynn code didnt work...

Thanks for your time.

I don't know what gives. To test my code I entered 1 Jan 2009, I Feb 2009 and 1 Mar 2009 (as serial dates) in J2:J5. The code created 3 sheets, Jan-09, Feb-09 and Mar-09 (if they didn't already exist).

What do you have in J2 onwards?

 

[Dan88]

oh. I only need the code to generate one sheet for the month.
J2 onwards are blank.
So next month, i will enter April in J2 and the bba will create a copy of the "main" and rename it april based on value in J2.

No need for multiple sheet like you tested....
hope this is clear.

Thanks

 

[Andrew Poulsom]

With 1 Jan 2009 in J2 I only get one sheet named Jan-09 (if it didn't already exist). If you only want to use J2 why?

Set rngData = Range(.Range("J2"), .Range("J2").End(xlDown))

and the For Each ... Next loop?

 

[Dan88]

i was able to play around with the code and came up with this:

Sub Copy2End()
Dim ws As Worksheet, wb As Workbook
Dim rngData As Range

Set wb = ActiveWorkbook

With wb.Sheets("main")
Set rngData = Range(.Range("J2"), .Range("J2").End(xlDown))
End With
For Each cell In rngData
If cell.Value <> "" Then
On Error Resume Next

Set ws = Worksheets(cell.Value)
wb.Copy after:=wb.Sheets(wb.Sheets.Count)
wb.Sheets(wb.Sheets.Count).Name = Format(cell.Value, "MMM-yy")
Else
Set ws = Nothing
End If

On Error GoTo 0

Next cell
End Sub


The vba only adds one sheet now, however, as i want to add more it replaces the one that was jsut added.
ie. i add Mar09, the go back to main and put Apr09, the macro adds the new sheet but replaces the Mar09 sheet.

driving me insane.