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

Jeff Johnson

New Member
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:

Code:
``````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.

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
maybe....

Code:
``````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``````

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

Code:
``````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?

I figured it out. Thanks for your help!

Code:
``````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``````

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,

Replies
1
Views
310
Replies
3
Views
347
Replies
14
Views
949
Replies
0
Views
562
Replies
3
Views
3K

1,196,503
Messages
6,015,586
Members
441,903
Latest member
MG12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back