?
Solved

VBA to select specific worksheets to run a macro

Posted on 2014-08-13
2
Medium Priority
?
2,494 Views
Last Modified: 2014-08-14
I need to use VBA to run a macro but to only run the macro for the worksheets that I have flagged in an administration sheet.
If when working down the list of worksheets a sheet is not present the macro will simply go to the next sheet on the list
The worksheet list is constantly being changed, increased and decreased
Selective-macro-application.xlsm
0
Comment
Question by:DAVID131
2 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40259737
Here is code that will achieve this selective method:
Option Explicit
Sub Run_Selective()
    Dim rng As Range
    Dim cl As Object
    Dim boolError As Boolean
    
    Sheets("Administration").Select
    Set rng = Range("A1", Range("A1").End(xlDown))
    
    For Each cl In rng
        If cl.Offset(0, 1).Value = "yes" Then
            On Error GoTo NoSheet
            boolError = False
            Sheets(cl.Value).Activate
            If Not (boolError) Then SIMPLE_COPY_TEST
        End If
    Next cl
    MsgBox "Done."
    Exit Sub
    
NoSheet:
    boolError = True
    Resume Next

End Sub

Open in new window


I've also attached a modified version of your workbook for you to test.  I tweaked the SIMPLE_COPY_TEST subroutine also to better work with the code.

Regards,
-Glenn
EE-Selective-macro-application.xlsm
0
 

Author Closing Comment

by:DAVID131
ID: 40261379
Thanks Glenn
Spot on piece of VBA - DOES EXACTLY WHAT WAS ASKED AND SAVES A LOT OF MANUAL WORK

Regards
David
Edinburgh
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question