Maybe:
Sub test()
Dim wsHome As Worksheet
Dim wsR As Worksheet
Dim R As String
'this is the reference to the sheet name. Currently ("Summary 1") in the workbook
Set wsHome = Sheets("Home")
R = wsHome.Range("H1").Value
Set wsR = Sheets(R)
If 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
wsR.Range("C7:J42").ClearContents
wsHome.Range(Range("G8:J8"), wsHome.Range("G8:J8").End(xlDown)).Copy
wsR.Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsHome.Range(wsHome.Range("M8"), wsHome.Range("M8").End(xlDown)).Copy
End If
End Sub
Or even:
Sub test()
Dim wsHome As Worksheet
Dim wsR As Worksheet
Dim R As String
'this is the reference to the sheet name. Currently ("Summary 1") in the workbook
Set wsHome = Sheets("Home")
R = wsHome.Range("H1").Value
Set wsR = Sheets(R)
If ActiveSheet.Name = "Home" Then
wsR.Range("C7:J42").ClearContents
wsHome.Range("G8:J" & wsHome.Range("G" & Rows.Count).End(xlUp).Row).Copy
wsR.Range("C7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsHome.Range("M8:M" & wsHome.Range("M" & Rows.Count).End(xlUp).Row).Copy
' something else...
End If
End Sub
Bookmarks