Renaming Workheet As Date Format (cell Reference) Using VBA
Mar 2, 2007
I download & import CSV files/ sheets from bank, visa accts etc & I am trying to automate the processes I use including ensuring I (& others) will use the same naming conventions eg visa "yymm" = V0701, Direct Cedits = DC0701 etc. I am having trouble renaming or ensuring the active sheet is named according to the date in A3 eg in the example I want the sheet to be renamed V0702 as the date in the cell is 02/02/07 "dd/mm/yy" format ( imperial system). In BOLD is what I have & the renaming is messy becuase I searched the forum, Walkenbach's bible & help menu.
Sub VisaMonthlyAcct()
Dim shtName As String
Dim newName As String
ActiveSheet.Activate
'ActiveSheet.Name = shtName
newName = "V" & Format(DateValue("A3"), "yymm")
'Old name for future reference = Visa0701 (2)
ActiveSheet.Name = newName
Worksheets(shtName).Name = "V" & Format(Range("A3"), yymm)
'Worksheets("Visa0701 (2)").Name = "V" & (FormatDateTime(Range("A3"), yymm))
'To select Date column & format
Range("A3:A66").Selection.NumberFormat = "dd".............
View 2 Replies
ADVERTISEMENT
Feb 22, 2012
Cell C3 has "Joe"
Cell X44 is a VLOOKUP that retrieves "Joe"
Let's say Joe goes on vacation. The workbook user goes in and puts a blue fill in C3. Cell X44 would also need to change automatically.
What's needed to make this happen?
View 1 Replies
View Related
Jul 3, 2009
I am preparing a model for a client that runs excel 2003. I need to change the format of a range based on the value in a cell (reference cell) that is outside the range to be reformatted.
Reference cell is a named cell with the value calculated with a Match() function, the only possible values are (1, 2, 3).
I need the VBA code to retrieve the reference cell value and apply one of three formats to the range. I believe the code for that part is something like:
Private Sub Format_Font()
Set vrange = Range("d63:r63")
Set refrange = Names("ind_agentfee").RefersTo
Set refrange = Mid(refrange, 2)
On Error Resume Next
If refrange = 1 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Else
If refrange = 2 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Else
.Style = "Percent"
End If
End Sub
I am having problems getting the proper opening statements and declarations correct and also get an "Else without Then" error with all combination's that I am attempting. I am sure that the code is mess now as I started with a recorded macro then modified it multiple times while researching the web.
View 9 Replies
View Related
Jan 23, 2010
I'm trying to get the bars of a chart to change colour depending on the selection from a drop down in a cell. The drop down is validated to be one of four options which changes the figures displayed in the graph using index matching.I would really like the colour to change to represent what the option being selected is.
I used some VBA code before for changing the cell colour depending on the selection made to get over the 3 condition limit. Could this be easily edited to change a bar graph colour? All of the references I can see on line refer to changing the colours of bars based on their value, I don't want to do this, more an independant cell value!
My previously used code is below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColor As Integer
If Not Intersect(Target, Range("d9:d129")) Is Nothing Then
Select Case Target.Cells(1)
Case "Commercial Final State"
iColor = 49
Case "Commercial Interim"
iColor = 31
View 7 Replies
View Related
Feb 21, 2014
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
View 4 Replies
View Related
Aug 1, 2008
I have been struggling with my Excel 07 Date format. Nearly every time I perform a calculation, enter in a value etc in a spreadsheet my answer is returned as a date. So for example if I enter in a cell: = 5 - 4. I get the answer "1-Jan-1900" instead of 1. I have to manually set the formats to general or number if I want the correct format. The setting seems to be the default one and occurs for all workbooks I open. What can I do to change this.
View 5 Replies
View Related
Apr 6, 2012
Basically I have a row of dates
so,
B1 - 01/04/2012
B2 - 02/04/2012
etc...
And I have tabs for each of those dates aswell.
Basically I want to pull some figures out of the tabs but use the dates in the reference.
So i need a formula that will look at the date in cell B1, use the date to find the tab and then say find the value of cell G12.
View 4 Replies
View Related
Jul 22, 2011
I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=9/9/2011",NextPaymentDueDate,"<=9/22/11")
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
A2 contains 9/9/2011
B2 contains 9/22/2011
=SUMIFS(NextPmtAmt,NextPaymentDueDate,">=A2",NextPaymentDueDate,"<=B2")
but it doesn't work.
View 6 Replies
View Related
Aug 10, 2013
I'm using the following formula which is entered by using VBA on the worksheet change event but the cell reference 'A2' is not changing for each row, i.e. A3, A4 etc.....
Code:
=MAX(IF( 'Device Use - 4 month Period'!$A$2:$A$20000=A2, 'Device Use - 4 month Period'!$C$2:$C$20000))
View 4 Replies
View Related
Jan 16, 2008
Using cell contents in formula to Populating cells with values from closed workbooks
I am trying to create a summary table of data (e.g. orders) from previous weeks. In the table I am creating, the left hand column contains the Monday date of the week to be summarised along the row. I want the cells to show the values from other spreadsheets when those spreadsheets are closed, but I want the formula to pick up the date of the week that is in column A. I have attached a sample spreadsheet to try and make it clearer.
View 2 Replies
View Related
Nov 11, 2009
I'm trying to link two different budgets into a third spreadsheet. The final step I'm attempting to accomplish is have an if-and statement reference just the month from a date-formatted cell. One of my departments tracks their budget on a daily basis, i.e.
11/2/2009, 11/3/2009, etc. rather than just 11/2009. I want the if-and statement to return the budget from all days in a given month so i can apply the formula to the whole year, broken down monthly too. I saw a question similar to this one but didn't see any responses so I'm not sure if it can be done.
View 6 Replies
View Related
Aug 24, 2009
I have renamed a cell to "final"
Now im trying this:
Range("A3:final-1").Select
But that doesnt work, I want the cell just above final.
And I cant refer to the cell above directly due to some factors.
So how could say final - 1?
View 7 Replies
View Related
Jun 25, 2014
I have this formula counting down days until my next fire drill:
[Code].....
However... Every time the drill is done I need to change the date in the formula for the countdown. That I can have it reference a cell instead?
My initial idea was a formula like this:
[Code] .....
(however this code doesn't actually work ...).
View 5 Replies
View Related
Jun 11, 2009
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View 2 Replies
View Related
Aug 29, 2013
I have the below code to get the difference between todays date and the date located on sheet "requests" "B3". The code returns the value: -28040498
Code:
Dim MyODateRng As String
Dim MyODate As String
MyODateRng = Sheets("Requests").Range("B3").Value
[Code]....
So i gues with the first code there is something wrong with the date formating. But i do not understand why the second code does not return simply return the value: 1.
View 7 Replies
View Related
Apr 3, 2014
I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
View 2 Replies
View Related
Mar 19, 2009
Basically I just need the worksheet tab to be renamed based on the date value in cell B2.
Short and sweet.
View 8 Replies
View Related
Jan 22, 2012
Sub AddSheet()
Dim ActNm As String
With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
[Code] ..........
Every time a new sheet is added I need cell ("A1") Name to be "Date Code".
View 9 Replies
View Related
Jun 19, 2014
I'm trying to make a vba that would save the file with the end date that will reference to a certain cell that user selects it. Working Sheet is a name of a File, and Current Sheet is a name of the panel where user will select the date.
ActiveWorkbook.SaveAs Filename:= WorkingSheet("Control Sheet").Range("D3").Value & Format(Date, "yyyymm") & ".xlsm", FileFormat:=51
But here i seem to missing the location
And I tried this one:
ActiveWorkbook.SaveAs Filename:="X:Work2014WorkingSheet& Format(Date,"yyyymm"). & ".xlsm" _, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
but it keeps popping up as 'expected end of statement' for yyymm?
View 8 Replies
View Related
Feb 20, 2008
i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.
so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.
any ideas would be appreciated. know how to do this in VBA but no clue with formulas.
what i am trying to do is concatenate part of a file name and a cell value which contans a date
=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)
View 3 Replies
View Related
Dec 12, 2009
Sheet4
ABCDE1Source DataDesired Result
Include these extensions: Exclude these extensions:2OLD.FILENAME.rar.htmNEW.FILENAME.rar
.part*.rar.htm3OLD.FILENAME.part01.rarNEW.FILENAME.part01.rar
.rar.html4OLD.FILENAME.part02.rar.html.htmNEW.FILENAME.part02.rar
.avi
5OLD.FILENAME.txtNEW.FILENAME.txt
.zip
6OLD.FILENAME.zip.htmNEW.FILENAME.zip
.txt
7OLD.FILENAME.aviNEW.FILENAME.avi
.7z.*
8OLD.FILENAME.7z.01.htmNEW.FILENAME.7z.01
9OLD.FILENAME.7z.002NEW.FILENAME.7z.002
I'm looking for a formula which can take my original data (column A) and "transform" it into what you see in column B.
I am transferring large amounts of files from various servers to my ftp and back out again. My original data are url filenames which of course, are named differently and not how you see it in my example.
The filenames themselves are preceeded by website names and varoius folder/path names. I edit the links to include various Usernames and Passwords and at the same time, rename the original filenames. Then I transfer the files to my ftp.
Most of the files have a ".part*.rar" extension at the end, so my formula has only included this scenario. Rather than manually editing my formula everytime a different extension is present (about 20-30% of the time), I would like to find a way to automatically look at a table/list of possible extensions (D2:D7) which are to be recognized for the sake of finding the end of the filename itself, so that I can rename (SUBSTITUTE) the original filename to that of my choice (cell D13) and of course, also include the original extension(s) of the source data (keeping this is important, especially in the cases where ".part*.rar's" are present). The list of extensions in cells E2:E7 are to be excluded from the new filename.
My present formula works fine for the editing of the username and passwords for various servers and for finding the beginning of the filename in the original url. It also works fine for finding the end of the filename (for the sake of renaming it), but only in cases where a .part*.rar" is present.
View 9 Replies
View Related
Nov 19, 2008
Dates:
------
November 1, 2008
1 novembre 2008
In the formula bar, these formated dates appear as 11/1/2008.
What I need to do is copy the formated dates and paste them into another column so that they show up as November 1, 2008 or 1 novembre 2008 and not as 11/1/2008.
Does anyone know of a non-macro formula or function that can do this? Is there a "paste as is" function?
View 9 Replies
View Related
Oct 11, 2008
I need to create formulas that reference a single date and output date ranges. The objective is to have a person input a Monday date in any given month and receive a four weeks out worth of dates and ranges. For example: In a lone cell, the person inputs 10/13/08. Automatically, the sheet produces the next full week range: October 19 – October 25 in a single cell and also produces a cell for each date. Example: Sunday 19, Monday 20, Tuesday 21, etc…. It should look like:
Monday Date:
10/13/08
October 19 – October 25
Sunday 19
Monday 20
Tuesday 21
Wednesday 22
Thursday 23
Friday 24
Saturday 25
and then repeat for three more weeks. I thought I had it figured out until the month changed. The dates continued in October instead of adding a month. This report will be ran weekly, so simply adding a +1MONTH to some cells will not benefit me as I’ll have to change the formula every week. I want the formula to compute the data without any manipulation over the next several years. The only change will be the Monday date.
View 2 Replies
View Related
Nov 23, 2006
I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values.
Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.
View 4 Replies
View Related
Jan 26, 2012
What I have done is create a macro in Excel and then opened up the VBA editor to make a lot of changes to the initial macro. I am reformatting a whole lot of separate spreadsheets over many workbooks.
Here is the code that is generating the error (Run-Time error '438', Object doesn't support this property or method):
Code:
Range("E2").Select
Selection.Format = "long date"
With Selection
.Font.Size = 11
' .Format = LongDate
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
As you can see, I first tried the ".Format = LongDate" and did put LongDate in "" which did not work, then I tried the "Selection.Format = ..." after the "Range" statement, and that did not work either. So you will actually know, I am trying to set the format for the cell "E2" to a Long Date format.
View 7 Replies
View Related
Apr 13, 2009
I have a problem when i try to format a cell. i download data from a website and it gives me the date and time in the first column, ex. 1/1/2009 5:00:00 pm. when i format the cell to date, the time is erased and the date remains which is what i want, ex. 1/1/2009. however, when i click on that cell or any other in that column, the display above, or "insert function" line still show the contents of the cell with the date and the time, ex. 1/1/2009 5:00:00 pm. is there any way i can change that so the time is erased and only the date remains, ex. 1/1/2009 when i click on the cell?
View 9 Replies
View Related
Nov 23, 2006
I am facing a problem with number formats. I have downloaded some data from a database. In the data, there are some date columns in MM/DD/YYYY format. In a seperate workbook, I have copied that data as values. Now when I am trying to change the formats of the dates to d-mmm-yy, they don't change. The formats are not not changing to any other format. I even tried changing the formats using VBA, but it did not work.
View 2 Replies
View Related
Feb 2, 2008
I am pulling reports out of a non-windows based program. This program spits out the data directly into an Excel spreadsheet, with but a click of a button. I imagine that the designers of this program assumed that this would be useful, but it's actually proving to be quite irritating.
The Date format they have chosen for their program is NOT the date format that Excel uses, but it resembles it enough to cause serious problems. The original program tracks dates in a "dd/mm/yy" format, rather then Excel's "mm/dd/yy". To my VAST personal irritation Excel does NOT convert this into Text (which could be
manipulated by snipping it into the correct format) but instead converts it into it's own Date format, leaving me with a useless and incorrect serial number as the only data.
I cannot interact with the exporting process at all, and as the program exports DIRECTLY into Excel I cannot manipulate the data prior to import (which would be one relatively easy way of avoiding this problem). I am left trying to figure out a way to reconstruct an actual date out of this mess. Is there any way to convert a date serial number BACK into text?
View 2 Replies
View Related
Jun 7, 2013
I'm trying to copy the value of one column to another, column A has date and time in this format: 6/1/2013 6:35:00 AM and I'm trying to get ONLY the dates to another column so that when I do my pivot, I can sort it out by date. I tried =A1 and I can format the column to how I want it but the value of the cell still remains in date&time. Is there any formula I can use? or should I change anything in my pivot table?
View 4 Replies
View Related
Jan 8, 2009
just wondering if its possible to format a cell to display date and week number and if so how to go about it
eg
04/01/09 week 1
View 7 Replies
View Related
