Computer Support Forum

Moving rows with Date field from one worksheet to another

Question: Moving rows with Date field from one worksheet to another

Hello,Have a Excel file with multiple sheets , each sheet is identified by the persons ID numberSee attached file.Here i need to copy data from FILE1 in each sheet to FILE2As you see in file1 that data for each person is scar tared , that is date are up and down ...that data need to be moved to file2 where already date from 21 oct to 20 nov is marked.THANKS

Relevance 100%
Preferred Solution: Moving rows with Date field from one worksheet to another

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Answer: Moving rows with Date field from one worksheet to another

Quotei need to copy data from FILE1 in each sheet to FILE2I'm wondering about the wording of the quoted part of your post. I suspect it would be better worded like this: i need to copy data from each sheet in FILE1 in to corresponding sheets in FILE2. Is that correct? Are FILE1 and FILE2 names of separate Excel files?

14 more replies
Relevance 107.3%

Hello,Have a Excel file with multiple sheets , each sheet is identified by the persons ID numberSee attached file.Here i need to copy data from FILE1 in each sheet to FILE2As you see in file1 that data for each person is scar tared , that is date are up and down ...that data need to be moved to file2 where already date from 21 oct to 20 nov is marked.THANKS

More replies
Relevance 95.12%

I have produced a worksheet called 'Records' with a different customer record on each row. When a case is cleared I select 'Y' in column D. Is it possible to set up a macro that once activated moves all cleared cases to worksheet 2 called 'Cleared'. Ensuring it doesn't delete what is already on worksheet 'Cleared'.

Answer:Moving rows from 1 excel worksheet to another

Right-Click the sheet tab for RecordsPaste this code into the pane that opensEnter a Y in Records, Column D
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Was change made to Column D?
If Target.Column = 4 Then
'If yes, was a Y entered?
If Target = "Y" Then
'If yes, Store Row number, Determine next empty Row in
'Cleared sheet, Move Row, Delete Row
delRow = Target.Row
nxtRow = Sheets("Cleared").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Cut Destination:=Sheets("Cleared").Range("A" & nxtRow)
Rows(delRow).EntireRow.Delete shift:=xlUp
End If
End If
Application.EnableEvents = True
End Sub

3 more replies
Relevance 81.18%

Hi. I need help! I created a macro to copy two rows from worksheet to another. I need it to be able to repeat mulitple times. I need it to always paste to the last available blank row. The problem is that it will only run once. I try to run it again and it is selecting rows further down on the worksheet that has the rows I need copied.
this is what I have so far....
Sub copy2()
' copy2 Macro
' Keyboard Shortcut: Ctrl+w
Sheets("Orders In Production").Select
ActiveCell.Offset(0, 0).Select
End Sub

Answer:macro to copy two rows from worksheet to another worksheet to the next open row

To select the last filled row I suggest the following

The last available blank row is:

Sheets("Sheet2").Range("A" &rows.count).end(Xlup).row + 1).select

This locates the last filled row and then goes one down.

Try this to checkit
Sheets("Sheet2").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select


Sub Test
Sheets("Sheet2").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
End Sub

1 more replies
Relevance 72.98%

I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.

Answer:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

16 more replies
Relevance 70.52%

I use due dates on my Outlook tasks. When I customize my Tasks list (in the Calendar view) to group by due date, I just get too many groupings (one for each due date!). I would like to know if I can create a custom field called 'Due Month' and/or 'Due Week', extracted from the 'Due Date', so I can view my tasks by what is due during a given week or month. If the answer is yes, any suggestions on the technique I would use would be greatly appreciated.
Thanks in advance,

More replies
Relevance 69.7%

I need a date field changed into a text field of YYYYMMDD so for example the date of birth field is 3/17/1953 and I need that converted to 19530317 in the Excel spreadsheet. Thanks.

Answer:I need a date field changed into a text field of YYYYMMDD

under the format options.

5 more replies
Relevance 68.88%

Urgent need to hear from you more knowledgeable members (i.e., everybody!) as to exactly how to add rows which was so simple in earlier Excel versions, but now is bafflegab.

Even after several efforts to comply with what follows when at INSERT, all I got was to move cell I'm in one to the right with all other cells in the row following suite.
OF COURSE I had specified "entire row".

Why this should be so difficult I know not. Please advise ASAP. Thank you.

Answer:How to ADD ROWS to Excel 2007 worksheet.

Pretty sure it is adding rows, but it is keeping within the same naming convention of the alphabet. It shifts the data accordingly.

5 more replies
Relevance 68.88%

I need a macro that will find rows that column K shows "X", then cut the rows and insert them further down the same worksheet below a merged row highlighted in orange in Excel 2010.

Answer:Relocate specific rows within the same worksheet

It would be ActiveSheet, not Active.Sheet (no dot)ActiveSheet.Cells(srcRw, "F")The Cells method uses 2 arguments: RowIndex and ColumnIndexThe RowIndex argument has to be a number or an expression that evaluates to a number. The number represents the Row that the cell is in.The ColumnIndex argument can be a number or a letter or an expression that evaluates to a number or a letter. The number or letter represents the Column that the cell is in.e.g.Cells(1, 1) refers to A1Cells(1, "A") also refers to A1Cells(3, 6) refers to F3Cells(3, "F") also refers toF3You can also use variables:srcRw = 3srcCol = 6Cells(srcRw, srcCol) refers to F3Or values in worksheet cells:A1 contains 4B1 contains 7Cells(Range("A1"), Range("B1")) refers to G4 as doesCells(Cells(1, 1), Cells(1, 2))Or expressions that evaluate to numbers and/or letters:Cells(4+4, Left("Tom",1)) refers to T8I could go on all day! Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

16 more replies
Relevance 68.06%

I have two workbooks, say:workbook_a.xlsx and workbook_b.xlsx and both contain different worksheets. Two of those worksheets are already linked by a couple of VLOOKUP formulas using a unique key which is the custid column. With this setup I can let users enter data in the workbook_a and then I can run all my BI on the workbook_b. Now, one of the most tedious things I do is search for new high risk customers in workbook_a so I can manually add them to workbook_b. I would like to do the following:1. From workbook_a lookup custid in workbook_b.2. If it exists, do nothing (I already added the customer and the VLOOKUP formula will take care of updating the cust_risk cell value).3. If it doesn't exist, copy the row from workbook_a to workbook_b (maybe even not the whole row but just one cell?).Is this possible? I'm using Excel 2007.

Answer:Insert rows into worksheet based on criteria?

It can be done with VBA.As an example, this macro will search for each cust_ID from [Book1.xlsm]Sheet1!D2:D12 in [Book2.xlsm]Sheet1!D:D.If it doesn't find a cust_ID in [Book2.xlsm]Sheet1!D:D, it will copy the entire row from [Book1.xlsm]Sheet1 into [Book2.xlsm]Sheet1.Obviously, the code would need to be modified to fit your workbook layout.Sub GetCust_ID()
LastRw = Workbooks("Book2.xlsm").Sheets(1).Range("D" & Rows.Count).End(xlUp).Row
For Each cust_ID In Workbooks("Book1.xlsm").Sheets(1).Range("D2:D12")
With Workbooks("Book2.xlsm").Sheets(1).Range("D2:D" & LastRw)
Set c = .Find(cust_ID, lookat:=xlWhole)
If c Is Nothing Then
LastRw = LastRw + 1
Workbooks("Book1.xlsm").Sheets(1).Range("D" & cust_ID.Row).EntireRow.Copy _
Destination:=Workbooks("Book2.xlsm").Sheets(1).Range("A" & LastRw)
End If
End With
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 68.06%

