How can this Excel macro be more efficient and more versatile?

Posted on 2014-08-23
Last Modified: 2015-03-01
I have attached abridged detals of an Excel macro, which each week has to be renamed to work with the current week and enlarged to include an additional line of activity. Although this sort of works, it's cumbersome and I would like it to be more efficient and versatile. I have included details of Week 03, Week 04 and Week 05 so you can see the week by week progression as the macro grows; by the end of the year I will have 52 separate macros. Just one macro to do the whole job would be great. Thanks for any help available.
Question by:Allpurple
    LVL 19

    Expert Comment

    attachment missing?

    Author Comment

    LVL 27

    Expert Comment

    by:Glenn Ray
    Here's a slightly more concise version of your first subroutine; you could easily adapt this code to your other two:
    Sub Prepare_Week_03()
        Dim r As Integer
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        For r = 5 To 7
            If Cells(r, 2) <> 0 Then GoTo Fin
            Cells(r, 2).EntireRow.Hidden = True
        Next r
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End Sub

    Open in new window

    LVL 27

    Accepted Solution


    Did you have a chance to review and or test my solution?  If so, and it will work for you, can you please properly close this question by clicking the "Accept this solution" link above my submission above that answers your question?.  This will help ensure that future searches are meaningful to other EE members.

    Otherwise, let us know if you have any other issues.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    In a nutshell Dropbox is a cloud-based data storage service which synchronizes with data files on your computers. The Dropbox folk provide 2GB of free storage but if you need more you can sign up for a 50GB or 100GB subscription account. I pr…
    As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now