VBA Code to Include new Worksheets

[wootenka]

My company works on jobs with multiple stages and we have to bid each stage seperatly and then total up all of the stages. We are trying to simplyfy this experience for our sales team using VBA and macros.

I have a macro recorded in an estimate writing workbook that copies the revenue estimate worksheet and the cost estimate worksheet, then inserts cells on a summary data page that feeds into a sumif formula to give us a snapshot summary of all the stages in the job. The problem that I cant get past is that when I run the macro, it always refers to the worksheet name that I touched when I recorded it. I need to be able to make this go to the newly added worksheet each time that the macro is run.

Any thoughts?

Here is the macro:
Sub New_Stg()
'
' New_Stg Macro
' Adds a new stage
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Est-Mob").Select
Sheets("Est-Mob").Copy Before:=Sheets(7)
Sheets("Cost-Mob").Select
Sheets("Cost-Mob").Copy Before:=Sheets(8)
Sheets("Job Stats Data").Select
Rows("2:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6:A9").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Sheets("Est-Mob (2)").Select
Range("M67").Select
Sheets("Job Stats Data").Select
Range("B2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=+'Est-Mob (2)'!R[65]C[11]"
Range("B3").Select
Sheets("Job Stats Data").Select
ActiveCell.FormulaR1C1 = "=+'Cost-Mob (2)'!R[29]C[11]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=+'Cost-Mob (2)'!R[40]C[11]"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=+'Cost-Mob (2)'!R[60]C[11]"
Range("B6").Select
End Sub

 

[bertie]

Code generated by the macro recorder generally needs to be tidied up.

You don't need to select a Sheet/cell before using it.
For example, this:

Sheets("Est-Mob").Select
Sheets("Est-Mob").Copy Before:=Sheets(7)

Can be cut down to:

  Sheets("Est-Mob").Copy Before:=Sheets(7)

And you don't want things like ActiveSheet or ActiveCell. Instead specifically refer to the worksheets and cells you are working with.

I have tidied up your code; hopefully this will give you a clearer insight into what is going wrong.

[COLOR=darkblue]Sub[/COLOR] New_Stg()
[COLOR=green]'[/COLOR]
' New_Stg Macro
[COLOR=green]' Adds a new stage[/COLOR]
[COLOR=green]'[/COLOR]
' Keyboard Shortcut: Ctrl+a
[COLOR=green]'[/COLOR]
  Sheets("Est-Mob").Copy Before:=Sheets(7)
  Sheets("Cost-Mob").Copy Before:=Sheets(8)
 
  [COLOR=darkblue]With[/COLOR] Sheets("Job Stats Data")
    .Rows("2:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A6:A9").Copy
    .Range("A2").PasteSpecial
    .Range("B2").FormulaR1C1 = "=+'Est-Mob (2)'!R[65]C[11]"
    .Range("B3").FormulaR1C1 = "=+'Cost-Mob (2)'!R[29]C[11]"
    .Range("B4").FormulaR1C1 = "=+'Cost-Mob (2)'!R[40]C[11]"
    .Range("B5").FormulaR1C1 = "=+'Cost-Mob (2)'!R[60]C[11]"
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
  Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]