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!
Bookmarks