Name Worksheet With Date Residing In A Cell
Nov 21, 2009
I am trying to create a macro that will copy a sheet, move it to the end and rename it it with the next date of the value of the date in a particular cell.
I can do the first part no problem, however I am stuck with naming the sheet with the date which is the next day from the date value in a cell.
i.e. If the date value in A1 of the copied and moved sheet is 11 Nov 2009 I need the next sheet to be called 12 Nov 2009 in that format (dd mmm yyyy)
This is the code that I have come up with so far, but it gets stuck at the bottom line
Sub Macro3()
Dim WS As Worksheet, WB As Workbook
Set WB = ActiveWorkbook
Set WS = WB.ActiveSheet
WS.Copy After:=Sheets(WB.Sheets.Count)
WS.Name = Format(WS.Range("A1" + 1).Value, "dd mmm yyyy")
End Sub
View 7 Replies
ADVERTISEMENT
Dec 11, 2007
I have a local area network with a couple of hundred computers which share one internet connection. Each user has a CPE (customer premise equipment) with an assigned (by me) IP. This CPE connets to the users equipment via CAT5. My responsibility is to provide internet. Sometimes users call and say their comupter cannot access the internet. I need a quick way to see if the problem lies with my network or if the problem is with the customer's computer. It seems to me that if I could open my spreedsheet with all the network connections and user data and simply ping their IP I would see if the problem is with my equipment or the users.
Column A of my spreedsheet has the actual IP addresses: 192.168.1.1 thru 192.168.1.254 and 10.0.0.1 thru 10.0.0.254 but not all are currently being used. Each row has distinct user account information. I have created a shortcut, named it PING106.bat and listed the target as %windir%system32ping.exe 192.168.1.106 which I can click on and it runs. Next I inserted a hyperlink in A:107 and it does work (it brings up a DOS screen and pings 192.168.1.106 three times then closes the DOS screen)... But there must be a better way. I don't want to create hundreds of shortcuts and insert hyperlinks to specific cells one at a time. It would be nice if I could click on a cell which contains an IP and know if that particular IP is up and reachable on my LAN.
View 4 Replies
View Related
Apr 20, 2014
Here is what I am trying to accomplish - note that I need the formula to exist in an unrelated cell - in this example I use A6
Cell A1 is a drop down with "Proposed" and "Actual" as the choices.
Cell A2 allows user input ("Proposed") until the "Actual" value is known.
The "Actual" value is pulled from a different workbook (Workbook2).
The "Proposed" number can change many times before the "Actual" number is pulled in...this means that I cannot place the formula into A1 as it will be overwritten when the user puts in their "Proposed" number.
I would like to place the formula into an unrelated cell to continue to allow A1 to be user edited.
Formula would reside in A6 and the statement I am trying to execute would be something like:
If(A1="Actual", A2=Workbook2.xlsx]Actual!A2)
I have tried the formula in it's simplest structure to confirm it works before I reference workbook2 but it doesn't seem to work (formula resides in A6): =IF(A1="Actual",A2="hello"). I just get a "FALSE" result in A6. If I remove the = before IF excel does not recognize the statement as a formula.
View 1 Replies
View Related
May 24, 2007
I am trying to do, is paste a word in front of text that is already residing in cell throughout an entire column, and then automate this process by creating a macro that will do the same thing for me throughout an entire column. To best explain this, it woudl be like if you have a column 100 rows/cells long, and every cell already contains data. I need to insert something in front of what lies within each cell.
View 9 Replies
View Related
Jun 26, 2007
It can be done using named ranges. Name the range of the source list and set the validation to List =name. Sheet location does not matter.
I can get it to work using Indirect - but this puts a limitation on the process that the source must be open. Mike, perhaps you could explain more fully how you are achieving this?
View 9 Replies
View Related
Aug 8, 2007
My goal is to take a list of times which are exported from a database into 1 cell and change the string in that cell to become a function that adds all the times....
View 9 Replies
View Related
Jan 9, 2014
I have a workbook that contains 14 worksheets. I have been looking for vba code that hides 3 of these sheets according to a date value. The date value is situated on sheet 1 cell B7. The 3 sheets I wish to hide are sheets 2, sheets 3 and sheets 4. These 3 sheets are required to be made unhidden until 3 days before date in sheet 1 cell B7. From the date value I want the workbook to hide the 3 sheets and can only be unhidden if a correct password is used to unhide these 3 sheets.
View 4 Replies
View Related
Apr 12, 2009
I am trying to reference a cell on another worksheet, the cell is formatted to be a date. when i put in the following formula =Sheet2!A1 and i don't input a date into A1 on sheet 2 the cell containing the formula shows the date 00/01/1900, is there any way of stopping this showing, ideally the cell would be blank until I inputted a date into A1 on sheet 2
View 9 Replies
View Related
Feb 1, 2013
I have a worksheet that is protected with data entry cells unlocked.
1. Unprotect worksheet
2. Lock all cells in the worksheet and then protect.
3. After a date in a cell in the worksheet.
I'm a newbie to vba and at least know vba is the only way. And this need to be automated.
View 1 Replies
View Related
Feb 6, 2008
I have two workbooks, one a database that accumulates data over time, and the other a monthly report that needs to be populated based on information from the database. When moving data between the two, the following works fine (snipped a lot for ease of reading):
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook). Sheets("Sheet1"). Range("A1").Value = month(Cells(282, 5))
As you can see, it simply puts the month from a cell in the database into the report workbook. However, if I change Range("A1") to Cells(1,1) I get a runtime error:
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook).Sheets("Sheet1").Cells(1, 1).Value = month(Cells(282, 5))
I can generally work around this issue by changing the active workbook, but doing so is a big pain. Is there any way to use the second bit of code without popping an error message?
View 3 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Jan 26, 2014
I have a userform with numerous text boxes. One textbox is used to input a date with the following code
[Code] .....
When I have completed my userform I want to export this date as a date into range(a2) so I can then use the filter function to filter by month or year. THe problem I am having at the mo is that in the userform it is formatted as dd mm yy, but when I export it from userform it changes to mm,dd,yy. So if I entered 12th January 2014 it changes it to 1st December 2014.
A workaround is to export as text but then I use the filter by month year function so would prefer to avoid this.
View 1 Replies
View Related
May 23, 2014
Hide Columns based on Date value in cell (Worksheet Change event)Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.
View 2 Replies
View Related
Jul 11, 2012
I have to complete information providing dates and names for a long number of individuals. Is it possible to set up the spreadsheet so that if you enter a date next a persons details in on worksheet their information is automatically updated in the relevant ones?
View 2 Replies
View Related
Jun 25, 2014
I have a lost and found log that has 2 worksheets: (1) Unresolved and (2) Resolved. Data is entered into the Unresolved worksheet when something is lost or found. When a date is entered under Date Claimed / Sent to Capitol Police (column I), I would like the data in that row to be removed from the Unresolved worksheet and automatically inserted in the next blank row of the Resolved worksheet. I know very little VB,
Date Reported
Lost or Found?
Item Description
Name of Person Reporting Item
[Code]...
View 5 Replies
View Related
Jun 17, 2006
I am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
View 3 Replies
View Related
Dec 3, 2009
I have dates (12/3/09) in columns A, G, K and P.
There may or may not be a date in any of these cells.
How do I find the very last date in the worksheet.
I want to do this with code.
View 9 Replies
View Related
May 9, 2012
I have the following columns that return cover period dates.
Start of Cover PeriodEnd of Cover Period01-Nov-0631-Jan-0701-Jul-0831-May-0901-Dec-0930-Jun-1001-Jan-1131-Dec-10
I however need a macro that will delete the cell contents if the "Start of Cover Period" (column AK) date is > than the "End of cover period" (column AL) date.
Thus it must compare ak2 with al2 and if ak2>al2 then clear both cells. if AK2
View 2 Replies
View Related
Apr 18, 2014
I am working on an Inventory Spreadsheet. I have need it to date time stamp that willpopulate a different cell /field when text are entered in a different cell/field and will not change all the previous dates entered on the spreadsheetevery time I re-open the spreadsheet. I formula I am using is =IF(E3<>"",IF(D15="",NOW(),D15),"")and it changes each time I open thesheet to do the inventory so I do not know the date of the last inventory. Iwant to capture the actual date that the inventory was completed in thesecond/different cell?
View 9 Replies
View Related
Feb 14, 2012
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies
View Related
May 3, 2013
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
View 5 Replies
View Related
Mar 27, 2007
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
View 7 Replies
View Related
Jan 28, 2010
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
View 12 Replies
View Related
Jun 5, 2009
I have had a look via search for similar but I'm not good enough to recognise whether other threads bear any relation to my query.
I have a small spreadsheet with basic conditional formatting (my level!) where we record agency staff for eleven months service to enable us to ask them to take a four week break.
I want to add a formula or similar to automatically roll the date forward eleven months when we amend the re-commencement date on their return.
E.g. we type in A1 01/01/09, B1 updates to 30/11/09
Is this possible?
View 10 Replies
View Related
Aug 13, 2014
I have a 'Summary' worksheet which is shown as:
August
September
October
Example 1
1
4
5
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
View 1 Replies
View Related
Sep 27, 2009
Is there a simple way using VBA to find a date in the format of dd/mm/yyyy? I have a range on every sheet from A1 to IQ1 with dates already entered. I want to add this vba to some other code so the user can go straight to the date entered on the worksheet.
Using the edit find, it can find dates easily. Can this function be replicated through VBA?
View 9 Replies
View Related
Jan 15, 2008
I need a formula that will look up to find a specified date on another worksheet, and input the data in the cell that is 4 columns to the left of it. Can this be done?
View 9 Replies
View Related
Dec 12, 2002
How do i enable a date and time function to appear on the worksheet - so it updates constantly (either minutes or seconds). If this isn't available can anyone tell me website that contains the UK date and time and updates it constantly, I have tried the web query with [url] but for some reason the date doesn't come up.
View 9 Replies
View Related
Feb 13, 2008
Iwant to create a diary using excel. Iwant a seperate tab for each day of the the year.Is there an easy way to set this up rather than inserting individual worksheets.possibly using vba
View 5 Replies
View Related
May 20, 2014
find the attached sample file and why i can't make whole dates in a same fprmat,i tried different ways but didn't work.
View 4 Replies
View Related