Hello,

I need some pointers about how to correctly reference a sheet name based from a cell value. I currently have two sheets for example: 1. "Home" 2. "Summary 1".

On "Home" in cell H1 will be the name of the "Summary" tab (cell H1: "Summary 1"). I am trying to use VBA to reference that text and use it for a dynamic sheet name in the code.


Dim R As String

'this is the reference to the sheet name. Currently ("Summary 1") in the workbook
R = Sheets("Home").Range("H1").Text
   
If Application.ActiveSheet.Name = "Home" Then

'this is where it starts to have issues. I am trying to have "R" be the sheet name referenced from cell H1 above. This should essentially read as Sheets("Summary 1").Select
    Sheets("R").Select
        Range("C7:J42").Select
        Selection.ClearContents
    Sheets("Home").Select
        Range("G8:J8").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    Sheets("R").Select
        Range("C7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Home").Select
        Range("M8").Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        Selection.Copy
In the live workbook there are 8 sheets (Summary 1, Summary 2, Summary 3, etc). The above is just modified from a macro recorder so if there is a better looping formula I am interested to see how that works. Or just how to dynamically reference a sheet name per the above.

Thank you for any thoughts or suggestions.

Thank you for reading!