Loop to move between sheets

Andy W

Board Regular
Joined
Jul 31, 2003
Messages
68
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Private Sub CommandButton1_Click()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

Your Code Here

Next
End Sub
 

Andy W

Board Regular
Joined
Jul 31, 2003
Messages
68
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Board Regular
Joined
Jul 31, 2003
Messages
68

ADVERTISEMENT

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

Board Regular
Joined
Jul 31, 2003
Messages
68
Hello again,

They have changed their mind again, agggggggh :oops: 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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

Board Regular
Joined
Jul 31, 2003
Messages
68
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
No problem Andy,
I should have mentioned it in my orginal post, instead of assuming.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,446
Members
425,546
Latest member
DisMissive

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top