Turns out I didn't have names in the range for which there were equivalent
sheets. I'll try your solution. Seems very elegant.

Thanks for the reply!

"Dianne" wrote in message
...
Is your City variable a module-level variable? If not, then the value
held in City is not accessible from the Copy() procedure. Perhaps you
could change your copy procedure to accept a parameter, e.g.

Public Sub CopyToCity(strCity As String)
Selection.Copy
Sheets(strCity).Select
'I assume you paste your value into a cell
End Sub

Then change your SMCMonthlyUpdateSFR procedu

Public Sub SMCMonthlyUpdateSFR()
Sheets("work").Select
Range("C3").Select
Dim Cell As Range
For Each Cell In Range("B3:B27")
City = Cell.Value
Call CopyToCity(City)
Next
End Sub

Also, I would avoid using the word Copy as a procedure name, since Excel
may get it confused with its own Copy method. I have therefore changed
your procedure name to CopyToCity.

In fact, you could do the whole thing in one procedure without using
copy/paste (I may be jumping the gun here!):

Public Sub SMCMonthlyUpdateSFR2()

Dim Cell As Range
Dim strToCopy As String
Dim strSheetName As String

strToCopy = Sheets("work").Range("C3").Value

For Each Cell In Range("B3:B27")
strSheetName = Cell.Value
If Not strSheetName = "" Then
'let's say you want to paste into A1 of each sheet
Sheets(strSheetName).Range("A1").Value = strToCopy
End If
Next

End Sub

--
Dianne

In nk.net,
Rick Campbell typed:
I've been using the following code in a workbook successfully:

Public Sub SMCMonthlyUpdateSFR()
Sheets("work").Select
Range("C3").Select
Dim Cell As Range
For Each Cell In Range("B3:B27")
City = Cell.Value
Call copy
Next
End Sub

Public Sub copy()
Selection.copy
Sheets(City).Select

I copied the code to another workbook with the same format, different
data, and now the City value is not being posted to
Sheets(City).Select.