insert values from one sheet into anther

Question

I have data in one sheet called "Data Storage". I want to display parts of the data in another sheet. The first part of the code finds the data based on input from the user i.e. RPName = Range ("Role Play") The next part identifies a range based on "Role Play" that are a series of numbers that will be averaged.

I get an error on this line:

Sheets("Data Storage").Range(Cells(RPName_rng1, 5), Cells(RPName_tot, 5)).Name = "Com1avg"

I can't figure out why that is causing an error.

' average Macro
Dim RPName As String, RPName_rng1 As Integer, RPName_rng2 As Integer, RPName_tot As Integer
'get value from Role_Play cell
RPName = Range("Role_Play")
'    MsgBox ("Variable RPNam is selected in Macro average" & RPName)

' Find first instance of RPName
RPName_rng1 = Sheets("Data Storage").Cells.Find(What:=RPName, After:=Cells(1, 1), SearchOrder:=xlByRows).Row
'    MsgBox RPName_rng1

'Count number of RPname
RPName_rng2 = Application.WorksheetFunction.CountIf(Sheets("Data Storage").Range("A:A"), RPName)
'    MsgBox RPName_rng2
RPName_tot = RPName_rng1 + RPName_rng2 - 1
'    MsgBox RPName_tot

'Name range here
Sheets("Data Storage").Range(Cells(RPName_rng1, 5), Cells(RPName_tot, 5)).Name = "Com1avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 7), Cells(RPName_tot, 7)).Name = "Com2avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 9), Cells(RPName_tot, 9)).Name = "Com3avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 11), Cells(RPName_tot, 11)).Name = "Com4avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 13), Cells(RPName_tot, 13)).Name = "Com5avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 15), Cells(RPName_tot, 15)).Name = "Com6avg"
Sheets("Data Storage").Range(Cells(RPName_rng1, 17), Cells(RPName_tot, 17)).Name = "Com7avg"


'    Range("RPData").Select
    Application.CutCopyMode = False
    Range("CA_Com1").FormulaR1C1 = "=AVERAGE(Com1avg)"
    Range("CA_Com2").FormulaR1C1 = "=AVERAGE(Com2avg)"
    Range("CA_Com3").FormulaR1C1 = "=AVERAGE(Com3avg)"
    Range("CA_Com4").FormulaR1C1 = "=AVERAGE(Com4avg)"
    Range("CA_Com5").FormulaR1C1 = "=AVERAGE(Com5avg)"
    Range("CA_Com6").FormulaR1C1 = "=AVERAGE(Com6avg)"
    Range("CA_Com7").FormulaR1C1 = "=AVERAGE(Com7avg)"
'    Sheets("Data View").Activate
End Sub

Answer 1

You need to fully qualify the location of your Cells, otherwise it will default to the ActiveSheet. This becomes much easier (and more readable) using a With block.

With Sheets("Data Storage")
    .Range(.Cells(RPName_rng1, 5), .Cells(RPName_tot, 5)).Name = "Com1avg"
    ...
End With

Note how .Cells is used in the With block. When this is done, the Cells range will be with respect to the sheet defined in the With statement.

If you want to do the same without a With block, your call would look like this:

Sheets("Data Storage").Range(Sheets("Data Storage").Cells(RPName_rng1, 5), Sheets("Data Storage").Cells(RPName_tot, 5)).Name = "Com1avg"