Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help with a loop please.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Help with a loop please.
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with a loop please.
For Each sh In ThisWorkbook.Sheets

sh.Cells.Select

sh.Columns("D:D").Select

you need to qualify all of the range objects with the sh. prefix so VBA
knows to go to other that the active sheet.


"ryguy7272" wrote:

For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,393
Default Help with a loop please.
Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Help with a loop please.
Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Help with a loop please.
Run-time error '1004':
Select method of Range class failed

....guess that would be helpful is debugging this thing...

--
RyGuy


"ryguy7272" wrote:

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Help with a loop please.
I made a little progress. I eliminated the error, but all changes are made
to one single sheet, so I literally get 'Next Quarter' 10 times, in a cell in
one sheet and 'Current Quarter' 10 times in the same cell in the same sheet!!

Application.DisplayAlerts = False
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q1*" Then
'If InStr(1, sh.Name, "*Q1*") Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
End If
Next sh

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q4*" Then
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Current Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Current Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh


ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True

There is something wrong with the logic of the loop because it doesn't
evaluate the names of the sheets, and it doesn't move to the next sheet after
it does the find/replace on the first sheet that it lands on. Can someone
please point out my error?

Thanks so much,
Ryan---


--
RyGuy


"ryguy7272" wrote:

Run-time error '1004':
Select method of Range class failed

...guess that would be helpful is debugging this thing...

--
RyGuy


"ryguy7272" wrote:

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with a loop please.
Copy this and paste it in the module. Fix any line wraps (they should be red
in the code module if they wrapped). Comment out your old code. Then try it.

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

With Sh.Columns(4)
.Replace What:="Total Display", _
Replacement:="Total Display - Next Quarter", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="Class 1", Replacement:="Class 1 - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:= False, ReplaceFormat:=False
.Replace What:="Class 2", Replacement:="Class 2 - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End With

End If
Next sh


"ryguy7272" wrote:

I made a little progress. I eliminated the error, but all changes are made
to one single sheet, so I literally get 'Next Quarter' 10 times, in a cell in
one sheet and 'Current Quarter' 10 times in the same cell in the same sheet!!

Application.DisplayAlerts = False
Dim sh As Worksheet

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q1*" Then
'If InStr(1, sh.Name, "*Q1*") Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
End If
Next sh

For Each sh In ThisWorkbook.Sheets
If sh.Name Like "RVP - Q4*" Then
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Select
Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Current Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Current
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Current Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh


ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True

There is something wrong with the logic of the loop because it doesn't
evaluate the names of the sheets, and it doesn't move to the next sheet after
it does the find/replace on the first sheet that it lands on. Can someone
please point out my error?

Thanks so much,
Ryan---


--
RyGuy


"ryguy7272" wrote:

Run-time error '1004':
Select method of Range class failed

...guess that would be helpful is debugging this thing...

--
RyGuy


"ryguy7272" wrote:

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Help with a loop please.
Range("D:D")
Columns("D")
Columns(4)

"ryguy7272" wrote:

Run-time error '1004':
Select method of Range class failed

...guess that would be helpful is debugging this thing...

--
RyGuy


"ryguy7272" wrote:

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,836
Default Help with a loop please.
Thanks. Unfortunately, it doesn't move from one sheet to another sheet in
ThisWorkbook.Sheets

What can I do?

Thanks,
Ryan---

--
RyGuy


"JLGWhiz" wrote:

Range("D:D")
Columns("D")
Columns(4)

"ryguy7272" wrote:

Run-time error '1004':
Select method of Range class failed

...guess that would be helpful is debugging this thing...

--
RyGuy


"ryguy7272" wrote:

Thanks for the info. JLGWhiz. That makes sense, but it still doesn't work.
Now it errors on this line:
sh.Columns("D:D").Select

Any thoughts?


Thanks,
Ryan---

--
RyGuy


"Bernard Liengme" wrote:

Here is my test sub

Sub tryme()
For Each ws In Worksheets
If ws.Name Like "temp*" Then
ws.Range("A1") = "X"
End If
Next ws
End Sub

If I used Range("A1")="X" without referencing the ws varaible, the sub put X
in A1 of the active worksheet and no others. This. of course, is exacly
whyat the code would mean.

You need something like

Dim sh As Worksheet
For Each sh In Sheets
If sh.Name Like "RVP - Q1*" Then
with sh
.Cells.Select
.Selection.Copy
,,,,
next with
next sh


best wsihes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ryguy7272" wrote in message
...
For some reason, all changes occur in one single sheet. I used F8 to move
through the code and still didn't see why it's not moving from one sheet
to
another sheet. Can someone point out my error?

Dim sh As Worksheet
For Each sh In Sheets

If sh.Name Like "RVP - Q1*" Then

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Columns("D:D").Select
Selection.Replace What:="Total Display", Replacement:= _
"Total Display - Next Quarter", LookAt:=xlPart,
SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="Class 1", Replacement:="Class 1 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Class 2", Replacement:="Class 2 - Next
Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Search", Replacement:="Search - Next Quarter",
_
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False
Selection.Replace What:="Total", Replacement:="Total - Next Quarter", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If
Next sh

Thanks,
Ryan---

--
RyGuy



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
returning back to loop check condition without completing the loop ashish128 Excel Programming 13 April 3rd 08 12:53 PM
Loop to Filter, Name Sheets. If Blank, Exit Loop ryguy7272 Excel Programming 3 February 5th 08 03:41 PM
Naming Worksheets - Loop within a loop issue klysell Excel Programming 5 March 29th 07 05:48 AM
(Complex) Loop within loop to create worksheets klysell Excel Programming 1 March 20th 07 12:03 AM
Advancing outer Loop Based on criteria of inner loop ExcelMonkey Excel Programming 1 August 15th 05 05:23 PM


All times are GMT +1. The time now is 10:27 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement