create new worksheet based on month change

Hi, I have a problem I hope someone can help me with. I have a worksheet for
employees to enter data. At the beginning of each month I run a macro which
copies a mastersheet to start the new month. The first column of the sheet
is for the employee to enter their name. Using code when they move to the
2nd column it automatically inserts the date and the 3rd column the time
both based on whether there is an entry in the first column. I want to call
my new worksheet macro when the date changes month. I have been trying to
use ActiveCell.Offset command to look at the date in the row above but
without success at pinpointing the change in month to call my macro. Can
anyone please help?

Mike


0
mekim (2)
10/7/2003 11:08:34 AM
excel 39879 articles. 2 followers. Follow

3 Replies
529 Views

Similar Articles

[PageSpeed] 29

I'm not quite sure how the activecell fits into your code.  (You don't usually
have to .select or .activate ranges to work with them.)

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("sheet1")
   FirstRow = 2   'header rows?
   LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

   For iRow = FirstRow To LastRow
     If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value) Then
         If Month(.Cells(iRow, "B").Value) _
                             = Month(.Cells(iRow + 1, "B").Value) Then
             'same month/year
         Else
             'different month/year
             'call your new worksheet stuff
         End If
     End If
   Next iRow
End With
End Sub

You could even just format the dates the same way and compare that:

    If Format(.Cells(iRow, "B").Value, "yyyymm") _
        = Format(.Cells(iRow + 1, "B").Value, "yyyymm") Then
        'same
    Else
        'different
    End If

I think you'll want to check to see if the cell has anything in it in the code. 
But I wasn't sure what happens then.
    
"Mike.M" wrote:
> 
> Hi, I have a problem I hope someone can help me with. I have a worksheet for
> employees to enter data. At the beginning of each month I run a macro which
> copies a mastersheet to start the new month. The first column of the sheet
> is for the employee to enter their name. Using code when they move to the
> 2nd column it automatically inserts the date and the 3rd column the time
> both based on whether there is an entry in the first column. I want to call
> my new worksheet macro when the date changes month. I have been trying to
> use ActiveCell.Offset command to look at the date in the row above but
> without success at pinpointing the change in month to call my macro. Can
> anyone please help?
> 
> Mike

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/7/2003 10:04:11 PM
Thanks Dave this works great when the month changes but not  when the year
changes. I found when I  swapped the If year and If month lines around it
ran my macro on change of year but not month. How do I make it do both?

Cheers Mike

"Dave Peterson" <ec35720@msn.com> wrote in message
news:3F83385B.82E129C5@msn.com...
> I'm not quite sure how the activecell fits into your code.  (You don't
usually
> have to .select or .activate ranges to work with them.)
>
> Option Explicit
> Sub testme01()
> Dim iRow As Long
> Dim FirstRow As Long
> Dim LastRow As Long
> With Worksheets("sheet1")
>    FirstRow = 2   'header rows?
>    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
>
>    For iRow = FirstRow To LastRow
>      If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value)
Then
>          If Month(.Cells(iRow, "B").Value) _
>                              = Month(.Cells(iRow + 1, "B").Value) Then
>              'same month/year
>          Else
>              'different month/year
>              'call your new worksheet stuff
>          End If
>      End If
>    Next iRow
> End With
> End Sub
>
> You could even just format the dates the same way and compare that:
>
>     If Format(.Cells(iRow, "B").Value, "yyyymm") _
>         = Format(.Cells(iRow + 1, "B").Value, "yyyymm") Then
>         'same
>     Else
>         'different
>     End If
>
> I think you'll want to check to see if the cell has anything in it in the
code.
> But I wasn't sure what happens then.
>
> "Mike.M" wrote:
> >
> > Hi, I have a problem I hope someone can help me with. I have a worksheet
for
> > employees to enter data. At the beginning of each month I run a macro
which
> > copies a mastersheet to start the new month. The first column of the
sheet
> > is for the employee to enter their name. Using code when they move to
the
> > 2nd column it automatically inserts the date and the 3rd column the time
> > both based on whether there is an entry in the first column. I want to
call
> > my new worksheet macro when the date changes month. I have been trying
to
> > use ActiveCell.Offset command to look at the date in the row above but
> > without success at pinpointing the change in month to call my macro. Can
> > anyone please help?
> >
> > Mike
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
mekim (2)
10/8/2003 3:52:25 PM
Oops.  You're right

Option Explicit
Sub testme01()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With Worksheets("sheet1")
    FirstRow = 2   'header rows?
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    For iRow = FirstRow To LastRow
        If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value) _
         And Month(.Cells(iRow, "B").Value) _
                        = Month(.Cells(iRow + 1, "B").Value) Then
            'same month/year
        Else
            'different month/year
            'call your new worksheet stuff
            MsgBox iRow
        End If
    Next iRow
End With
End Sub


But the format version will do it right (it was ok the first time) and is less
to type!

Sorry about that!


