-1

I am trying to reference a worksheet that is created when the user hits a macro button. Which in this case is named from an input cell (TI Summary B4, and in this case ends up being "7525"). I would like to reference this sheet as a variable, because the input cell name will change.

Sheets.Add After:=ActiveSheet
ActiveSheet.Select
ActiveSheet.Name = Sheets("TI Summary").Range("B4")
Sheets("TI TEMPLATE PAGE").Select
Cells.Select
Selection.Copy
Sheets("7525").Select
Range("A1").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='TI Summary'!R[1]C"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='TI Summary'!R[1]C"
Range("D3").Select
ActiveCell.FormulaR1C1 = "='TI Summary'!R[1]C"
Range("D4").Select
Sheets("TI Summary").Select
BigBen
  • 46,229
  • 7
  • 24
  • 40
rdmgambrell
  • 1
  • 2
    `Dim ws As Worksheet`, `Set ws = Worksheets.Add(After:=ActiveSheet)`. Then use `ws`. – BigBen Aug 16 '21 at 18:01
  • If I define and set ws at the top and then change Sheets("7525").Select to ws.select it creates a sheet with the name from cell B4 but then creates another sheet and pastes the info there instead – rdmgambrell Aug 16 '21 at 18:06
  • Change `Sheets.Add After:=ActiveSheet` to `Set ws = Worksheets.Add(After:=ActiveSheet)` – BigBen Aug 16 '21 at 18:07
  • 1
    Also, if you're copying the whole sheet, you may as well do `Sheets.Copy` instead of `Sheets.Add` – Toddleson Aug 16 '21 at 18:07
  • 1
    Also see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Aug 16 '21 at 18:07

1 Answers1

2

Without the select/activate:

Dim wb As Workbook, ws As Worksheet, wsDest As Worksheet

Set wb = ThisWorkbook 'or ActiveWorkbook

Set ws = wb.Worksheets.Add(After:=ActiveSheet) 'get a reference to the new sheet...
ws.Name = wb.Sheets("TI Summary").Range("B4").Value

Set wsDest = wb.Sheets("7525")

wb.Sheets("TI TEMPLATE PAGE").Cells.Copy wsDest.Range("A1")

wsDest.Range("B3:D3").FormulaR1C1 = "='TI Summary'!R[1]C"

wb.Sheets("TI Summary").Select

If you want to reference the just-added sheet in a formula:

  .FormulaR1C1 = "='" & ws.Name & "'!R[35]C[-2]"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125