Extract Date Value From Not Yet Created Worksheets To Put In A Central Document
Jun 27, 2013
At my work, we receive manufacturing plant reviews periodically from each plant. Information on the plant's completed tasks and dates of completion are then updated to a central summary document for all plants. These updates are completed forms which each plant supervisor fills out and uploads to our network. At this time, someone manually has to shift through all these plant update forms and pull key information out so the central summary document can be presented. I want to streamline this process so there is no double handling of review information or continuous code changes.
What would be a way to have the central summary document extract date information from the most recent file in a plant's update folder (i.e. a folder contains plant update 05/02/2013, 06/20/2013, 06/25/2013, 07/2/2013, 07/22/2013, 8/05/2013, 8/13/2013 and it will only pull date information from 08/13/2013. This also means that these worksheets have yet to be completed and therefore do not exist in the update folder until the plants post them.
View 1 Replies
ADVERTISEMENT
Mar 14, 2013
How do I add a password to protect the PDF that I create from my excel 2010 document?
View 5 Replies
View Related
Feb 20, 2014
I have an Excel spreadsheet that contains information for a Word document. It uses an elaborate macro to "substitute" unique codes in a starter document. At completion of the process, it goes through and delete all unused codes
The problem is that the codes are preceded by a "bullet" symbol.
How can I remove that orphaned bullet symbol. If the code is ". CtlCode10" (where the . is a bullet), then ALL need to be removed
View 2 Replies
View Related
May 4, 2007
I have created a userform in Excel. The information from this userform is saved into a word document using a command button. What I would like to know is can I use vba to, not only create this word document but also, to send it as an email attachment at the same time? Just to make it a bit tougher - I'm using Groupwise as my email software.
View 4 Replies
View Related
Jul 5, 2013
I am looking to take information from a document emailed to me in word and then populate the specific ranges in my excel spreadsheet for invoices. What is the best method for doing this and how can i control where it comes from the position in the document and the range is going to.
View 1 Replies
View Related
May 17, 2008
Is it possible to have the formula written in one remote cell, so that any modification done in this formula is reflected on all places where its refereced to?
z26 : Formula A
A1 : Formula A
A2 : Formula A
A5 : Formula A
A6 : Formula A
A7 : Formula A
A9 : Formula A
A11 : Formula A
in this example i store the formula in z26 and variety of cells in A are using it (not all of them)Suppose if i change anything in z26. I want it reflected in all the cells that are using this formula?
View 9 Replies
View Related
Aug 7, 2013
I have five worksheets among eight total in my workbook that I use as a report. I would like to have a button that copies those five particular worksheets into a new workbook and pastes all numbers as un-linked values. This would save me a TREMENDOUS amount of time generating my reports every month.
View 3 Replies
View Related
Aug 26, 2006
a while back I posted Central Glossary to feed multiple documents to put a glossary at the end of a Word doc. Fabulous help and got it all sorted. Now I'm trying to do it at the end of an Excel w/b.
View 4 Replies
View Related
Aug 4, 2006
I am about to write a macro and I want it to identify, from the C: drive, any .csv files that were created / saved today. I have managed a pure list before and I have managed to distinguish by filename, but selection by date would be exctremely useful.
View 2 Replies
View Related
May 5, 2006
I am using a module to create and save a Word document from an Excel module. I'm using the following line to save the document which works fine but (and this is a big BUT)(to quote a TV programme from long ago) obviously everytime I save the document it will overwrite itself. Is there a way of adding the date it is saved on to the file name so it creates a new document each time.
objWordDoc.SaveAs Filename: = "WAGReport"
I have tried changing it like this but get an error message
Dim dtMyDate As Date
dtMyDate = Format(Date, "DDMMYY")
dtMyDate = Sheets("Client Master").Range("d2")
objWordDoc.SaveAs Filename: = "WAGReport" & dtMyDate
View 3 Replies
View Related
Sep 22, 2009
I have the following info in a table:
name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555
Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.
Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).
View 9 Replies
View Related
Feb 2, 2014
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
View 3 Replies
View Related
Feb 17, 2009
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
View 9 Replies
View Related
Nov 4, 2013
I have a Workbook containing four different Worksheets, each of which contains a different number of columns:
"Set A" = 18 Columns
"Set B" = 47 Columns
"Set C" = 47 Columns
"Set D" = 11 Columns
Each Worksheet contains a different number of rows, approx 4,000.
Column A for each of the above Worksheets is, however, the same and contains an alphanumeric string called "Accession ID".
I have been tasked with extracting the entire row of data for each "Accession ID" which appears on at least 2 Worksheets, and then compile them in a new "Summary" Worksheet.
So, for example, if one of the Accession IDs was "ABC123" and it appeared on all four of the Worksheets; the Summary Worksheet would contain the 18 Columns from the row on which "ABC123" appeared in "Set A", the 47 Columns from the row on which "ABC123" appeared in "Set B", the 47 Columns from "Set C" and the 11 from "Set D" all on different rows.
I tried toying with VLOOKUPs etc, but given there are four Worksheets, and in order to qualify the Accession ID must only appear on a minimum of two Worksheets - it quickly become a mess.
Is there any way of being able to do this as a macro perhaps?
View 2 Replies
View Related
Sep 21, 2007
I have 2 sheets in a workbook. Column A on sheet 1 has numbers such as 123456, 123457, 123458 ect ect. Column A on sheet 2 has same amount of numbers but mostly different. However there are a few duplicates. QUESTION: How can i write a code that will fit in a new sheet lets say sheet 3 that will pull out only the list of numbers from sheet 1 and sheet 2 that are NOT duplicates?
View 5 Replies
View Related
Mar 9, 2014
i have several styles to handle mentioned under different rows separately.
all the styles have their various raw materials inward date mentioned under different column of their respective rows from column Q to Y
now i need to 1. extract the latest date of any particular raw material which can be in any of column from Q to y columns ACCORDING TO DIFFERENT STYLES IN A Particular row automatically that is AC
View 1 Replies
View Related
Mar 27, 2013
Extract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
View 9 Replies
View Related
Jan 17, 2010
I have two questions regarding date format and hope you can provide input.
1) say 01/01/2010 displays as Jan-10 and i need a new column to state it as JAN. What function should i use to achieve it?
2) i need to state the difference between first day and last day of the month. What function should i use to achieve it?
View 2 Replies
View Related
Mar 15, 2003
I have a spreadsheet containing a factory's operational data, with each machine in the factory being represented by its own worksheet.
The sheets all have the same structure, with each line of data representing a shift (we have 12-hour continental shifts, so column A of all the pages has the date in an standard format, and column B has either "d" or "n", representing the shift). Various datat is documented on each machine on a shift-per-shift basis.
What I need to do is figure out how to do a macro that extracts all of the lines on all of the machines' worksheets that pertain to a particular shift (for instance, all of the lines where column A read "14-Mar" and column B read "d"), and export the values of the entire row to another worksheet, creating a shift summary on one page.
To do this, I would like to make the date and shift user-definable variables that appear at the top of what would become the summary sheet, and have a button that activated such a macro at the top of the page as well.
View 9 Replies
View Related
Nov 15, 2009
I have a workbook with data extracted from our ERP (transaction sheet), and a list of cards (card list sheet) which ties the relevant cards to companies and vehicle.
I would like a macro to extract all transactions in the "transaction" sheet into a new file (see "unit" sheet), with the relevant company name, report dates, vehicle number, card no, date, time, polling point name, volume, unit rate. The "Amount" column will be the volume X unit rate.
Vehicle numbers can only extracted from "card list" sheet, and not found in the "transaction" sheet.
these are the details I need:
- all company's transactions details (not sorted in the "transaction" sheet) should be extracted into new individual company's excel file
- headings I need are shown in the "Usage" sheet (company name, vehicle number, etc)
- depending on the number of transactions, there should be a " subtotal" row below the last transaction (as shown) of each vehicle
- with all vehicles extracted from each company, a grand total will be added
- filename should be created with company name
View 9 Replies
View Related
Jun 25, 2008
I am trying to write a Macro that would go into same specific cells in multiple worksheets and paste the data into one summary worksheet if ofcourse a criteria is met.
The multiple worksheets have the same format. The worksheets are numbered 001, 002, 003, 004, etc (increase on a daily base) ..
View 3 Replies
View Related
Mar 7, 2014
I have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
View 9 Replies
View Related
Aug 26, 2012
I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?
View 1 Replies
View Related
Jul 22, 2009
Once a week I recieve a data file from another department and their report shows the period starting as well as ending but in text format all in one cell.
How can I extract the two dates to show the srart date in one cell and the end date in another. The example that would arrive in cell A1 is:
Reporting Period: 01/01/2009 00:00 to 07/05/2009 23:59
The hours and minutes do not matter.
View 9 Replies
View Related
May 9, 2014
Every week we produce a report that has the filename: Master_BER_Pending Revokes Report_YYYY-MM-DD.xlsm
Some of the information from this workbook needs to be pasted into a new tab on a different workbook. The new tabs naming convention is simply "DD MM". Currently I've got a command prop that asks the user to enter it in manually. See below
VB:
Dim newsh As String
newsh = InputBox(Prompt:="Enter new tab name", Title:="Tab Name", Default:="e.g. '05 05'")
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = newsh
The user gets the information for the month and day from the filename of the report that was produced. What I need is the DD and MM to be extracted from the filename and used to name the new sheet.
View 6 Replies
View Related
Jul 8, 2014
In cell A1 i have a drop-down list.
In this list are the following:
London [S] (14/02/2013)
Rome [W] (23/10/2013)
Tokyo [W] (17/01/2014)
Berlin [A] (24/02/2014)
...and so on...
In cell B1 I would like to extract the currently selected city name and the [?] (e.g. "London [S]")
In cell C1 i would like to extract the currently selected date (e.g. (14/02/2013)
View 6 Replies
View Related
Dec 19, 2009
I need to extract the date from the following file name format:
C:Documents and SettingsRezDesktopExcel forumQueue Performance Phone Daily15_Dec_2009.csv
i have macro running where this file path gets save as a string and i can paste this string in a cell, is there a way i can use a few extra line of code to extract only the date from this string and paste only the date? or the other way for this would be use formula to convert this to a date.
View 5 Replies
View Related
Jul 2, 2010
What I want to do is extract the date only from a string of text in a cell and put it in the cell next to it. The date is not alway in the same position within the text string and although similiar, the text string can differ. There are around 6000 lines in the sheet, I've pasted 4 as an example below.
THE BIG COMPANY LTD 15312178 COLLECTION 14.12.09
The Small Company Ltd 19073864 COLLECTION
DATE 17/05/10 511438 511437 511436 ORD LE
The Medium Size Company 19080948 COLLECTING WEDS 26/05/10
The Very Good Company 19149554 COLL DATE:28.06.2010 175 PEA PARK ROAD Z7 9AB
View 9 Replies
View Related
Jul 17, 2013
I want to extract date from string My dates are going to increase every 9 days. as you can see in table given bellow. And goes till Wk52
Wk1-> 02-Jan-11
02-Jan-11
Wk2-> 09-Jan-11
09-Jan-11
Wk3-> 16-Jan-11
16-Jan-11
Wk4-> 23-Jan-11
23-Jan-11
Wk5-> 30-Jan-11
30-Jan-11
View 2 Replies
View Related
Dec 26, 2013
Any formula that can extract from a time stamp text string, which looks as follows: 7/8/2013 7:10:33 AM.
The tricky bit is saying anything before the first "/" represents the month, anything between the 1st and 2nd "/" represent the date, and the 4 digits after the second "/" represents the year.
View 9 Replies
View Related
