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

Posted on 2014-08-23
Medium Priority
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:Bob Barnes
  • 2
LVL 19

Expert Comment

ID: 40280376
attachment missing?

Author Comment

by:Bob Barnes
ID: 40280753
LVL 27

Expert Comment

by:Glenn Ray
ID: 40284440
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

Glenn Ray earned 2000 total points
ID: 40301387

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.


Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

807 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