0

I have a Macro button on a sheet (Line Item Summary) that creates a copy of Costing Sheet as a new worksheet. After the macro is done the active sheet is Line Item Summary but I would like it to be the sheet that was created. Here is the macro for the button:

Sub NewSheet()

Call Sheet7.NewLine

Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")
ActiveSheet.Range("B6:F68").Value = ActiveSheet.Range("B6:F68").Value

End Sub

I was looking into using "Application or Activate" as a way to do this but being fairly new to VBA I'm not sure how or if there's a better way to execute this. Any help is appreciated!

icalderon
  • 53
  • 2
  • 13
  • In general, you want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad Nov 29 '19 at 15:25
  • Try `Worksheets(Worksheets("Costing Sheet").Index+1).Activate` right after `Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")` – Foxfire And Burns And Burns Nov 29 '19 at 15:27
  • What is this supposed to do: `ActiveSheet.Range("B6:F68").Value = ActiveSheet.Range("B6:F68").Value`? *Formulas > Data*? If so, the point above ^ is very valid, good practice is to reference the sheet by (code)name. Therefor, you won't need it to be the *ActiveSheet* – JvdV Nov 29 '19 at 15:27
  • Anyways, you can interact with the range of another worksheet doing something like `Worksheets("nameofthatworksheet").Range("rangeAddress"). You don't need it to be the ActiveSheet – Foxfire And Burns And Burns Nov 29 '19 at 15:28
  • @FoxfireAndBurnsAndBurns adding the extra line you initially mentioned worked great! Thank you for the feedback – icalderon Nov 29 '19 at 15:33
  • @cybernetic.nomad thank you for the feedback, I will keep this in mind for future reference – icalderon Nov 29 '19 at 15:35
  • @JvdV thank you for the input. To answer your question, the `ActiveSheet` line of code is used after the new sheet is created to copy and paste range B6:F68 as values only. – icalderon Nov 29 '19 at 15:37

1 Answers1

1

Try

Worksheets(Worksheets("Costing Sheet").Index+1).Activate

right after line

Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")

Worksheets can be referenced by their name, codename or index. In your case, line Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet") is copying the worksheet Costing Sheet right after itself.

So in my code, the part that says Worksheets("Costing Sheet").Index will return the index of that worksheet, and we sum up 1 to get right the next one.

UPDATE: As I said in comments, sometimes you don't need a worksheet to be the active one to make some operations. In your case, because you want to convert formulas into values, you can do it with a single line. Your final code can be:

Sub NewSheet()

Call Sheet7.NewLine

Worksheets("Costing Sheet").Copy After:=Worksheets("Costing Sheet")
With Worksheets(Worksheets("Costing Sheet").Index+1)
    .Range("B6:F68").Value = .Range("B6:F68").Value
End With


End Sub
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38