can any body suggest me a macro code to delete the blank rows from the worksheets, i have a worksheet in which blank or empty rows are present unevenly. and i want to delete all such blank rows all at once. so please suggest me a code.

Answer:how to delete blank rows in a worksheet by macro?

I referenced this utility at MSDN, and tested it.
This may or may not be what you are looking for
============== =====================================
Place the following VBA code in the worksheet you want to delete blank rows from
' The following code deletes blank rows from the active worksheet.
Dim Counter
Dim i As Integer
Sub DelRow()
' Input box to determine the total number of rows in the worksheet.
Counter = InputBox("Enter the total number of rows to process")
' Loops through the desired number of rows.
For i = 1 To Counter
' Checks to see if the active cell is blank.
If ActiveCell = "" Then
' Decrements count each time a row is deleted. This ensures
' that the macro will not run past the last row.
Counter = Counter - 1
' Selects the next cell.
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub
The solution can be found at.....
You may further be able to programmatically create a form with a button that will automate the process.
Hope this helps

3 more replies
Relevance 68.06%

Hi guys, I've browsed loads of previous questions to attempt to aid me in this but none have seemed to help.I am a complete newbie with VB so bear with me.I have a workbook with a worksheet called "Pipeline" in which users will type in to, I would like to assign a macro to a button that when pressed would look to column "O" and if the text says "45%" then cut that entire row and paste it into the next available row in worksheet "Prospect" (which is already created). I would also need to create similar conditions to cut rows into other worksheets based on the data in column "O" but i'm sure i could figure that out from the first question. Any help is greatly appreciated, been stuck on this one for a few days now. (I am rubbish with excel)Thanks a lot

Answer:Macro button to conditionally cut rows to new worksheet

Try this thread... Here Before Posting Data or VBA Code ---> How To Post Data or Code.

4 more replies
Relevance 68.06%

Dear Friends,Am using a timesheet in excel 2007  column , with details like Name , Employee id , designation, Date , time in and time out.Every day time in and time out with date for each person entered in same sheet.By month end , i need to segregate , for each employ based with his i need to know how to copy automatically multiple rows with same name to be copied to new worksheetThanks for your help in AdvanceSoman



1 more replies
Relevance 68.06%

Dear Friends,Am using a timesheet in excel 2007  column , with details like Name , Employee id , designation, Date , time in and time out.Every day time in and time out with date for each person entered in same sheet.By month end , i need to segregate , for each employ based with his i need to know how to copy automatically multiple rows with same name to be copied to new worksheetThanks for your help in AdvanceSoman



1 more replies
Relevance 67.65%

I'd like to be able to insert fields into the cells of an Excel workbook, rather than just in the header / footer.

I'm going to be completely in shock if this is not possible, but I'm not able to find anything whatsoever.




Answer:Insert field (e.g., &[Tab]) into worksheet cell

8 more replies
Relevance 67.24%

Is there a quick way to delete blank rows in an excel worksheet. I am using excel 2007

Dubli, Ireland

Answer:Delete blank rows in an excel worksheet 2007

9 more replies
Relevance 67.24%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 67.24%

Hi! Does anyone know what is the format of the date in the field "(battery) Manufacture Date" informed by Lenovo Vantage (Hardware Configuration > Energy)? It seems not to follow Windows setting. In my case, it is set to Brazilian Portuguese DD/MM/YYYY, but there is not a leading "0" in the returned value (see attached image). Does it really disregard the Windows setting and presents it in a fixed format (maybe American English)? When was my battery manufactured (Oct. 07 or July 10)?Thanks in advance.  

More replies
Relevance 66.83%

I am trying to create a worksheet to help me track my business.  In it I need a text field where  I can enter daily information. Is there some type of a text box or table that I can insert and use that will not increase the size of the worksheet when I update?  Thanks, lindee

More replies
Relevance 66.42%

I have selected certain cells, e.g. total cells, and cells that contain formulas which should not be changed, and 'locked' them. I then protected the worksheet but allowed users to insert and delete rows/columns. However, when I open the spreadsheet, the option to insert or delete in the context menu is greyed out. I can insert entire rows, but I have multiple sections adjacent to eachother which will become misaligned if entire rows are inserted.

Any idea how allow users to insert and delete rows and columns which span a specific number of cells?


Answer:In Excel, I can't insert/delete rows in a protected worksheet (even when enabled)

6 more replies
Relevance 66.42%


I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.



Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?

3 more replies
Relevance 66.42%

I accidentally formatted the date cell & it only accept numbers

Answer:how to correct a date field when you enter a date a number a

I don't know what you mean by "it only accepts numbers".Why can't you just format it as a date again?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 66.01%

First of all... I am a total beginner, but think I am 80% done with my goal, but 80 might as well be 0.

I have created a userform to input data into my worksheet. I have 8 fields which populate my worksheet. On my userform I have an 'Add Data' button which works like a charm via a tutorial from

On each row on my worksheet I would like there to be a product image. How do I add an input field on my userform that will allow the user to point to an image file and in essence be my 9th field on my worksheet? I have tried creating a button using the getopenfilename along with other commands for this button but nothing I have tried comes close to achieving my goal.

Can anyone explain to me how I can accomplish this. So, my goal is for the user to select a photo as one of the fields that will populate my spreadsheet. Also, if this can be achieved can I set an image size for this item. Needs to be a little bigger than a thumbnail.

Thank you for listening,

More replies
Relevance 65.6%

In creating a totals column, 3 columns, seller revenue, convenience fee and cc fee, need to be added up. However, there are some orders that have multiple rows that only need to have the cc fee column added once. How do I create the formula to add the first 2 columns for each order row and only the first row of cc fee if there are multiple rows for that order? Attached is a test file. Thank you for your help.

Answer:Adding Field only once from Multiple Rows

7 more replies
Relevance 65.6%

Hello there, i have a recordset rsTemp and datagrid TDBGridMain.

How can i print/MsgBox the field x from all the selected rows.

With this:


[COLOR="Blue"]dim ms as string
ms = rsTemp("ID")

MsgBox ms[/COLOR]

It only Show the last selected row and not the others. How ca i loop through
the selected rows and print the value from the field x from each selected row?. Thanks.

Answer:Print Field X From The Selected Rows VB6

11 more replies
Relevance 64.78%

Help! This should be easy but I can't get it to work.

I have a report that must group by Trip# and sort by ascending activity date all of the various activities that took place on multiple days of the trip.
Further, the trip with the earliest date must come first regardless of the Trip#.
I've accomplished getting the earliest starting date trip first and grouping its detail with a unique string made up of the earliest date of each trip formatted to YYYYMMDD+Trip#. This prints a trip total and works fine.

But the daily activity for the trip is out of date sequence.
Adding a Date field to the 'Sorting and Grouping' insists on breaking on and printing a sub-total for each of the trips activity dates.
Without the sorting & grouping by date the trip records are out of sequence even though the data source query is sorted ascending by activity date.

(see attachments that show the above)

The only solution I can think of is to create a sub-report sorted by activity date.

Does anyone have any better ideas?

Jim Shannon
JES Computer Systems
Access Developer.

Answer:How sort by 2nd field (date) without grouping when date changes?

7 more replies
Relevance 64.78%

I have a spreadsheet with hundreds of records. In row "I", there is a number that is representative of an employee. I need to move the entire row based on that one field.For example, in the below, I need to move Joe Smith and his entire row to sheet 2. I need to move Jason Doe and Jane Does and their entire row to sheet 3 and so on.Name DOB MR NXTASSESS Emp#Joe Smith 12/13/1984 307353 1/30/2012 1Jason Doe 7/31/1920 196986 2/2/2012 2Jane Does 6/29/1949 480904 2/2/2012 2Jim Doesnt 4/7/1942 198878 2/3/2012 3LOST, help!

