Need VBA Macro to run macro on worksheets that are specified by a range in the wksht

[JohnPellerin]

Hello,
I am new to this forum. I am an advanced user in Excel 2007 and have a decent knowledge of VBA (but have much to learn!!!)

I have a problem that I am trying to solve. I am working on a file where the number of sheets may change periodically. In addition, I have macros that run on some, but not all of the worksheets. The macros perform the identical operations on each worksheet. The problem is that I have to go to each worksheet and execute the macro button.

My goal is to create one macro that reads a list of sheetnames ( for example from sheet1, range A1), and then runs the macros only on those worksheets. As mentioned above, the numbers of sheets may change periodically, so the macro needs to be flexible in that regard. Any idea on how to accomplish this?

Best regards,

John

[JapanDave]

What decides what sheets the macros will run on?

[JohnPellerin]

There will be a range on one of the worksheets that contains a listing of the worksheets that the macro will run on. I would like to use a range name for this list.

[JapanDave]

Try this,

Change the range to suit your workbook.

Please Login or Register  to view this content.

[JohnPellerin]

Below is the macro as adapted with my file name and range name. I am getting a compile error message. Do I have something entered incorrectly?

Sub ImportOCTActuals()

Dim ws
Dim cel
For Each ws In ActiveWorkbook.Sheets
Set cel = Range("CostCenterListing").Find(ws.File Parameters)
If Not cel Is Nothing Then
'run your macro
End If
Next ws

End Sub

[MarvinP]

Hi John and welcome to the forum,

Try this for specific worksheets. See the attached for the example:

Please Login or Register  to view this content.

[JohnPellerin]

I ran the WhatSheetsTest. if i understand the code correctly, I would have to define the sheet names in the VBA code specfically. I guess what I am ultimately looking to do is define the worksheets in a list in the actual spreadsheet. Is that possible?

[JapanDave]

Please wrap you code in code tags.

Why are changing this

Please Login or Register  to view this content.

That defeats the purpose of the code. Leave it as ws.name


Try this,

Please Login or Register  to view this content.

[JohnPellerin]

Thank JapanDave. For some reason, it is not performing as I would like. I looks like it only performed the macro on the first worksheet that I defined in the list. I am interpretting the code a looking for every worksheets in the activeworkbook. I do not want to perform the macro in every workbook. I have a specific list of worksheet that I want to perform the macro on. This list is located in a worksheet named File_Parameters and in a named range titled CostCenterListing. I want the macro to get the names of the worksheets from the named range CostCenterListing. is that possible?

[JapanDave]

OK, what the code does is checks if the sheet name is in the Named range you specified, if it is it will run the macro, if not it won't run.

Try this sample book and I have put a couple of sheet names in your named range. A msg will pop up. Now all you have to do is replace the msgbox with your code and adjust the named range to suit.

[JohnPellerin]

Thank you. I was unable to open the excel file as I am denied access to it. I am logged in however. Once the code above determines that the worksheet is in the worksheet name listing, does it select the worksheet or does my macro need to include some language to select active worksheet?

[JapanDave]

I don't know why you can't view the workbook?

But, for you second question. No, just post the code that you are using to do what you want. My portion of the code will recognize if the sheet needs to have it run or not. BTW, the Sheet names in the named range "CostCenterListing" need to be exact matches and are case sensitive.

See if you can access this workbook.

[JohnPellerin]

What is weird is that the macro does perform, but only on the first sheet. And it looking like it repeats several times on that first sheet as opposed to looping to the next sheet in listing. i did check that the rangename is typed exactly in the code. Any ideas on what may be causing this?

[JohnPellerin]

The other weid thing that is happening is if I initiate the macro on a sheet that is not on the worksheet listing, the code gets interrupted. Hoever, if I start it on a worksheet that is in the worksheet listing it runs, but only for that worksheet and for multiple times on that worksheet.

[JapanDave]

What is the sheet name with the named range on it?

[JohnPellerin]

File_Parameters

[JapanDave]

Can you post a sample workbook?

[JohnPellerin]

Attached is a sample file that should illustrate what i am looking to do. The sheetnames are listed on the worksheet titled File_Parameters.

[JapanDave]

OK give this a try.

[JohnPellerin]

I tried the file you sent and it worked. However, I would like to be able to call out macro routines as illustrated below. I tried the macro below and it only performs the macro on the first sheet in the list (and only when I initiate the macro from that worksheet).



Sub ImportOCTActuals()

Dim ws
Dim cel
Application.ScreenUpdating = 0
For Each ws In ActiveWorkbook.Sheets
Set cel = Sheets("File_Parameters").Range("A38:A54").Find(ws.Name)
If Not cel Is Nothing Then
'run your macro
With ws
Call CLEAROCT
Call COPYOCTACT
Call VALUEOCTACT

End With
End If

Next ws

Application.ScreenUpdating = 1

End Sub

[JohnPellerin]

ThI thought more about what I am trying to accomplish. I think if I had a VBA macro that can select select specific worksheets based upon a list of worksheets in an excel sheet. Once it selects a sheet, then it will call a macro to run on that specific sheet. The macro would select one worksheet at a time and move to the next worksheet in the listing upon completing the called macro.

Does anyone have any ideas how to accomplish this?

[JapanDave]

I am a bit backed up right now. But if you give me a few hours I will see what I can do.

[JapanDave]

Try this,

Please Login or Register  to view this content.