Repeating some code on all worksheets
 

Hi,

I have some code that will find the bottom row of data, offset by a
couple of rows and paste a string.
I need this to repeat across all worksheets in the workbook.
can anyone give me the VBA for looping through all worksheets?

thanks in advance,
Matt


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=500576

Repeating some code on all worksheets
 

Sub LoopThroWShts()
Dim sh WorkSheets

For each sh in WorkSheets
Your code
Next

End Sub

Remember to qualify your ranges with sh as in sh.Range("a10:e25") or
sh.Cells(10, "a") as the case may be.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=500576

Repeating some code on all worksheets
 

Dim w as worksheet

For each w in activeworkbook.worksheets
---insert code
Next w

Repeating some code on all worksheets
 

I have tride that:

Dim w As Worksheet

For Each w In ActiveWorkbook.Worksheets
Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w

but this just stays on the first worksheet


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=500576

Repeating some code on all worksheets
 

Hi Matt,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim LRow As Long
Const sStr As String = "TEST"

Set WB = ActiveWorkbook '<<==== CHANGE

For Each SH In WB.Worksheets
With SH
LRow = .Cells(Rows.Count, "A").End(xlUp)(3).Row
.Cells(LRow, "A") = sStr
End With
Next SH

End Sub
'<<=============

This assumes that Column A is used to determine the last used cell and the
location of the pasted string.

Change A to accord with your requirements.


---
Regards,
Norman


"matpj" wrote in
message ...

Hi,

I have some code that will find the bottom row of data, offset by a
couple of rows and paste a string.
I need this to repeat across all worksheets in the workbook.
can anyone give me the VBA for looping through all worksheets?

thanks in advance,
Matt


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=500576

Repeating some code on all worksheets
 

To preserve your coding, two ways to get round:

Solution1:

For Each w In ActiveWorkbook.Worksheets
w.Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w

Solution 2:

For Each w In ActiveWorkbook.Worksheets
w.activate
Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=500576

Repeating some code on all worksheets
 

This is what Myles told you:

Remember to qualify your ranges with sh as in sh.Range("a10:e25") or
sh.Cells(10, "a") as the case may be.


--
Kind regards,

Niek Otten

"matpj" wrote in
message ...

I have tride that:

Dim w As Worksheet

For Each w In ActiveWorkbook.Worksheets
Columns("A:A").Select
Selection.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "Total Surplus"
Next w

but this just stays on the first worksheet


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=500576