Answer:Need to move multiple rows based on one field in each row

I suggest that you try the following:1 - Create a template with 11 sheets2 - Group Sheets 2 - 11 (Select Tab 2, Shift-Click Tab 11)3 - Hide the Columns that you want hidden4 - Enter your Column Headings5 - Ungroup the sheets. (Select any sheet tab other than the current sheet)6 - Press Alt-F11 to open the VBA editor7 - Paste the following code into the pane that opens8 - Create a button in Sheet1 and assign the macro to the button9 - Save the template, Macro enabled10 - Paste your data into Sheet1 and click the button to run the macro11 - Save the resulting sheet as an xlsx sheet, unless you want the macro to be saved with every sheet. Your choice.Note: The code assumes that you have column headings in Sheet1, so it starts searching Column I in Row 2.Sub CopyRowsToSheets()
'Determine last row with data in Sheet 1
last_srcRw = Sheets(1).Range("I" & Rows.Count).End(xlUp).Row
'Loop through Sheet 1 rows, starting in Row 2
For srcRw = 2 To last_srcRw
'Calculate destination sheet by adding 1 to value in Column I
dstSht = Sheets(1).Range("I" & srcRw) + 1
'Determine the next empty Row in destination sheet
next_dstRw = Sheets(dstSht).Range("I" & Rows.Count).End(xlUp).Row + 1
'Copy the row to the dstination sheet.
Range("I" & srcRw).EntireRow.Copy _
Destination:=Sheets(dstSht).Range("A" & next_dstRw)
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

15 more replies
Relevance 63.14%

I'm trying to move a row from one worksheet to another upon drop down.I found this code:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then If Target = "TD" Then Application.EnableEvents = False nxtRow = Sheets("TD Locks").Range("I" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy _ Destination:=Sheets("TD Locks").Range("A" & nxtRow) Target.EntireRow.Delete End If End If Application.EnableEvents = True If Target.Column = 9 Then If Target = "Closed" Then Application.EnableEvents = False nxtRow = Sheets("Closed Locks").Range("I" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy _ Destination:=Sheets("Closed Locks").Range("A" & nxtRow) Target.EntireRow.Delete End If End If Application.EnableEvents = TrueEnd SubIt seems to work, but is throwing errors a run time error '424' Object required.Also, I have all the sheets set up as tables so that I am able to sort them. I'd like the row to copy to the other page and create a new table row instead of inserting underneath and then me having to drag the table down.If anyone could help, that would be awesome! Thanks!

Answer:Moving Row To Different Worksheet

First, a posting tip:Please click on the blue line at the end of this post and read the instructions on how to use the pre tags to post VBA code in this forum. Posting code within the pre tags will retain the indents and make the code easier for us to read.Second, you might find this How-To helpful. It can not only help you troubleshoot code that is not working, but can also help you understand how code that is working is doing what it does. By reverse engineering code that you find on the web, you can learn a great deal about writing VBA code.I used some of these techniques to find the problem with your code., as for your problem, there are a couple of issues with the code.The main reason that you are getting the Object Required error is because you are deleting the Target when TD is found. Then when your code flows through to the second "If Target.Column = 9" there is no Target, i.e. no Object for VBA to work with.Another problem with the code is that it is somewhat inefficient. Even if you eliminated the Object Required error, the code would still flow through to the "Closed" section and execute some of the instructions. In other words, if the Target did equal "TD", there is no need to run any instructions related to Closed, such as checking the Target Column (again).The following code should work a little better. The changes to the first section eliminate the Object Required error by exiting the Sub after executing the instructions ... Read more

4 more replies
Relevance 62.32%

I have 4 work sheets:worksheet one has pending ordersone ordered confirmed i have a column confirmed i have a row that says YesI want the information in that row to go to the next worksheet#2 which is on route to delivery. Once delievered I have a row that saids YesThat row would go to the next Worksheet that states Delivered row with Yes to next work sheet for billing.What is a formula to activate a whole row to move?

Answer:Excel Moving a row to another worksheet

There is no formula that can "move" a row. A formula/function can only return a result in the cell in which it resides.You'll probably need to use VBA to accomplish your "one ordered confirmed i have a column confirmed i have a row that says Yes"I don't understand this. Please try to explain it differently and we'll see if we can help.Posting Tip: Before posting Data or VBA Code, read this How-To.

2 more replies
Relevance 62.32%

What I am trying to acomplish is a work log. I have in row A5 through A11 the dates of the week and these are calculated from the date I put in B3. So for example if I put 10/12/2012 in B3 then A5 will show "Saturday 6 October 2012", A6 will show "Sunday 7 October 2012" and so forth all the way to cell A11 which has "Friday 12 October 2012" which is the date that matches B3. Of course in the worksheet A3 has the words "Weekending"I do have codes in cells A5 through A11. Basically the formulas I have is, I put an actual date in B3 and then the formula in A11 is (=+B3) then in A10 I put (=+A11-1) then in A9 I put (=+A10-1) and do this up through A5.I am trying to create it so that when it is used, all a person has to do is put a date in B3 and it will automatically calculate the dates for A5 though A11. Since this is a monthly log there are typically 4 to 5 worksheets and using the example above, I would want that worksheet to show "Week Ending 12 October 2012", and then of course the next worksheet would automatically rename itself acording to the date in B3.Any help would be greatly appreciated.Many ThanksWes Morgan

Answer:How to automatically name a worksheet from the date in a cel

Let's start with the easy parts:First, you have plus signs in all your formulas. That's a throwback to ancient times and is not necessary. Save yourself some keystrokes and don't use them anymore. Second, you said "I do have codes in cells A5 through A11."The word "code" is usually reserved for VBA, like the "code" I offer below. It would probably be better (i.e. more easily understood) if you said "I do have formulas in cells A5 through A11." As for naming your sheets, this Worksheet_Change macro will name the sheet based on the date entered in B3. The Sheet will be named as soon as the date is entered.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Me.Name = "Week Ending " _
& Day(Target) & " " _
& MonthName(Month(Target)) & " " _
& Year(Target)
End If
End SubDo you have some way of ensuring that the users enter a valid date in B3? Data Validation can take of that by simply using the Date option, but that won't ensure that they enter a valid week ending date. You can use Data Validation to take of that too, but it's a bit more complicated than just choosing the Date option.Come on back if you'd like some help with that part.BTW...You could also set it up so that the user only has to enter the week ending date in the first sheet and the other sheets could use a formula to place the other week ending dates in the other B3's.The VBA code could then be modified so that it names all ... Read more

5 more replies
Relevance 61.5%

Hi All I have a excel workbook that I wish to loan somebody but I want it to work on only one day. My question is how to do this? I can get todays date to come up in a cell and then get IF function to give a true or false answer but then what? Ideally I'd like it to change something or move a formula so that it doesn't work right so what do i enter under false, can it be made to throw an error saying wrong date or something? Is there something else better to do this?Thanks FB

Answer:Date protect excel worksheet??

but maybe this will help until somebody (VoG, whisperer, simsy etc) come along.You can put =today in a cell and the date on which you want the workbook to work in another.As an example, put =today in A1 and 1/06/05 in B1 In the workbook open event put the follwingPrivate Sub workbook_open()If sheet1.cells(1,1) <> sheet1.cells(1,2) then activeworkbook.closeEnd subThis will check if today is the date you want the sheet to work and close the sheet if not.Tas

9 more replies
Relevance 60.27%

