Auto Open Macro To Find Correct Week Tab & Day/date In 52 Week Worksheets
Jan 19, 2010
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
View 9 Replies
ADVERTISEMENT
Dec 11, 2013
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
View 3 Replies
View Related
Jan 20, 2007
I need a macro to find the date at the end of the week.
Ie today is saturday the 20th january - Macro would return the value 21/01/07.
Or IF the date today was the 24/01/07 - Macro would return the value 28/01/07.
View 9 Replies
View Related
Nov 24, 2009
I have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
View 3 Replies
View Related
Apr 17, 2014
I've got a column with multiple dates and a lookup sheet with the weeks (start date/end date). What I'm trying to do is :
1) check date
2) look it up in the lookup sheet (where it falls between start data/end date)
3) once i find where it falls, return the corresponding week name/number
I've seen a solution using the Median function but I'm not here it would work in my case. link here
I've attached a workbook : Date_Sample.xlsx‎
View 12 Replies
View Related
Apr 22, 2009
In the attch file i have the date coulumn from this date column i need to calulate the month & week no. (like WEEK1,WEEK2..)
The Week ( Monday to sunday) which need to be calculated is the week no. in the given month
like for month of April the week1 is print in the week column for 6april to 12 april date and Week2 print for 13 April to 19 april
View 14 Replies
View Related
Sep 27, 2009
I've been racking my head on this one.
I need a function where for a given date will return the date of a specified weekday in the previous week.
Example: If today is Friday Sept. 25th 2009 and I want to find the date of
the Wednesday in the previous week I would need something
like DATEPREVIOUSWEEK("09/25/2009", vbWednesday)
View 9 Replies
View Related
Mar 9, 2014
I may be making this more complicated than it needs to be but I can't seem to figure it out. The goal of this spreadsheet is for General Managers of a restaurant to give feedback about food deliveries. These deliveries usually come on Monday, Thursday, and Saturday, but could for a list of reasons come on any day of the week. I want to automatically populate as much information as possible without requiring GMs to remember and enter dates.
A1 is =TODAY()
A2:A5 is a drop down where the GM selects the day of the week the delivery arrived. i.e. MON, TUE, WED, etc. I want the corresponding date of the past week to auto-populate in this cell (or even the adjacent cell if necessary) once the day is selected. For example, if today is 3/8/2014, when MON is selected in A2, I want the cell to automatically add "3/3/2014," making the entire cell read "MON 3/3/2014."
So if said GM is filling in this spreadsheet on a Saturday, and I want to display the date of the previous Monday, I need to find the day of the week of A1 (WEEKDAY function), and make A2 = A1-5.
If today is a Friday, it would be A2=A1-4, and so on for the 7 days of the week.
Then I would need to do all of this for if a Tuesday is selected in the drop down box, meaning IF(Saturday)Then A2=A1-4, etc...
I was trying to string together IF statements like this:
=IF(WEEKDAY(TODAY()=7),A1-5), IF(WEEKDAY(TODAY()=6),A1-4)
It works when I do only the first IF statement, but when I add another it returns #VALUE. I thought that excel would find the first true value and stop evaluating.
View 2 Replies
View Related
Feb 17, 2014
If a sale was made between 2/09/2014 - 2/15/2014 return the date of the following weeks Friday. In this case 02/21/2014.
View 3 Replies
View Related
Jul 19, 2014
I have two processes that has to run depending of the week number of the year. So even weeks has to run "sub 2" and odd weeks has to run "sub 1".
Weeks with number 1,3,5,7,9,11,13,15 etc has to call "sub 1" and weeks 2,4,6,8,10,12,14,16 etc has to call "sub 2".
The filenames consist of "name_name_YYYYMMDD.xls" (example: SteveSmith_Runningshoes_20140719.xls)
So vba needs to calculate the week number by reading the YYYYMMDD of the filename and then call the correct sub.
View 6 Replies
View Related
Jan 8, 2010
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
View 9 Replies
View Related
Mar 14, 2014
I have an excel sheet wherin there is a column that has the data where in the dates are displayed and many other columns.
I get this excel every Thursday so i want to filter this date column in such a way that it give me the data related to the date of the previous week only yet there is a catch here. When i say previous week i mean.
Suppose today is 03/14/14 then i want the data from 03/07/14 till today ie Last week friday to this week full( so cant use Current week option) and then paste it in a new sheet.
I tried the Record part but in that it is taking a hard coded value as i am selecting the date myself. I dont want to change the date manually every time.
this was the macro that was created
Code:
ActiveSheet.Range("$A$1:$BX$58").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, "3/10/2014")
Range("A59").Select
ActiveCell.FormulaR1C1 = "=COUNT(R[-4]C:R[-1]C)"
Range("A60").Select
View 8 Replies
View Related
Dec 2, 2012
I have to generate daily report in which data is fiscal week format. We need to show Fiscal week in actual month and day format. create a macro so that for every fiscal week anywhere in the sheet, it automatically converts fiscal week to month and days of the week
For example: WK 1 OCT FY2013 get converted to Oct 1-7
WK 2 OCT FY2013 coverted to Oct 8 - 14
WK 3 OCT FY2013 get converted to Oct 15 -21
WK 4 OCT FY2013 to Oct 22-28
WK 5 OCT FY2013 to Oct 29- Nov4
View 4 Replies
View Related
Apr 21, 2007
I am trying to code a macro call where in once the file is saved with the current week start date all the other macro's should be disabled. Since am having lot of data and report sheets which needs to be added and deleted in the run. It causes me problem when I try to open it again to view the results. For example I have a sheet named apple and another one applereport. I have made my code to delete apple since it is a data sheet. I want apple report to have all the other macros disabled once its been renamed to week start date for now it is 16 Mon 2007.
View 3 Replies
View Related
Apr 6, 2007
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 Replies
View Related
Aug 6, 2008
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
View 9 Replies
View Related
Mar 6, 2010
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
View 9 Replies
View Related
Oct 4, 2012
Any 'date' or 'day' formula that will automatically insert days of the week in a column? I am attaching a sample.
I want to be able to use a drop-down menu to insert a day of the week (e.g.: Sunday) in cell B2 and have the succeeding days (Mon, Tues,Wed, Thurs, Fri, Sat) populate cells C2~H2 in sequence.....
View 9 Replies
View Related
Jun 16, 2014
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
View 6 Replies
View Related
Feb 5, 2009
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies
View Related
Dec 4, 2013
I am trying to fill a row with abbreviated days of the week. example type Wed in cell A1 and cells B1 through Z1 change to the appropriate day Thu, Fri, Sat etc. I know how to do it with the fill off the menu but want to be able to change Wed to Thu and the same cells will change.
View 7 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
Feb 18, 2009
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)
View 4 Replies
View Related
Nov 18, 2013
How do I auto fill a series of 2014 dates in the row below the weekdays? I have a row C5 that has a series of auto fill weekdays successfully for 2014. I can't seem to auto fill the weekdates in the row below. I have to make manual adjustments for each Monday, and for the correct month ends, etc.
View 8 Replies
View Related
Dec 20, 2009
Need a function that returns the days from the last week of a month?
View 3 Replies
View Related
Apr 11, 2007
Is it possible to format in a macro such that dates that you extract from another workbook can be reformatted in such a way: mm/dd/yyyy Day, for example, :03/23/1990 Sun?
View 3 Replies
View Related
May 22, 2014
In column A, field 1 i want to post the text "Week 2, 6-10 Jan" with 5 days in the week. Field 2, "Week 3, 13-17 jan" And then i just want to pull this field down in the column, but then the week number changes and the date interval.
View 1 Replies
View Related
Jul 9, 2014
. In Cell B1 I can have =Weekday(A1), which returns 4 in Cell C1 I can have =CHOOSE(B1,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") this returns the day of the week for the chosen date.
However, I wish to remove step 2 and use just A1 and C1 and if I use =IF(WEEKDAY(A1)=1,"Sunday",CHOOSE(A1,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")), which returns the dreaded - #VALUE!
View 1 Replies
View Related
Jun 4, 2008
I have this column with a value like 04/06/2008. I will want another of the column to auto generate the date for the start of the week like 02/06/2008 which is a Monday.
View 14 Replies
View Related
Jul 31, 2009
I have a list of dates in Excel that I would like to pull the day of the week it was on: for example, cell A1 has '1/03/09' and I would like it to display in B1 what day of the week that date was.
View 3 Replies
View Related