radka.silva
02-25-2015, 10:22 AM
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
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