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 |
need some help with selecting a sheet in a loop again
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 |
need some help with selecting a sheet in a loop again
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 |
need some help with selecting a sheet in a loop again
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 |
need some help with selecting a sheet in a loop again
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 |
need some help with selecting a sheet in a loop again
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 |
need some help with selecting a sheet in a loop again
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 |
All times are GMT +1. The time now is 02:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com