Macro copy to new sheet and copy that sheet after format cells

[ether]

I created a Marco to copy a worksheet named "Data" to a new worksheet called "Sept"

Then unmerged all the cells in "Sept", and copied it to a new worksheet named "Oct"

The problem I'm running into is that it only works on new workbooks.

If I use it on a workbook with data already in it, when I get here: Worksheets("Sept").Copy After:=Worksheets("Sept")

It says the 'Click_to_Select_Amount' already exist.

I don't have another worksheet named Oct, so I'm not sure what's going on.

Any ideas?

Sub Copy()
'   
    Set ws = ActiveSheet
    ActiveSheet.Name = "Data"
    ws.Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = "Sept"
    ActiveSheet.Cells.UnMerge
    Worksheets("Sept").Copy After:=Worksheets("Sept")
'
End Sub

 

[Alex Blakenburg]

I don't have another worksheet named Oct, so I'm not sure what's going on.

I am a bit confused, nowhere in the code do you mention Oct.

If you want to the newly created sheet to be called Oct then you need to add the line below to your code (same as you have already done for Sept)

    ActiveSheet.Name = "Oct"

As your code stands you won't be able to run it a 2nd time on the same workbook since at the sheet Sept would already exist on the 2nd run and if you add the above line so will Oct.

 

[ether]

I am a bit confused, nowhere in the code do you mention Oct.

If you want to the newly created sheet to be called Oct then you need to add the line below to your code (same as you have already done for Sept)

    ActiveSheet.Name = "Oct"

As your code stands you won't be able to run it a 2nd time on the same workbook since at the sheet Sept would already exist on the 2nd run and if you add the above line so will Oct.

How would I write the code, so I don't have this problem?

Thanks for your help!

 

[Alex Blakenburg]

You need to provide more details on what you are trying to do, especially when you run it repeatedly.
What do you expect to happen the 2nd time you run it and then the 3rd time etc.

If you are adding a month each time are you starting with the sheet Data and have to do the unmerge each time ?
Do you want an input box to ask the user for the new month name ?