I have a table (A1:G5000) that is on Sheet1. Data begins in Row 5 and is continuous, but may end before Row 5000. The data was constructed using Excel formulas.Based on a condition in Col G, I want to copy its Row to Sheet2 starting at Row 5. I need to copy only the data, not the formulas. Also it would be preferable to exclude Col G, copying only Ax:Fx.Many thanks!

Answer:Moving Rows to another Sheet

Obviously, I can't see what you are doing from where I am sitting, so all I can do is tell you what you should be doing so that the macro will work. This process works for me and does not produce any errors. If you are unable to follow the steps in this process, please explain why so that I understand exactly what you are doing/trying to do.1 - You will need a workbook with at least 2 worksheets.2 - One of those worksheets needs to be named Sheet2. If the name of the worksheet that you are trying to copy the data to is not named Sheet2, then change both instances of "Sheet2" in the code to match the actual sheet name. The quotes are required.3 - Select the code from Response #5 and copy it to your clipboard.4 - Right click the sheet tab for the sheet in which you are entering OK in Column G.5 - Choose "View Code" from the pop up list6 - Paste the code into the pane that opens. Edit the "Sheet2" name if required, as noted in Step 2.After following that process, you should be able to enter OK in any single cell in Column G of that sheet and the values in A:F of that Row will be copied to Sheet2.It works for me every time.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

9 more replies
Relevance 60.27%

Thank you to every one that helped in my previous post. I did not get the exact answer to my queries but it helped massively.Now i have manage the get half of the asnwer to my queries by joggling through the various replies from your expertise.Here is what i came up with:Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Column = 5 Then If Target = "Y" Then'If yes, Store Row number, Determine next empty Row in delRow = Target.Row nxtRow = Sheets("Removed List").Range("A" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Cut Destination:=Sheets("Removed List").Range("A" & nxtRow) Rows(delRow).EntireRow.Delete shift:=xlUp End If End If Application.EnableEvents = TrueEnd SubNow, i am trying to use more than one selection instead of only one which, is the Y:I would like to use "Y" or "W" to move the row. But currently, when i select Y the row is moved to the destination sheet but i tried all i could think of but my second selection is not working. Your help will be much appreciated.Best regards

Answer:Moving Rows from One Sheet to another using VBA

First, a posting tip:Please click on the blue line at the end of this post and read the instructions found via that link. It will explain the proper way to post VBA code in this forum.As for your question, try this:If Target = "Y" Or Target = "W" ThenClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 60.27%

how do I copy a date down over several rows without it increasing the day

Answer:copy same date down over several rows

Copy the cell in which the date appears, for example A1Then highlight the rows you want the date to be pasted in, for example A2 - A10Then paste the date in. Ctrl and v

3 more replies
Relevance 59.45%

What if I wanted to swap adjacent rows without entering the row numbers I wanted to swap? I'm trying to write a macro that takes the currently selected row and moves it above the row above it ? essentially swapping the rows. But instead of being prompted to enter the row numbers, I want to begin with the currently selected row (or the row with the selected cell). I also want a separate macro that will swap that row downward.I found this below but it requires you to enter the row numbers you want to swap:Sub RowSwapper()rw1 = Application.InputBox("Enter Lower Row number")rw2 = Application.InputBox("Enter Higher Row number") Rows(rw2).Cut Rows(rw1).Insert Shift:=xlDown Rows(rw1).Cut Rows(rw2).Insert Shift:=xlDownMy end goal is to create two arrow buttons that will move rows up (and down) for desired positioning.I am a novice as I?m sure you can tell. Any help is appreciated.Jarrod

Answer:Swapping adjacent rows (moving a row above the row above it)

There are lots of books and web sites related to learning to write VBA code. While I have never read a book (probably should have) I did spend a lot of time hanging out in forums and looking at what others have written, then using the debugging techniques to help understand what their code was doing, whether I needed that code or not. I'd read a question, see the answer(s) and then "debug" the code if I couldn't understand how it was doing what it was doing.e.g. "Oh, that's how you build a comma separated text string from multiple values and then eliminate that pesky final comma!"Once a specific technique is understood, the concept can usually be applied/expanded to fit other situations. The main thing to keep in mind is that VBA is nothing more than a logical, step-by-step set of instructions. Try recording a macro and see what the VBA editor produces. Granted, it will be inefficient, bloated code because the editor will simply record (just about) every manual step you take, but it will give you something to "debug" just to see how the debugging tools work. Eventually you will learn how to clean up recorded code to make it more efficient, but it's not a bad place to start.Chip Pearson is probably my #1 on-line go-to resource. of formulas and VBA to play with.I understand that John Walkenbach is a well respected author in the Excel world. hope that helps.Click Here Before Posting Data or VBA Code ---> How T... Read more

7 more replies
Relevance 59.45%

Hi,I am hopeless and not familiar with macro at all. I've been online for hours trying to figure this out but I need a lot of help. I have an excel spreadsheet named Master with the following information. A B C D E F G H I 1 Last name First name Email Phone Rating Interests Date Pro Follow Up 2 Hill Jack ... ... M 4.0 ... ... ... ...3 Hill Jill ... ... W 2.5 ... ... ... ...and so on. I have about 200 rows and will be adding more constantly. The column I care about is E (Rating). I want to make a new spreadsheet based on every rating which will be ...W 2.5, W 3.0, W 3.5, W 4.0, W 4.5, W 5.0+ and then 6 more but with M instead of W (M 2.5 ... M 5.0+). I want the new spreadsheets in the same workbook and named after the Rating. So all the W 2.5 would be in the 'W 2.5' sheet along with all the information in their row. How can I do this? I am a total noob, don't even understand how to set up a macro and enter code or anything. Is anyone willing to help? I know it's simple and I've tried twice with other code and just don't get it. It's for my new job and they need it ASAP. Also, if you have enough time, is there a way to actually exclude some of the columns of information in the new spreadsheets? I would on... Read more

Answer:MACRO - Moving certain rows into new sheets!!

re: I am hopelessIf you were hopeless, you wouldn't have come here looking for help. You would have just given up. You came here hopeful that you would get some in case I type in W 2.3 (an invalid rating)Create a Data Validation Drop Down list that only contains valid ratings so that you don't have to type anything in Column E. Just use the Drop Down to select the rating and eliminate the chance of errors.This code should do what you are looking for:Option ExplicitSub RateTabs()Dim wsSheet As WorksheetDim lastRate, rTab, nxtRrw As IntegerDim rateName As String'Determine Last Row in Rates List lastRate = Sheets(1).Range("E" & Rows.Count).End(xlUp).Row'Loop Through Rates List, Create Sheet and'Copy Row 1 (Labels) If Sheet Doesn't Exist For rTab = 2 To lastRate Set wsSheet = Nothing rateName = Sheets(1).Range("E" & rTab).Value On Error Resume Next Set wsSheet = Sheets(rateName) On Error GoTo 0 If wsSheet Is Nothing Then Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = rateName Sheets(1).Rows("1:1").Copy _ Destination:=Sheets(rateName).Range("A1") End If Next'Copy Rows To Next Open Row On Corresponding Rate Tab For rTab = 2 To lastRate rateName = Sheets(1).Range("E" & rTab).Value nxtRrw = Sheets(rateName).Range("E" & Rows.Count).End(xlUp).Row + 1 Range("A" & rTab & ":F" & rTab).Copy _ Destination:=Sheets(rateName).Range("A" & nxtRrw) NextEnd SubClick Here Before Posting Data or VBA Code --->... Read more

7 more replies
Relevance 59.45%

Hi all. First post so be kind! Bit of a novice at VBA, but i am sure this is possible..
basically, I have a contact list in Excel that I need to format 'correctly'. It is currently formatted with the address on multiple rows, with phone number and email address in another column ( i have attached a dummy file). I would like to 'move' data so it is in correct columns...
Name Address1 Address2 Address3 Address4 Address5 Phone Email

