need some help with selecting a sheet in a loop again

i have tabs renamed, so i am using this

For c = 2 To 13

Sheet2.Range("C5:E5").Copy
Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
(this range is on sheet1)
Next c


i want to replace the 2 with the c so it selects the range of sheets from 2
to 13.


any help appreciated
--


Gary

For c = 2 To 13

Worksheet("Sheet" & c).Range("C5:E5").Copy
Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
(this range is on sheet1)
Next c


--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:uPaoE6ygFHA.1468@TK2MSFTNGP14.phx.gbl...
> i have tabs renamed, so i am using this
>
> For c = 2 To 13
>
> Sheet2.Range("C5:E5").Copy
> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> (this range is on sheet1)
> Next c
>
>
> i want to replace the 2 with the c so it selects the range of sheets from
2
> to 13.
>
>
> any help appreciated
> --
>
>
> Gary
>
>
>

bob:

this doesn't seem to work. could be 2 reasons:

1. i have the tabs renamed, doesn't that make a difference when using the
worksheet function?
2. also, isn't there a space or something in the number that c represents?
if i combine them and use a msgbox to display. it shows sheet 2

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uvj$l8ygFHA.2268@TK2MSFTNGP15.phx.gbl...
> For c = 2 To 13
>
> Worksheet("Sheet" & c).Range("C5:E5").Copy
> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> (this range is on sheet1)
> Next c
>
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:uPaoE6ygFHA.1468@TK2MSFTNGP14.phx.gbl...
>> i have tabs renamed, so i am using this
>>
>> For c = 2 To 13
>>
>> Sheet2.Range("C5:E5").Copy
>> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
>> (this range is on sheet1)
>> Next c
>>
>>
>> i want to replace the 2 with the c so it selects the range of sheets from
> 2
>> to 13.
>>
>>
>> any help appreciated
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

here's the error:

compile error,
sub or function not defined

worksheet is highlighted

--


Gary


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:uvj$l8ygFHA.2268@TK2MSFTNGP15.phx.gbl...
> For c = 2 To 13
>
> Worksheet("Sheet" & c).Range("C5:E5").Copy
> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> (this range is on sheet1)
> Next c
>
>
> --
> HTH
>
> Bob Phillips
>
> "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> news:uPaoE6ygFHA.1468@TK2MSFTNGP14.phx.gbl...
>> i have tabs renamed, so i am using this
>>
>> For c = 2 To 13
>>
>> Sheet2.Range("C5:E5").Copy
>> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
>> (this range is on sheet1)
>> Next c
>>
>>
>> i want to replace the 2 with the c so it selects the range of sheets from
> 2
>> to 13.
>>
>>
>> any help appreciated
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

Hi Gary,

Try something like:

Sub TestIt()
Dim c As Long
Dim sh As Worksheet
For c = 3 To ActiveWorkbook.Sheets.Count
For Each sh In ActiveWorkbook.Worksheets
If sh.CodeName = "Sheet" & c Then
'do something. e.g.:
MsgBox sh.Name
End If
Next sh
Next c

End Sub

---
Regards,
Norman



"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:uPaoE6ygFHA.1468@TK2MSFTNGP14.phx.gbl...
>i have tabs renamed, so i am using this
>
> For c = 2 To 13
>
> Sheet2.Range("C5:E5").Copy
> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> (this range is on sheet1)
> Next c
>
>
> i want to replace the 2 with the c so it selects the range of sheets from
> 2 to 13.
>
>
> any help appreciated
> --
>
>
> Gary
>
>
>

Hi Gary,

Change:

> For c = 3 To ActiveWorkbook.Sheets.Count

to:

> For c = 2 To 13

I forgot to amend my test code!

---
Regards,
Norman



"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:%23udKVJzgFHA.1468@TK2MSFTNGP14.phx.gbl...
> Hi Gary,
>
> Try something like:
>
> Sub TestIt()
> Dim c As Long
> Dim sh As Worksheet
> For c = 3 To ActiveWorkbook.Sheets.Count
> For Each sh In ActiveWorkbook.Worksheets
> If sh.CodeName = "Sheet" & c Then
> 'do something. e.g.:
> MsgBox sh.Name
> End If
> Next sh
> Next c
>
> End Sub
>
> ---
> Regards,
> Norman

If you have renamed them that is difficult, we need to use the codename
then.

For c = 2 To 13
Worksheet(ThisWorkbook.VBProject.VBComponents("Sheet" & c) _
.Properties("Name").Value).Range("C5:E5").Copy
Range("C5:E5").PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd
Next c


--
HTH

Bob Phillips

"Gary Keramidas" <GKeramidas@comcast.net> wrote in message
news:urRPMAzgFHA.3316@TK2MSFTNGP14.phx.gbl...
> bob:
>
> this doesn't seem to work. could be 2 reasons:
>
> 1. i have the tabs renamed, doesn't that make a difference when using the
> worksheet function?
> 2. also, isn't there a space or something in the number that c represents?
> if i combine them and use a msgbox to display. it shows sheet 2
>
> --
>
>
> Gary
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:uvj$l8ygFHA.2268@TK2MSFTNGP15.phx.gbl...
> > For c = 2 To 13
> >
> > Worksheet("Sheet" & c).Range("C5:E5").Copy
> > Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> > (this range is on sheet1)
> > Next c
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Gary Keramidas" <GKeramidas@comcast.net> wrote in message
> > news:uPaoE6ygFHA.1468@TK2MSFTNGP14.phx.gbl...
> >> i have tabs renamed, so i am using this
> >>
> >> For c = 2 To 13
> >>
> >> Sheet2.Range("C5:E5").Copy
> >> Range("C5:E5").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd '
> >> (this range is on sheet1)
> >> Next c
> >>
> >>
> >> i want to replace the 2 with the c so it selects the range of sheets
from
> > 2
> >> to 13.
> >>
> >>
> >> any help appreciated
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >
> >
>
>