Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
945 views
in Technique[技术] by (71.8m points)

vba - Loop through all worksheets in workbook

I want to repeat this code on all the worksheets in a workbook.

There may sometimes be 1-2 worksheets sometimes 50+.

Sub HoursTotal()
'
' HoursTotal Macro
'

Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-1])"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Total Hours"
Range("G1").Select


End Sub
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This should do it.

Sub HoursTotal()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Range("F2").FormulaR1C1 = "=SUM(C[-1])"
        ws.Range("F1").FormulaR1C1 = "Total Hours"
        ws.Range("G1").Select 'I don't think you need this line but I included it anyways
    Next
End Sub

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...