To make matters a bit trickier, the number of address rows varies, so it might be 5 rows or sometimes 3; and not all contacts have an email address (although they would always be in row "2" of a contact address.

Many thanks in advance for any advice


Answer:Solved: Moving rows to columns

12 more replies
Relevance 59.45%


I have imported sales data in excel. The information lists in along two rows with mutliple columns. I need the information to be in one row under various headings. I have attached a link to a sample to explain this properly. I think I need to use a macro but I don't understand enough about them to make it work.

Any help would be much appreciated.

Answer:Moving data from columns to rows

11 more replies
Relevance 59.45%

Hi there,
I came across this thread (now closed)

Adding Date and Time Last Modified to footer of an Excel worksheet.

It adds the date to each worksheet.
Could someone please show me how to modify it to add the date to each worksheet ONLY IF that particular worksheet has changed?

Thank you in advance.

Answer:Solved: Adding Date and Time Last Modified to an Excel worksheet

Hi there,

Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.


3 more replies
Relevance 59.45%

In cell A2 I want to display the date that the worksheet was last modified.

Is there a function that does this?

Or is this a job for VBA? If so , how do I do this (from scratch)?


Answer:Solved: Excel 2003 - add worksheet date last modified to a cell

6 more replies
Relevance 59.45%

Hi, So I have a huge excel file with about 5,000 rows. I need to insert two rows for everytime a month changes.

Answer:How to insert a rows after date is changed

How is your data laid out? We need to know where the dates are and what they look like before we can offer a specific solution.Generically, write a macro that compares a date cell to the date cell below it. When the months don't match, insert 2 rows and then keep checking. How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

2 more replies
Relevance 59.04%

This has been posted before and I attempted the macro given, but it did not seem to work. It only seemed to copy the data from one sheet to a "new".

Column A Column B Column C Column D
George 12345 Red Book
George 23456 Blue Television
George 34567 Orange Computer

Need data to appear as follows:
Column A Column B Column C Column D Column E Column F Column G Column H etc
George 12345 Red Book 23456 Blue 34567 Television

In my actual sheet, there 6 columns of data excluding the name (Column A). The rows are multiple.

Can someone possible help?

Answer:Moving multiple rows of data into a single row

7 more replies
Relevance 59.04%

i have a large set of data that I pull. I need to take the large list and create a new line for every new number and duplicate the first set of data. This will be easier to show:Example data:GASTROINTESTINAL AGENTS DIGESTIVE AIDS CREON 2 51200024006740 51200024006760 51200024006720 GASTROINTESTINAL AGENTS DIGESTIVE AIDS LIPRAM-UL 2 51990003206774 51990003206785 51990003206787 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE 2 51990003200310 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRELIPASE MST-16 2 51990003206780 GASTROINTESTINAL AGENTS DIGESTIVE AIDS PANCRON 2 51990003206772 51990003206786 GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE 2 51990003206748 GASTROINTESTINAL AGENTS DIGESTIVE AIDS ULTRASE MT 2 51990003206774 51990003206785 51990003206787 Each number is pull into a separeat column to the right of each product. There could be up to 20 columns of numbers related to only 1 product. I need to convert that 1 row into x number of rows (based on number of columns) and show as separate row entries.Ex) Creon 2 90 caps 51119999119 51111111111needs to be: creon 2 90 caps 51119999119 creon 2 90 caps 51111111111etc etc.any help???????

Answer:Excel: moving column data into new rows??

