Repeating some code on all worksheets

[matpj]

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

[Myles]

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

[Rob Hick]

Dim w as worksheet

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

[matpj]

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

[Myles]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Niek Otten]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>