Looping through Sheets
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
|