|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Help with a loop please.
In the locals window it shows that it does change sheets. You won't see the
sheets change because the code does not use select. Check to see if the changes were made, if noit then make sure your criteria is correct in the If...Then statement for the worksheet name including the spaces between RVP the dash and the Q1. You have another built in problem, Ryan. The words you are replacing are repeatded in the different statements, and the code is doing exactly what it is instructed to do. When it sees Total Display, it replaces it with Total Display - Next Quarter, then later when it sees Total, it replacest that with Total - Next Quarter so that the first replacement ends up looking like "Total - Next Quarter Display - Next Quarter" because it replaced the Total that had already been changed. You need LookAt:=xlWhole. I ran this code with the changes and it worked ok for me with three worksheets. So if you still have a problem, it has to be the sheet name not registering because of structural differences. Sub dk() 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 Application.CutCopyMode = False With sh.Columns(4) .Replace What:="Total Display", _ Replacement:="Total Display - Next Quarter", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False .Replace What:="Class 1", Replacement:="Class 1 - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Class 2", Replacement:="Class 2 - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Search", Replacement:="Search - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Total", Replacement:="Total - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False End With End If Next sh End Sub "ryguy7272" wrote: 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 |
Help with a loop please.
This did exactly what I wanted it to do. I am so happy now. Yes, you are
right JLGWhiz, the Total and Total Display was screwing me up. I didn't even realize it before, because I was focusing on the looping issue. I've never used the 'LookAt:=xlWhole' object before. Thanks for pointing that out! Thanks for everything!! I can move on with my project now!! Kind Regards, Ryan-- -- RyGuy "JLGWhiz" wrote: In the locals window it shows that it does change sheets. You won't see the sheets change because the code does not use select. Check to see if the changes were made, if noit then make sure your criteria is correct in the If...Then statement for the worksheet name including the spaces between RVP the dash and the Q1. You have another built in problem, Ryan. The words you are replacing are repeatded in the different statements, and the code is doing exactly what it is instructed to do. When it sees Total Display, it replaces it with Total Display - Next Quarter, then later when it sees Total, it replacest that with Total - Next Quarter so that the first replacement ends up looking like "Total - Next Quarter Display - Next Quarter" because it replaced the Total that had already been changed. You need LookAt:=xlWhole. I ran this code with the changes and it worked ok for me with three worksheets. So if you still have a problem, it has to be the sheet name not registering because of structural differences. Sub dk() 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 Application.CutCopyMode = False With sh.Columns(4) .Replace What:="Total Display", _ Replacement:="Total Display - Next Quarter", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False .Replace What:="Class 1", Replacement:="Class 1 - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Class 2", Replacement:="Class 2 - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Search", Replacement:="Search - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False .Replace What:="Total", Replacement:="Total - Next Quarter", _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False End With End If Next sh End Sub "ryguy7272" wrote: 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 |
| All times are GMT +1. The time now is 12:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com