HiHere is a macro that copies your data from a worksheet named "Source" to a worksheet named "Destination".Each code number is on a separate line. along with the name in column A and the value in column B.(From the data sample you posted it appears that there is a value (2 in each case) following the product name and before the code number - if this is not the case, please confirm which column always contains the first code number.You will need to name the Source worksheet "Source" or else edit all instances of "Source" in the macro. You will need to have an empty worksheet named "Destination" - the macro does not create it and won't work without it.To run the macro, I suggest you add a button to your source worksheetFrom the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)In Developer - Controls select Insert and choose the button icon.Draw the button on the worksheetIn the 'Assign Macro' dialog box select 'New'In the code window that opens enter this:Option Explicit

Sub Button1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim rngDestStart As Range
Dim intDestOffst As Integer
Dim intColsToMove As Integer
Dim n As Integer

On Error GoTo ErrHnd

'turn off screen updating to reduce flicker
Application.ScreenUpdating = False

'set start of source data
Set rngStart = Worksheets("Source").Range("A2")
'find end of sourc... Read more

11 more replies
Relevance 59.04%

@DerbyDad03 Your formula is great, except for 1 thing. I have 3 sheets, I move the information from 1st sheet to 2nd no problem and 2nd the 3rd. But wanted to make it so the information could be moved from the 2nd back to the 1st if needed or to the 3rd. When I add an additional IF statement (on the 2nd sheet) my macro doesn't run correctly.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 Then If Target = "Demand Out" Then Application.EnableEvents = False nxtRow = Sheets("Demands").Range("G" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy _ Destination:=Sheets("Demands").Range("A" & nxtRow) Target.EntireRow.Delete End If End If Application.EnableEvents = TrueEnd Sub

More replies
Relevance 59.04%

Is there a way to add Date and Time Last Modified to an Excel worksheet footer, so I can can tell from the various "work in progress" hardcopy printouts what is the latest version?


Answer:Solved: Adding Date and Time Last Modified to footer of an Excel worksheet

16 more replies
Relevance 58.63%

Hi. Im a bit stuck on how to attack this problem in vba and im sure someone has a basic solution for this:

In Sheet1, I have A1: Start Date and A2: End Date
In Sheet2, is the look up sheet. Its sorted by ColumnA:
05/02/2008...Jones .......10...........$800

I wanted to create a button in Sheet1 that will copy the entire row of data in Sheet2 but only within the date range declared in Sheet1. and place the copied row in Sheet1 starting at position A100.

Start Date: 05/02/2008
End Date: 07/02/2008
Click button(vba executes)
Copy all rows from lookup sheet2 within these dates and paste it in Sheet1 A100.

Any solution will be very much appreciated.

Answer:Copy rows within date range - EXCEL

Dear Miage
Peace be upon you
What if there is 10 rows (or more) matches the criteria should all rows been copied from A100 to A140??!!!
I think this will be annoying.
Instead I think it is better to
make a category for each row in Sheet2 depending on the date range in Sheet1. (Using Vlookup function, in access 2007 only)
Give the category a name that could be understood (From010102To150302)
apply a filter on Sheet2 so that you can see instantly all the records you want within the range.
If you want more details I am ready, but please post a sample file (just 4 records)
Mohammad ElNesr

1 more replies
Relevance 58.63%

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated

More replies
Relevance 58.63%

hi,i have data like this:8/24/2010 p 152438/24/2010 p 15008/29/2010 n 458/29/2010 p 74108/29/2010 p 6218/29/2010 n 2759/2/2010 p 152439/2/2010 p 1500what I need excel to do is insert a row (blank) after every specific date (for e.g. between the last entry of 8/24 and the first of 8/29).any kind of code that I have written just does not seem to work.. please HELP!!!Thanks..

Answer:insert rows based on date in cells vba

Rather than just giving you the code to do what you want, I think it would be beneficial to you if you posted what you have tried and we'll help you fix it.I'm not asking you to do that to embarrass you or point out shortcomings in your VBA skills, but rather to help you learn from your trials and errors.Sometimes explaining why something doesn't work is more educational that just telling you want does.

5 more replies
Relevance 58.63%
Question: Update date field

Thanks in advance for your help!

I used to know this and I did a search and couldn't find the answer.

How do you update a field to a date either 6 months away or 12 months away for the current date in that field?

Thanks again!

Answer:Update date field

6 more replies
Relevance 58.63%

Is it possible with Access 2010 to have a date field that would change to current date whenever another particular field is modified. For example, we have comment field and we would like to have another date field that would change to the current date when the comment field has a change.

Answer:field modified date

Yes you can do it with simple VBA code, you could also add it to the comment itself, so it appears at the beginning of the comment.

1 more replies
Relevance 58.63%

Not sure how to go about this. I am not a programmer by any means but need to make a program.

I have a text file that is in what is called BAI format (bank standard format to send info). Basically this is a CSV file with a header. Right now I use a mail merge file to update the date field. I am looking to automate this so I would only need one "dummy" file and have a program that will update the date field automatically. The file is opened with notepad. I have done some VB but not a lot. I am looking for the best way to go about doing this. I tried to get some of the programmers to do it but no one wants to worry about supporting it, so I am taking a crack at it.

All help is very much appreciated. One idea I had is maybe a macro in excel but not sure if it would mess up the formating. I will try to get a copy of the file I use and post it.

Answer:Updating date field

6 more replies
Relevance 58.63%

Hi,I'm trying to create an if formula and I'm a bit stuck.Column L contains dates separated by commas. I'm trying to create a formula to say:if L2 contains (fixed date from a single cell) then "Exclude" else "No"There are two issues. How do I make the if statement contains not just equals? How do I copy the cells down and keep the field with date in fixed?For example I added the date I needed to 'Delivery sheets d1':=IF(Data!L2='Delivery sheets'!D1,"exclude","No") but when I try and fill down the D1 continues in a series e.g. d1, d2, d3 etcI hope this makes sense. Thanks in advance!

More replies
Relevance 58.63%

I work with MS Access 2003.

I am creating a query to show all contracts set up in a certain year that have been cancelled in that specific year. This "Cancelled" field is not a yes/no field, but includes the date on which it has been cancelled (always starting 31/12/YYYY).

First, to show me the contracts set up in a certain year (say 2008) , i created a parameter under the "Contract Year" field, asking which year you want to see (in this case 2008).

I wish to add a criteria that then shows cancelled contracts at the end of that specific year, so 31/12/2008. So whether it is "2008" or another year is dependent on the value in the "Contract Year" field.

I tried the following as criteria for the Column "Cancelled":

#31/12/[Contract Year]#

to try and have it take the year mentioned in that field.
But it doesn't work. Access sees is as a second parameter, instead of a field name.
I tried adding LIKE to it and the &-sign, still no luck.

Is it possible to use a field name in a date criteria and if so, how could i do it?

Thank you very much in advance for your help.

More replies
Relevance 58.63%

Hello. I am looking for copy data based on criteria into a new work sheet and format it at the same time. My data is a database dump with thousands upon thousands of records and for my purposes I want to create a semi-refreshing excel dashboard that will show the data I want to extract. Basically I am dumping the database to an excel tab but can also access the database so getting to the data is not the problem. What I would like to do is get the data based on criteria and add it to an excel worksheet with a row being created based on the successful criteria of the data.Basically my data looks like this:## DATA FILE TAB ##[![Data file sample][1]][1] [1]: this is what I want the data to end up looking like. One thing to keep in mind is that I can't simply populate a worksheet as I need to be able to create new rows once "good data" has been filtered.## Formatted Worksheet ##[![Formatted Worksheet][2]][2] [2]: for your help in advance.

Answer:How do I add rows of data to new worksheet from data dump

I'm not quite sure what kind of help you are asking for. Your subject line asks about "adding rows to a new worksheet" yet you posted an image [2] of data that is apparently sorted in some way and formatted with different font sizes and fill colors. In addition, the 2nd contains data that doesn't even appear in the original data set.What exactly are you asking for help with? Are you asking us to help create that 2nd, formatted sheet or are you asking for help with adding rows to that formatted sheet? I also don't understand this line:with a row being created based on the successful criteria of the data.What does "successful criteria of the data" mean?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 58.22%

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.

Answer:Excel: Format rows based on date and a few other specifics

7 more replies
Relevance 58.22%

I use a multi sheet spreadsheet at work to track outstanding tasks.

I would like to write a formula to take today's date and hide the row if it is:
1- less than a week since the last entry
2- hide the row if it from the current month.

I would also like to write a formula that hides the row or column if all cells in use in that row or column have been checked.

Thank you in advance for your assistance.

Answer:EXCEL - using a date dependant formula to hide rows

Is it necessary to hide the row? The only way you might be able to do that is with a macro, but I think it would be complicated and messy. A more simple way would be to use conditional formatting - for example you could make the text a light grey (or even white) if the conditions are met. It wouldn't hide the row, but it would mean the other rows stand out by comparison.

6 more replies
Relevance 58.22%

Hello, I need help with the following:
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.

select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx

Any insight into this would be greatly appreciated. Thank you.


Answer:Concatenating Date and Time Field in SQL

Just wanted to add some more examples:

Account_Number: Integer Type
Date_Time: Date Type
Date_Time: Modified: Character Type

Account_Number Date_Time Date_Time_Modified
3246 1/31/2005 12:00:00 AM 200501
4920 11/30/2008 12:00:00 AM 200811
5932 6/30/2011 12:00:00 AM 201106
... ... ....

1 more replies
Relevance 58.22%

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )

Answer:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

1 more replies
Relevance 58.22%

Hello everyone,

I'm working on an order management database and I'm stuck on one item.

I have a form that users can fill in with purchase order data. One section of the form relates to contract review completion.

I would like to have a checkbox that the user can check when contract review is complete, but I'd also like for a completion date field to be auto-filled when the checkbox is checked.

I've tried a couple of things, both unsuccessful.

Any ideas?


Answer:MS Access - autofill date field

6 more replies
Relevance 58.22%

Is there a way to insert a date merge field where the date is spelled out (June 10, 2008) and will not change if the document is reopened, resaved, modified, etc?   Thanks for any help!

Answer:MS Word merge field for date

I'm not quite sure I understand the question, but depending on the Word can use the "Insert" link from Word's toolbar to insert the date into a document...formatted to your liking.Once the document is saved...the date will always appear as you have set it.

2 more replies
Relevance 58.22%

Hi guys,

