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

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

Dim w as worksheet

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

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

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

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

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