cell linked to a sheetname created VBA

[roulietta2424]

Hi,

The first part of my program is about a solver that creates a new sheet with the same format everytime it runs so the user can keep a trace of different scenarios.

But now i have another sheet called "Data Comparison" and I want to define two cells where the user can add the name of the sheets/scenarios he want to compare. the goal is to calculate different ratios.

All I can do is use this Formula and I had to add manually for each cell.

Worksheets("Data Comparison").Range("C19").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(""'""& R[-8]C[-0] &""'""& ""!""&""C3"")"
Worksheets("Data Comparison").Range("D19").Select
ActiveCell.FormulaR1C1 = "=INDIRECT(""'""& R[-7]C[-1] &""'""& ""!""&""C3"")"

 

[konew1]

Why don't you make the DataComparison sheet use indirect function to extract the required data from the 2 sheets under comparison. put the sheet names of the 2 for comparison into cells on DataComparison. Say in A1 and A2, but it could be anywhere. Then either get the code that creates the new sheet to enter the newly created sheet name into A1 on DataComparison and call for the user to specify the sheet it is to be compared with, and put that in A2