"Mike.M" wrote:
> 
> Thanks Dave this works great when the month changes but not  when the year
> changes. I found when I  swapped the If year and If month lines around it
> ran my macro on change of year but not month. How do I make it do both?
> 
> Cheers Mike
> 
> "Dave Peterson" <ec35720@msn.com> wrote in message
> news:3F83385B.82E129C5@msn.com...
> > I'm not quite sure how the activecell fits into your code.  (You don't
> usually
> > have to .select or .activate ranges to work with them.)
> >
> > Option Explicit
> > Sub testme01()
> > Dim iRow As Long
> > Dim FirstRow As Long
> > Dim LastRow As Long
> > With Worksheets("sheet1")
> >    FirstRow = 2   'header rows?
> >    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
> >
> >    For iRow = FirstRow To LastRow
> >      If Year(.Cells(iRow, "B").Value) = Year(.Cells(iRow + 1, "B").Value)
> Then
> >          If Month(.Cells(iRow, "B").Value) _
> >                              = Month(.Cells(iRow + 1, "B").Value) Then
> >              'same month/year
> >          Else
> >              'different month/year
> >              'call your new worksheet stuff
> >          End If
> >      End If
> >    Next iRow
> > End With
> > End Sub
> >
> > You could even just format the dates the same way and compare that:
> >
> >     If Format(.Cells(iRow, "B").Value, "yyyymm") _
> >         = Format(.Cells(iRow + 1, "B").Value, "yyyymm") Then
> >         'same
> >     Else
> >         'different
> >     End If
> >
> > I think you'll want to check to see if the cell has anything in it in the
> code.
> > But I wasn't sure what happens then.
> >
> > "Mike.M" wrote:
> > >
> > > Hi, I have a problem I hope someone can help me with. I have a worksheet
> for
> > > employees to enter data. At the beginning of each month I run a macro
> which
> > > copies a mastersheet to start the new month. The first column of the
> sheet
> > > is for the employee to enter their name. Using code when they move to
> the
> > > 2nd column it automatically inserts the date and the 3rd column the time
> > > both based on whether there is an entry in the first column. I want to
> call
> > > my new worksheet macro when the date changes month. I have been trying
> to
> > > use ActiveCell.Offset command to look at the date in the row above but
> > > without success at pinpointing the change in month to call my macro. Can
> > > anyone please help?
> > >
> > > Mike
> >
> > --
> >
> > Dave Peterson
> > ec35720@msn.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/8/2003 10:16:17 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Month problem
I have some code where I calculate this month minus 2 month. This goes fine until I get to august. When I use DateSerial to deduct 2 month I get to July, and this makes no sence. I made the following testcode. Sub StrangeMonth() Dim MyDate As Date MyDate = #8/31/2010# MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart(&qu...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Setting up a new e-mail account
I'm trying to set up my yahoo account so that I receive my e-mail messages in my Outlook Inbox. I went to Accounts, and servers, and put in mail.yahoo.com for incoming mail (POP3) and smtp.yahoo.com for outgoing mail (smtp). When I click on the send/receive button I get an error message. Dave <anonymous@discussions.microsoft.com> wrote: > I'm trying to set up my yahoo account so that I receive > my e-mail messages in my Outlook Inbox. I went to > Accounts, and servers, and put in mail.yahoo.com for > incoming mail (POP3) and smtp.yahoo.com for outgoing mail &...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

Changing a profile on Microsoft Outlook 2003
I set up two profiles through the control panel and directed Outlook to prompt me for which profile to use each time it was opened. But now it skips the prompt and goes straight to one of the profiles. I need to restore that prompt, but it won't respond to the instructions in the control panel Mail dialogue box Hi Chris, did you get the same behavior after a restart of the computer? You could try "Sart/run/fixmapi.exe" (you don�t get any confirmation message) and restart the computer again. If this wouldn�t wotk, I would create a 3rd (test) profile. Maybe Outlook don�t ...

creating a backup on 2002 for Money 98
I am helping a friend who has 98. I need to make a backup of info on my 2002 for him to use on 98. Any suggestions as to how to do this? M98 can't read any file written by M02 besides .QIF import. M02 can't write any file readable by M98 except for .QIF export. Sounds like QIF export/import is your only choice. I suspect you will find this doesn't do what you want. "Carlotte" <Carlotta41@discussions.microsoft.com> wrote in message news:015b01c3d2fa$bbf8fd60$a101280a@phx.gbl... > I am helping a friend who has 98. I need to make a > backup of info on...

Macro to change default setting on startup
I am in need of a macro that can change a default setting in excel and for it to run on startup The task is: Tools Options General Web Options Files uncheck Update links on save Below is the recording of the macro: With ActiveWorkbook.WebOptions ..RelyOnCSS = True ..OrganizeInFolder = True ..UseLongFileNames = True ..DownloadComponents = False ..RelyOnVML = False ..AllowPNG = False ..ScreenSize = msoScreenSize800x600 ..PixelsPerInch = 96 ..Encoding = msoEncodingWestern End With With Application.DefaultWebOptions ..SaveHiddenData = True ..LoadPictures = True ....

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...