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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("She et" & c) _
.Properties("Name").Value).Range("C5:E5").Copy
Range("C5:E5").PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd
Next c


--
HTH

Bob Phillips

"Gary Keramidas" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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