Creating a New Sheet with Macros

[mjhopler]

I am working in 2007 and trying to build a macro that will create a new sheet, go back to my original sheet, copy data and paste it on the new sheet. The problem is, that in the macro, it creates a new sheet named “Sheet #”, but when I try to rerun the macro, the new Sheet is named “Sheet #+1”. Therefore, when the macro tries to select the sheet, due to the wrong name, it stops and returns an error message. How do I create a macro that will create a new sheet and then be able to select that sheet to paste info.

Big Picture, I am trying to create the ability to enter new financials every quarter and keep the old financials. Since the worksheets require quite a bit of formulas, I want a macro that will automatically carry over the formulas. Each quarter, I want to be able to hit a button and it add a new sheet, enter the formulas and formatting so it is ready for me to work on. Please help

Thanks,

[JimmyA]

Hi there

This might help you on your way. My VBA is not all that great but it seems to allow you to flick between sheets and the newly added sheet.

Please Login or Register  to view this content.

Hope this helps

Jim

As an adition, it could be worth thinking about having a blank sheet that can just be copied and re-named each time its required.

[mjhopler]

I am not very good with VBA. I created my macro by using the record button. Below is what the record button used. What changes should be made?

I am coping data from “Office Template Prop 61968” to a new worksheet.

Sub YE_New_Record()
'
' YE_New_Record Macro
'

'
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Office Template Prop 61968").Select
Columns("A:R").Select
Selection.Copy
Sheets("Sheet13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Office Template Prop 61968").Select
Application.CutCopyMode = False
Range("A1").Select
Sheets("Sheet13").Select
End Sub

[mjhopler]

Looking at your language, below are the changes I made, but it stops at: Sheets ("NewW").Select

Sub YE_New_Record()
'
' YE_New_Record Macro
'

'
Worksheets.Add
NewW = ActiveSheet.Name
Sheets("Office Template Prop 61968").Select
Columns("A:R").Select
Selection.Copy
Sheets("NewW").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Office Template Prop 61968").Select
Application.CutCopyMode = False
Range("A1").Select
Sheets("NewW").Select
End Sub

[JimmyA]

Hi

You need to put this bit at the top the line after

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hope this helps


P.S. If you put code in a post you are meant to put code tags round it (so it appears in a box). you do tha by highlighting the code and selecting the "#" icon in the menu. Just thought I would meniton it as i got told off for it it once.