Renaming worksheet - increment by 1

[B-radK]

Hi team,

I have a workbook that opens another workbook, copies the cells I require, and pastes them into a new worksheet.

This is the code I have already:

Sub AddSheet()
Application.GetOpenFilename
Cells.Select
Selection.Copy
ThisWorkbook.Activate
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Week"
Cells.Select
ActiveSheet.Paste
Range("A1:I1").Select
ActiveWindow.DisplayGridlines = False
End Sub

Once this has macro has been executed, I manually rename the worksheet to say "Week 10" for example. The previous sheet names are "Week 1", "Week 2", "Week 3", etc...

Could you please help me to amend my code so it renames the tab by an increment of 1?

And could also help me to close the worksheet where I copied the data from and not save any changes to it?

Any help would be greatly appreciated.
Thanks team.

Cheers
Brad.

 

[dave3009]

Maybe just add the sheets.count minus the know fixed sheets to the macro. So if you had 3 fixed sheets you'd use....

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Week " & Sheets.Count - 3

 

[Asator]

.Name = "Week " & Worksheets.Count + 1

 

[B-radK]

Hi Dave and Asator,

Thank you very much for your prompt replies.
Asator, I used your code:

.Name = "Week " & Worksheets.Count + 1

I have a worksheet at the very front of the book named "Summary". When the code executes, the worksheet after worksheet "Week 10" is renamed to "Week 12", not "Week 11". This is obviously because the first sheet is "Summary" then the next sheet starts from "Week 1". Do you have any idea as to remedy this?

Thanks again for your help.
Cheers
Brad.

 

[Asator]

Just get rid of the + 1.

I was going under the assumption that you just had sheets for the various weeks.

Sheets.Count just counts the total sheets in the book BEFORE the new one is added. If you had 3 summary sheets, you could do worksheets.count - 2. (- 3 existing + 1 new one).

 

[B-radK]

Perfect!

Thank you very much mate.

Cheers
Brad.

 

[B-radK]

Asator,

If you're still there, do you know how to add code to the previous macro to select the worksheet name and copy it into Range("B25")?
I know how to do this using a formula, but I'm having trouble with the macro.

Thanks again mate.
Brad.

 

[Asator]

B25 on which sheet?

 

[B-radK]

Sorry Asator...

Once the Macro you helped me with is executed, I would like the name of the worksheet i.e. Week 12 to be copied and pasted into cell B25 of the same sheet.

Does that make sense?

Thanks again
Brad.