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.

Can anyone tell me what the problem is?

TIA

Rick

Rick,

I am a little confused about how your code is supposed to work, but

reconsider using the term copy. This is a reserved word in Excel and
'instructs' Excel to do a copy like in:
Activecell.Copy
Selection.copy

I have learned to double check all terms in code with the F1 key. If I get
a 'Key word not found' than it is OK to use. Otherwise you are prone to
crashes and other not-wanted events...

And just what is 'Selection.copy' copying?
Except for 'C3' you don't seem to be selecting anything...
AND - in most code you can eliminate the 'select' pieces...
You can get the same result with:
Cell.Copy

hth
--
sb
"Rick Campbell" wrote in message
nk.net...
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.

Can anyone tell me what the problem is?

TIA

Rick

Rick

depends where you get the error. Run time error 9 is subscript out of
range. So, it could mean that the sheet "work" does not exist or the sheet
that you are copying to (from cell B3 to B27) does not exist. Then it could
depend on where the variable "city" is defined ... it needs to be a public
variable or at least a global variable which has been defined before all the
subroutines.

Another thought is that you don't have city names in all the cells B3 to
B27.

Dim city As String

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
End Sub

This would work unless you have blanks in the range or names in the range
for which you don't have equivalent sheets .

Regards

Trevor


"Rick Campbell" wrote in message
nk.net...
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.

Can anyone tell me what the problem is?

TIA

Rick

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.

Thanks for the tip about copy. I'll change it. It's not really the problem
here, because it works in another workbook. Turns out it was City names
without equivalent worksheets.

"steve" wrote in message
...
Rick,

I am a little confused about how your code is supposed to work, but

reconsider using the term copy. This is a reserved word in Excel and
'instructs' Excel to do a copy like in:
Activecell.Copy
Selection.copy

I have learned to double check all terms in code with the F1 key. If I
get
a 'Key word not found' than it is OK to use. Otherwise you are prone to
crashes and other not-wanted events...

And just what is 'Selection.copy' copying?
Except for 'C3' you don't seem to be selecting anything...
AND - in most code you can eliminate the 'select' pieces...
You can get the same result with:
Cell.Copy

hth
--
sb
"Rick Campbell" wrote in message
nk.net...
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.

Can anyone tell me what the problem is?

TIA

Rick

"names in the range for which you don't have equivalent sheets "

Thanks! That was the ticket.

"Trevor Shuttleworth" wrote in message
...
Rick

depends where you get the error. Run time error 9 is subscript out of
range. So, it could mean that the sheet "work" does not exist or the
sheet
that you are copying to (from cell B3 to B27) does not exist. Then it
could
depend on where the variable "city" is defined ... it needs to be a public
variable or at least a global variable which has been defined before all
the
subroutines.

Another thought is that you don't have city names in all the cells B3 to
B27.

Dim city As String

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
End Sub

This would work unless you have blanks in the range or names in the range
for which you don't have equivalent sheets .

Regards

Trevor


"Rick Campbell" wrote in message
nk.net...
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.

Can anyone tell me what the problem is?

TIA

Rick

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.

Rick

you're welcome. Glad I could point you in the right direction. It had also
occurred to me that "copy" was a reserved word and might cause a problem,
possibly between different versions of Excel/VBA but I tested your coded and
it worked. It is, however, a good tip as you can make life difficult for
yourself. I seem to recall that defining a variable called "range" can have
some interesting effects.

Regards

Trevor


"Rick Campbell" wrote in message
ink.net...
"names in the range for which you don't have equivalent sheets "

Thanks! That was the ticket.

"Trevor Shuttleworth" wrote in message
...
Rick

depends where you get the error. Run time error 9 is subscript out of
range. So, it could mean that the sheet "work" does not exist or the
sheet
that you are copying to (from cell B3 to B27) does not exist. Then it
could
depend on where the variable "city" is defined ... it needs to be a
public
variable or at least a global variable which has been defined before all
the
subroutines.

Another thought is that you don't have city names in all the cells B3 to
B27.

Dim city As String

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
End Sub

This would work unless you have blanks in the range or names in the
range
for which you don't have equivalent sheets .

Regards

Trevor


"Rick Campbell" wrote in message
nk.net...
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.

Can anyone tell me what the problem is?

TIA

Rick