Loop to move between sheets

[Andy W]

Hello,

Is there any way of making you macro move through sheets without having to call each one. I am wanting to run a loop that will take the info off a sheet, when it has gathered the info to move up 1 sheet and take all the info I need from there and loop until I run out of sheets [in this case there are 6 sheets]. It is just the moving up a sheet bit I need but I have run out of ideas on how to make this work. Please can you help.

Thanks

 

[Cbrine]

Private Sub CommandButton1_Click()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

Your Code Here

Next
End Sub

 

[Andy W]

Thanks for that, can you help me with another problem I now have. Part of my code needs to refer to the open worksheet on a different workbook. Before I added the code to run through each sheet I used:

Cells(CopyCell, 1) = Workbooks("downtime.xls").Sheets("sheet1").Cells(r, 1)

How can this be modified so I dont name the sheet but refer to the active sheet in downtime.xls.

 

[Cbrine]

Andy,
Are you opening the downtime.xls worksheet using VBA?

ie. Workbooks.Open("Filename")

If so, I find it a good practise to assign name references to all open sheets and workbooks that I plan on referring to later.

Dim DTWB as workbook, DTWS as worksheet

Workbooks.Open("Filename")

set DTWB = ActiveWorkbook
Set DTWS = Activesheet

You can then reference them by using
ie.
DTWB.Range("A1").select
When you put in the DTWB. or DTWS. you will also get a drop down of the available functions and modules, which is always helpful.

If you open it manually you need to reference it like this

Workbooks("Downtime.xls").Worksheet("Sheet1"). ...Your Function

But again you could set the reference the same way and use the reference. Your call.

Set DTWB = Workbooks("Downtime.xls")
Set DtWS = DTWB.Worksheet("Sheet1")

Hope this helps.

 

[Andy W]

The sheet is being opened in VBA, and now it looks like I will have to call each sheet by name, the powers that be want to be able to run the macro daily, origionaly it was to be run once a week.

Thanks for your help

 

[Andy W]

Hello again,

They have changed their mind again, agggggggh now it is to run once a week

I have added

Dim SheetName As Worksheet

For Each SheetName In Workbooks("downtime.xls").Worksheets

My code

Next

I have got the sheet to refer to the activesheet.name to determin the name

But when it reaches the end of the code it doesn't move to the next sheet it loops through the first sheet 6 times [there are 6 sheets in the woorkbook]. Can any one help please

 

[Cbrine]

You mentioned that you are using Activesheet. to reference your worksheets in the loop. You should be using SheetName. to reference the sheet variable name. So all your code in the Loop should be

SheetName.Copy
SheetName.Range("A1").value = "Whatever"

Not

Activesheet.Copy

A quickfix would be to Put SheetName.Activate as the first line of your loop. This should make the current loop sheet the active sheet, then your activesheet. references should reference the correct sheet.

Calvin

 

[Andy W]

Thanks for that, it the first time I have done anything like this so I appriciate the explanation as to what needs doing and why

 

[Cbrine]

No problem Andy,
I should have mentioned it in my orginal post, instead of assuming.