Looping through Sheets

  • Thread starter Thread starter Utkarsh Majmudar
  • Start date Start date
U

Utkarsh Majmudar

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
 
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
 
I am not 100% sure exactly what you are looking for but if all you want to do
is loop through all of the sheets in a workbook this will do it...

dim wks as worksheet

for each wks in worksheets
'do that voodoo that you do
next wks
 
Jim

Your idea is right. Unfortunately the code doesn't run. It populates the
first sheet but does not move on to the next one.

Any clues?

Utkarsh
 
Can you paste your revised code. We should be able to figure out what'
wrong. Hard to tell without the code
 
Thanks to all those who helped. Resolved the problem with the following
code that I found on a posting:

****************

Dim sh As Worksheet
For i = 1 To Sheets.Count
Set sh = Sheets(i)
sh.Activate

'My voodoo stuff here

Next i

*****************

Utkarsh
 
Back
Top