Selecting the next sheet and copying the previous

[radka.silva]

Hi, I am trying to create a macro to create a new worksheet, copy the previous one, past it into the new one and update the name and date. I recorded the macro and this is what I got:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("2-24").Select
Cells.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A5:H379").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A6").Select

Here are some problems with it:

1. It references the name of the previous sheet (sheets("2-24")) and my names will change, I just need it to always select the previous sheet no matter the name.
2. When pasting the info from the previous sheet, it wants to go to Sheet2, but again, I will be using this in variety of spreadsheets and the newly created tab will not always be Sheet2 - is there anyway to reference the NEW tab that was just created?
3. Once new sheet is created and the info copied from the previous sheet, I need to update the date - I have not recorded this part. In cell B2 will be today's date and B3 previous date.
4. Can the macro name my new sheet? the name would the the same date as in cell B3 but in a different format. If the date is 2/25/15, the name of the tab should be 2-25.

I am using Excell 2010.

Thanks for any suggestions.

Radka

[p45cal]

maybe:

Sub blah()
Set oldsht = ActiveSheet
oldsht.Copy After:=Sheets(Sheets.Count)
With ActiveSheet
  .Range("A5:H379").ClearContents
  .Range("B2").Value = Date
  oldsht.Range("B2").Copy .Range("B3")
  .Name = Format(.Range("B3").Value, "m-d")
End With
End Sub

[radka.silva]

Wow, this is perfect. Thank you p45cal!!! I know this is probably totally trivial code to you, but it will make my life so much easier. Thank you, thank you, thank you.

[Yongle]

Radka.silva
This thread looks as if it is solved
If it is please use thread tools and mark as SOLVED

[Atifraza]

Does anyone know of code that will automate the creation of a photo album and then copy the slides (excluding the title slide) to new slides in the current presentation?

If not, anyone interested in writing it for me? Your help will be greatly appreciated! :-)

DJ

[mancubus]

post your question in PowerPoint help forum.
http://www.vbaexpress.com/forum/foru...owerPoint-Help

click "+ Post New Thread" button to start a new thread. it is in the upper left corner of the page.