How to select data on one sheet, make it part of formula on another

[Jeff Johnson]

Hi All,

My workbook has a sheet named "Plots" where data from all the other worksheets is summarized. On the Plots sheet in a given cell I want a formula that references another sheet (name is variable). Here's what I have so far:

Sub PopulatePlots()
'
' Gather data from all sheets, put it on "Plots" sheet
'
'
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "Plots" And sht.Name <> "Template" Then
    Sheets("Plots").Select
    Range("B3").Formula = "=MIN('CER0938A_11 140805'!R[8]C[14]:R[9]C[15])"
Next
End Sub

I just want to replace 'CER0938A_11 140805'! with a reference to each worksheet. This should be simple right? Thanks in advance.

 

[jim may]

maybe....

Sub PopulatePlots()
'
' Gather data from all sheets, put it on "Plots" sheet
'
'
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name <> "Plots" And sht.Name <> "Template" Then
    With Sheets("Plots")
    Range("B3").Formula = "=MIN(" & sht.Name & "!R[8]C[14]:R[9]C[15])"
    End With
    End If
Next
End Sub

 

[Jeff Johnson]

Thanks Jim. I used part of that to get to here:

Sub PopulatePlots()
'
' Gather data from all sheets, put it on "Plots" sheet
'
'
Dim Sourcesht, Plotsht As Worksheet
Dim ctr As Integer
Set Plotsht = Worksheets("Plots")
ctr = 3
For Each Sourcesht In ActiveWorkbook.Worksheets
    If Sourcesht.Name <> "Plots" And Sourcesht.Name <> "Template" Then
    'Plotsht.Range("A" & 3 + ctr) = sht.Name
    Plotsht.Range("B" & ctr).Formula = "=MIN('" & Sourcesht.Name & "'!p13:q14)"
    ctr = ctr + 1
    End If
Next
    
End Sub

I actually wanted to copy the formula into successive cells starting at B3. The above code does that. But now I want to add the sheet name to A3:A[whatever]. The commented line gives me error "Object Required" What am I doing wrong?

 

[Jeff Johnson]

I figured it out. Thanks for your help!

Sub PopulatePlots()
'
' Gather data from all sheets, put it on "Plots" sheet
'
'
Dim Sourcesht, Plotsht As Worksheet
Dim ctr As Integer
Set Plotsht = Worksheets("Plots")
ctr = 3 'starting row
For Each Sourcesht In ActiveWorkbook.Worksheets
    If Sourcesht.Name <> "Plots" And Sourcesht.Name <> "Template" Then
        With Sheets("Plots")
            .Range("A" & ctr).Formula = Sourcesht.Name
            .Range("B" & ctr).Formula = "=MIN('" & Sourcesht.Name & "'!p13:q14)"
            ctr = ctr + 1
        End With
    End If
Next
    
End Sub

 

[Smitty]

Welcome to the Board!

You're trying to reference a variable you don't have (sht).

Try:

Plotsht.Range("A" & ctr) = Sourcesht.Name

Also note that you need to explicitly declare your variables:

Dim Sourcesht As Worksheet, Plotsht As Worksheet

Otherwise Sourcesht will be assigned a Variant type.

HTH,