Something weird is happening. Some of my movie files (file type doesn't matter) became corrupted; when I play them in VLC they cause errors, they were just fine before and nothing has changed. I noticed in Explorer that these files have no Date Modified field, it's simply blank; I've never seen anything like that.

I did a chkdsk on the drive and see no errors. What could it be ?


Answer:Blank Date Modified Field ?

VLC should rebuild the Index, but that is a temporary fix. Depending on what format they are, there is "DIVFIX" and "Meteor MKV repair" you can try to run the faulty content through which of those is appropriate, you'll have to google it, cant remember the links.

3 more replies
Relevance 58.22%

hi, i searched in vain for an answer to this question:
when i edit an image file and resave, the date modified date does not change. since i started using computers 100s of years ago, this was automatic. how can i enable this (obvious) field? what point is there in even having a date modified field if it stays the same as the date created field? thanks!

Answer:date modification field does not change

Are you sure you have the Date Modified selected as a column to display?

Right click on the header column (has Name, Date, Type, etc.) and select the Date Modified tag.

9 more replies
Relevance 57.81%

Sometimes my tables in Word 2007 seem to be 'split' even thought they appear to be all one table. If I've created a table then moved rows around, I see strange behavior when I want to do an action on the whole table or a column. If I either select a column or select the whole table, only the top n rows are selected. I have to 'split' the table beginning at the row which is not selected, then tab through on the last row of the first set a couple of times to create 2 more rows, then select all the columns of the remaining table and drag those rows up to the 'main' table. Sometimes I have to do this 2 or 3 times before Word sees my whole table as a unit, as opposed to several mini-tables that appear to be a unit.
I know I must be missing something fundamental about tables in 2007. I never had this problem with Word 2003. Also, my apologies if this isn't really clear - it's hard to describe! I can't reproduce it with a dummy document so I don't have an attachment to upload.
Thanks for any help.

More replies
Relevance 57.81%

I was wondering if the following is possible:

I am using Microsoft Excel currently (on a Windows XP machine) and I have three columns, one is text (name) and the other two columns are MM/DD format (start date) (finish date).

Can I make something that will move the entire row to a new sheet/tab, after the second date is added?

Name Start Date Finish Date
ABC Company 07/11/07 07/15/07
BCF Company 07/15/07 _______

Any guidance with this problem would be greatly appreciated.

Thanks in Advance,

Answer:Solved: Moving text after completing a field

15 more replies
Relevance 57.4%

I currently have a PS1 script that takes yesterday's log files from a server, collates them into a single file and emails the file out in csv format. Out of necessity this always includes the first file from today's date, as that file contains the final activity from yesterday as well as the first activity from today.
Problem is, that means some lines of data will appear in today's file and tomorrow's file.
The filename of the daily csv file always contains yesterday's date in the format <7-char filename>dd-mm-yyyy.csv, e.g.:
At the outset, one of these daily csv files will consist of tens of thousands of rows of data, with all data in column A. Each string begins with a date/time stamp, so the values in col A are in the format yyyymmdd_hhmmss<log file information> e.g.:
20150319_093015< >long string of log information​
I then run a macro on the csv file, beginning with inserting two new columns to split the data into 2 columns using the formula:

Range("A2").FormulaR1C1 = "=LEFT(RC[2],15)"
Range("B2").FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-15)"​
In other words, it places the date & time stamp in one col A, and all the rest of the guff in col B. It then goes off and does a whole bunch of other stuff.

What I need to do first, though (don't mind whether it's before or after the column has been split into two), is delete all rows where the... Read more

Answer:Solved: VBA - del rows where date in string doesn't match filename

7 more replies
Relevance 57.4%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like


exit celll with ctrl, shift, enter

1 more replies
Relevance 57.4%


It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.


Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error

Answer:SQL Query help required with date/time field


Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

Reuel Miller

1 more replies
Relevance 57.4%

Have sent email to several friends, there is no attachment, just relatively straight forward item with some borders and a table in the email body copy. In one case the email was bounced back as 'delivery failed' with the message:This message has been rejected because it has an overlength date field which can be used to subvert Microsoft mail programs The following URL has further information click hereHave gone to hyperlink which says 'page removed'. Other emails I sent to this friend get through. My questions are:1. I assume this is a genuine block?2. Has anyone come across this and do you know what it means?3. Is there something I can do to get my message through? [I had put a lot of work into it]Many thanks Tiggertwo using Outlook Express and windows95

Answer:Email bounced 'overlength date field'

Sounds like some sort of anti spam program which is a little too zealous. Have you tried it again to the same user?

1 more replies
Relevance 57.4%

I receive periodic Word (.rtf) documents from a property rental agency whenever my property is rented. The document contains the dates rented, the renter's name, etc. There is a "date field" in each document which automatically shows today's date, rather than the date the doc was created and sent to me. If I want to forward the doc to the renter a week later how do I stop the date from updating when I send it and stop it from updating when the renter opens the file?

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 57.4%

I want to combine a text field and a date field into one field to use as a reference number. IE 'Mil - 05/11'.

I have got a field with the standard short date format 29/05/2011 (that I have to keep) and I have created a second field with the same date but formatted to a "mm/yy". When I concatenate the Operater Ref field "Mil" to the Date Ref field "05/11" I still get the full date format IE Mil - 29/05/2011.

Answer:MS Access Concatenate a date and text field


If you are creating the second field in your table, go to design view and change the Data Type to Text then try your concatenation. I built a mini-table and queried it and this worked for me... hope this works for you!

19 more replies
Relevance 57.4%

How do you insert a date field in Word 2007?

Answer:How do you insert a date field in Word 2007?

In Office 2010 at least, it should be under the "Home" or "Insert " tab. Then there should be a sub-category like "Date" or "Time".By the way. In the future, you'll get better office results here: and Answer. The way of learning.Dell Dimension 8300Intel Pentium 4 HT @ 3.20 GHz4 GB RAMNvidia Geforce FX5200

2 more replies
Relevance 57.4%

I am trying to add a sequential number to a date field in an Access form.
The format of the field is PS 18 Jan 11 xx, the PS is fixed, the current date and the xx is my sequential number I want to add.

Is there a way, to do this when the date is entered? And I would need the sequential number to reset to 01 with a new day.

I'm new to this, any help would be great!


Answer:Help in appending a sequential number to a date field

16 more replies
Relevance 57.4%

I have tried sending a message to a friend who, up until now has had no problem receiving. The message I am sending has no attachments. But this message has come back several times with the message:

This message has been rejected because it has
an overlength date field which can be used
to subvert Microsoft mail programs
The following URL has further information

but when I go to that hyperlink it is a page saying that the item 'might' have been removed.

Incidentally I have an uo to date virus checker running and have been able to send and receive emails to others since the first bounce of this email

1. Is this a genuine response or some sort of problem at receivers end?
2. If genuine can someone explain what I might need to do, if anything can be done, at my end to get the message through. I have already tried maing the subject line smaller but that made no difference.

More replies
Relevance 56.99%

"Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way"

Hello, all! Hopefully somebody can help me with the following problem (please see the attached document).

Presently, I have an Excel 2010 document containing variables 1-6 per country in rows for label 1 through label x. However, I need the table to contain the country level information as columns for each label so that there is only one row per label. For example, instead of row 1 containing label 1 argentina variables 1-6 then row 2 containing label 1 arab emirates variables 1-6, I need the table to contain row 1 label 1 columns argentina variable 1, argentina variable 2, etc...then arab emirates variables 1, arab emirates variable 2, etc.

Also, the countries in the table that do not have information for a given label fall at the end of the list for each label instead of in alphabetical order, but they need to be placed in alphabetical order when "columnized." I have provided the arab emirates as an example. Alphabetically, it comes before Argentina, but in the list it comes after because it did not contain information for those given labels. Since information per country will vary from label to label, the countries must remain in alphabetical order in the columns regardless of whether information is provided or not per label.

Is there a macro that may be written for this?

Thank you so much, in advance, for taking the time to read my post! Happy day!

Answer:Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way

14 more replies
Relevance 56.99%

Hi everybody,

I upload an excel file to help understand my problem.
So I have date and time and in column 2 I have my parameter value.

Note 1) not all the data is at 04:30:00, and this is a huge problem in the macro I found online.

1-1-16 0:00 1
4-1-16 4:30 1
8-1-16 4:30 1
11-1-16 4:30 1
15-1-16 4:30 1

What I want to create is the missing data time, associated with a column 2 blank and not 0 if possible

So the final result should be like
1-1-16 0:00 1
1-1-16 0:01 "blank" (not zero if possible)
1-1-16 0:02 "blank"
4-1-16 4:30 1
4-1-16 4:31 "blank"

Thanks for your time

More replies
Relevance 56.99%


