Sample Code to Lock each Sheet

Sub LockSheets()
'
' LockSheets Macro
''


Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer


Set wb = ActiveWorkbook

'Get true dimension for array
cSheets = wb.Sheets.Count
ReDim sSheets(1 To cSheets)

'Fill array with worksheet names
For i = 1 To (cSheets)
sSheets(i) = wb.Sheets(i).Name

wb.Sheets(i).Activate
ActiveSheet.Protect Password:="OAG"

Next i

wb.Sheets(1).Activate
End Sub

----------------

Sample Code I created to copy:

Sub CopyToOtherSheets()
'
' Copy Macro
'
'


Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cSheets As Integer
Dim sSheets() As String
Dim i As Integer


Sheets(7).Range("M28").Copy

Set wb = ActiveWorkbook

'Get true dimension for array
cSheets = wb.Sheets.Count
ReDim sSheets(7 To cSheets - 1)

'Fill array with worksheet names
For i = 7 To (cSheets - 1)
sSheets(i) = wb.Sheets(i).Name

wb.Sheets(i).Activate
Range("M28").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select



Next i

Application.CutCopyMode = False
wb.Sheets(7).Activate
End Sub


----------

Dennis
dalejrstwin


"Utkarsh Majmudar" wrote:

I have a Sub that I run on each of the Sheets in my workbook. This Sub
essentially copies data from a set of files into each sheet of my main
file. Is there a way in which I need not run the macro 'n' number of
times but loop through all the sheets at one go.

My current code looks like this:
***************
Sub Populate()

Dim Sname As String
Dim IFname As String

Application.ScreenUpdating = False
myfile = ActiveWorkbook.Name
mypath = ActiveWorkbook.Path
Range("E11").Value = ActiveSheet.Name
Sname = Range("E11").Value
IFname = Sname & ".xls"

Workbooks.Open Filename:=mypath & "\" & IFname
'Enter Basic Information
Workbooks(IFname).Sheets(Sname).Activate
Range("E1").Select
Selection.Copy
Windows(myfile).Activate
Range("E1").Select
ActiveSheet.Paste

...... more stuff here

Workbooks(IFname).Sheets(Sname).Activate
ActiveWorkbook.Close savechanges:=False
Workbooks(myfile).Sheets(Sname).Activate
Range("A1").Select
Application.ScreenUpdating = True
End Sub
*****************

Thanks for the help!

Utkarsh