What you've done will certainly work.
Generally when designing a program, if there are blocks of code which are repeated more than once then these are made into 'subroutines' which are called with parameters which control the way they behave. In my last post I changed the code so that instead of acting on a single sheet, it was rewritten to expect the sheet name to be passed to it as a parameter which it stores as
aTargetWS. When you call it with
Call CopyBlockTwice("MONDAY"), it will act on sheet MONDAY, etc.
You can then have separate routines to generate each sheet like I posted and/or you can have a single routine to generate them all in one go:-
Code:
[FONT=Fixedsys]Public Sub DoEveryDay
Call CopyBlockTwice("MONDAY")
Call CopyBlockTwice("TUESDAY")
Call CopyBlockTwice("WEDNESDAY")
Call CopyBlockTwice("THURSDAY")
Call CopyBlockTwice("FRIDAY")
End Sub
[/FONT]
The reason for extracting repeated code into separate subroutines is that if you had to change the code for any reason - maybe you found a bug in it or maybe you just decided to change what it did or the way it did it - then you'd only have one piece of code to look at instead of five. You might want to try my code just to satisfy yourself that it works. Save it somewhere in any case, as it demonstrates how parameters are passed to subroutines.
With Naming Macro I named it Macro 10 but in the macro box macro 10 comes up but so does CopyBlockTwice
The macro box lists all the public macros it finds. If
Macro10 is the code which currently works, you can delete
CopyBlockTwice. Remember: a macro or subroutine starts with a
Sub line; the word
Sub may be preceded by
Private or
Public and it will be followed by the name of the
Sub and a pair of brackets; the brackets may have the names of parameters in them; and the macro ends with an
End Sub line. Make sure you don't delete anything which belongs to an adjacent macro! I always insert an extra line between macros just to keep them apart and a lot of people insert one or more comment lines for the same reason.
One thing I would definitely recommend is that you rename your macro something more meaningful, otherwise when you have a few more of them, you're very likely to forget which one does what.
I would actually like to do a lot more with these sheets. Would you be willing to carry on helping me out?? If so shall I keep posting at this location?
I do check in to the board fairly often and I'm quite happy to respond to any posts where I feel I can help, as indeed are many other people, many of whom are much more knowledgeable and experienced than I am. I can't guarantee to respond to everything you post: as you can see from my profile, I have 4000+ posts in my name - if I were to guarantee a personal service to each of the people I'd responded to in the past I'd never get anything else done!
For the same reason, in common with many of the other regulars, I tend to ignore any private messages asking for assistance where I feel that posting in the public forum is more appropriate. Posting publicly is usually to be preferred and there are several reasons for this:-
- There may be other people who experience a similar problem in the future, so having the discussion in public allows people to find a solution more easily in the future. (In fact MrExcel posts normally appear in Google search results within minutes of being posted!)
- If you post to the board, you're almost certain to have an answer within a few hours - sometimes even within a few minutes - whereas sending private messages relies on the recipient checking in to the board.
- Posting in public will elicit a number of responses offering a wide range of different techniques, some of which will be better, faster or more elegant than others. You will be able to choose the one which is the most suitable for you and learn a lot in the process.
- Relying on a single person for a solution puts you at the mercy of his abilities. If you pick the wrong person you might find yourself heading down a completely inappropriate road.