In Access, I am trying to develop a process for generating a unique client number that follows a very specific formula: XXX - XXXX - X - XXXX. The first group should represent the quarter and year when the client is first introduced (non-standard year: Feb-Jan). The second group should be a unique, sequential ID number with leading zeros (missing numbers won't matter and leading zeros seems impossible, so I'm planning to use a DMax calculation on the form that generates a sequential number). The third number represents the client's initial program phase ID. The final number relates to the grant contract ID.

I have generated the number with the following in a Select query:

UniqueNumber: DatePart("q",DateAdd("m",-1,[MonthStart])) & Format([MonthStart],"yy") & "-" & [SeqNum] & "-" & [InitialPhaseID] & "-" & [ContractID]

The table already has a primary key, but since I am required to generate client numbers in this format, I want the UniqueNumber to be saved and searchable. This field never needs to change once the original record is saved. I've tried an Append query, but can't make it work. I assume it's related to the fact that I'm combining Date and Number fields, not to mention the DatePart function. I consistently get a key violation error. Any thoughts on how I can proceed?

More replies
Relevance 56.99%

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.

Answer:Solved: Access, Date/Time Field to String -- is it possible

7 more replies
Relevance 56.99%

Hi guys, I'm very new to access and I'm having some issues. I have made a field for an expiry date, and I have attempted to create a calculated yes/no field that gets ticked when the expiry date is up, so basically I have a box that I want ticked for when the expiry date is before the current date (I hope that makes sense). Thanks guys,


More replies
Relevance 56.99%

from email address removed by Dreamboat

I have a database with a date field; 'Birthday'.

I want to take the month info from the date field and have access put it into another (adjacent) field. Ie;

Month of B'day (New Field)

Also I want to be able to query the birthday field from previous data base tables which do not have the month field and make a table with the month information extrapolated to a new 'Month of B'day' field.

(Month([Birthday])) does not work.

I have printed a report with the 'Month of B'day" info extrapolated to the report but it is not useful because the report does not sort by the new information.

I know these must be possible but . . . . . AAAaaagh I'm going nuts.

Answer:Solved: Extrapolate Month from an Access date field

16 more replies
Relevance 56.99%

Hi all,

I am looking for help on VB script to send automated emails based on the date column in excel sheet..

Uploading the excel sheet as well. The email should trigger on dates in column F to corresponding email addressed in column E ..

The email body should say :

Your employee "Column C" is approaching his probation period on "Col D". Kindly confirm if you want to confirm on the date of "Column D" or modify it.

Answer:Need help to send automated email based on the date field

Have a look at the attached I have written the code necessary however if you have any changes you would like made let me know.

1 more replies
Relevance 56.99%

I want to be able to place the date in reverse order in the subject line of new emails when I press a button on the toolbar. The format of the date should read '20061003' for the 3rd Oct 2006 - I know i should be able to use a macro or write VB scipt, bit i am not that clever - please help - many thanks

Answer:MS Outlook 2003 - Reverse Date in Subject Field

06.10.03 - subject.It's easier to read and there will not be a filing problem for the next 94 years or so.I'll leave the technical stuff to others

8 more replies
Relevance 56.99%

I have a linked Excel spreadsheet to show invoices received and I insert a date when they are paid. I cannot figure out the expression to use to show all records where no date has been inserted. Can anyone help please.Thankyou

More replies
Relevance 56.99%

This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

Thanks in advance for your time/help ...

Answer:Excel: Extract hour ONLY from a date/time field

Ok, got it to work ... but not sure if there is a better way.

I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

Is there a quicker way out there?

2 more replies
Relevance 56.99%

I have an excel sheet with dates displayed in European format dd/mm/yyyy :-)I import it into a letter ("you have been a member since") and it imports in American mm/dd/yyyy.I can't see how to properly format the merge field

Answer:Mail merge a date field screws the formatting!

It all has to do with how your Operating System Date is set up.If your in the US then the default is mm/dd/yyyyYou can change how Excel Displays the date to dd/mm/yyyy, but it Stores the date in the System Date format of mm/dd/yyyy.When you import the date to Word, the System Date format is used.See here for how to change the format:

4 more replies
Relevance 56.99%

Hey everyone,

I'm having problems with Access, where I have a date/time field, which stores a month and a year (successfully), but still automatically assigns the first date of the month in question.

I used the following input mask (00/0000;;_) for the date field, and so far I haven't had any troubles with putting in a month and year. So far so good. But when I complete the date and move on, Access changes it to a full date. e.g. 06/2010 --> 01/06/2010.

Now, I'm actually fine with this part, my only problem is that I don't want it to do this in forms (both datasheet and normal view) or reports.

I heard something about using a Format ( to adjust the display, but I can't seem to get that to work. Is there any other way to tell Access how to display the date field, like what can be done in Excel?

This is rather urgent as I have a project due tomorrow and this problem (as well as others) have now been reached and I'd like to solve them as soon as possible.

Any help is appreciated, and I look forward to your responses.

Answer:Solved: Urgent: How do you format the DISPLAY of a date field?

8 more replies
Relevance 56.99%


Access 2003
Windows XP

I am creating a new table and would like to format a field to short date. Here is my code. Is there a way to format to short date from here?


Set fld = tbl.CreateField("Date", dbDate)
fld.Required = True
tbl.Fields.Append fld

Answer:Access 2003 - Format field to Short Date

try this:
fld.NumberFormat = "m/d/yyyy"

3 more replies
Relevance 56.99%

I have a Date field in a Form. I want the Date to be appear in red when it is more than 6 months older than today's date. Where and how do I make that happen?

Answer:Access 2010 Date Field in Form Macro or ()Iff ??

10 more replies
Relevance 56.99%

The company I work for has asked me to work with the DB (as you may well guess in this economy I now have to learn how to work with Access DB. This DB was created by someone who had a book “Access 2000 for dummies” in 2002, I have since then converted the 2000 DB to 2007 accdb format. That being said, in a form that is used to track records-TS# is the primary key, they want a YES/NO or actual name- Completed *check box for yes and blank for no*. I created the check box as you may know that was easy enough. The problem comes in when I go to the next record the box remains in whatever state it was on the previous record and does not show up anywhere else. I am sure this is likely because there are steps that I am omitting or not aware of. Any help offered would be amazing as I continue to work on this train wreck.


Answer:Solved: Access 2007 YES/NO field with time/date

Mike, the Check Box field has to be in the Table that the Form uses as it's Record Source. The Check box has to be "Bound" to that table's field, i.e. it's "Contorl Source" of the check box in design view must be the table field.

2 more replies
Relevance 56.17%

Hi everybody

I have got an hourly meteorological data for 10 years. The problem is that some of the data entries are missing. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 22:00
23-April-2006 23:00

I need to insert the rows for missing hours. Rest of the columns in the inserted row are needed to be blank. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 21:00
23-April-2006 22:00
23-April-2006 23:00

Please also note that for some years, the data entries are for half past hours. e.g.

31-Dec-2001 23:30
01-Jan-2002 00:30
01-Jan-2002 01:30
01-Jan-2002 02:30

Muhammad Zeeshan

Answer:Solved: Inserting rows for Missing Date/ Time data (Excel 2007)

16 more replies
Relevance 56.17%

Good day guys

My puzzler today (and has been for ages) is this:

In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

I therefore have a field called "DateDetails" in my report header whose ControlSource is:
="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
The dates actually derive from a table and the date format for both fields in that table is "General Date".

The result is that the report shows the following as an example:
Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

BUT what I want is the following:

Records Selected between 17/05/07 and 10/08/07

or even nicer

Records Selected between 17-May-2007 and 10-August-2007

Can this be done in such a control?


Answer:Solved: Access 2003 - Date Format in a Calculated Field

7 more replies