Starting code on specific middle sheet and every sheet after

Xybry

New Member
Joined
Sep 13, 2006
Messages
5
Excel macro code starting on specific middle sheet and running on every sheet after to the last sheet.

Question? - any coding suggestions for the following:

I need to run code on a sheet “Deals” (same name every month) and the same code on every sheet after “Deals” but the position of sheet “Deals” changes every month and the total number of sheet varies from 150-250 per month with new sheet names every month?

Thank you in advance!
Xybry
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I suppose you could use Index, something like this perhaps.
Code:
For I = Worksheets("Deals").Index To Worksheets.Count
     Set ws = Worksheets(I)
     ' run code on ws
     MsgBox ws.Name
Next I
A better idea might be to run through all sheets and check their names, but that would only work if there was some way of distinguishing which sheets to work with using their name.
 

Xybry

New Member
Joined
Sep 13, 2006
Messages
5
Norie,

Thank you but, unfortunately, it does not work. It stays on the "Deals" tab and continues to loop but does not advance to the following tabs. Do you have another suggestion?

Thank you,

Xybry
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
How does it not advance?

What is your actual code?
 

Xybry

New Member
Joined
Sep 13, 2006
Messages
5
Norie,

Thank you very much for your help, you inspired me to approach the problem from a different viewpoint and came up with the simplest solution, ActiveSheet.Next.Select. Therefore, it now works. Good old Occam’s Razor, the simplest solution is usually the correct solution.

Thanks again for your help,

Xybry
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Glad to see you got it working.:)

There is probably a 'better' solution though.

The reason I'm saying that is because there is no need to actually select a worksheet to work with it.

If you post the code that you say didn't work I might be able to demonstrate what I mean.
 

Forum statistics

Threads
1,136,347
Messages
5,675,233
Members
419,555
Latest member
Paddington

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