Automate Cells Data
Feb 6, 2009
I have a spreadsheet that I need one thing automated on it (for now). It is a tracking sheet for our QA coordinators. What happens is they have a list of medical charts that are incomplete, when they mark the column complete with an "x" then the cell that contains and "x" under the Team Leaders name should disappear.
I have a code similar but I don't know who to use and array in the code.
This is what I used to change the date of a fields automatically (I recieved this from someone on line here)
View 10 Replies
ADVERTISEMENT
Oct 6, 2008
I have a macro in Module1 of workbook "BAU" called "ExtractToSheets" which exports data depending on the contents of a cell (Thanks royUK et al)
At the moment however, you must run the macro manually to export the data.
Now, i have put some simple validation in column K of worksheet "BAU Data" within the "BAU" workbook. Whenever a cell within a predefined range with column K is changed to "Yes" i want the macro to run AND i want the row the cell is in to be locked so it cannot be edited further by the user. I have had too many users mess up previous sheets i have made to collect my data, and i wish to make this one somewhat more idiot-proof!
Any pearls of wisdom folks?
I would have attached a representation of my sheet, unfortunately because there is now a macro in my spreadsheet, our trigger-happy security software won't let me post it!
View 14 Replies
View Related
May 3, 2014
I've got a problem with copying cells from table A to B.
A short insight of the situation:
Table A shows: ITEM_1 = 3, consequently ITEM_1 from table A is copied to table B for 3 times.
I've got a few huge information blocks to handle (up to 5000 items) obviously it's unreasonable to do this manually.
I've added an xlsx file with more detailed description.
Example_WHS.xlsx
View 8 Replies
View Related
Feb 1, 2014
I am trying to capture data for statistical analysis, but have hit a snag. As shown in attached sheet I have each day for the year broken down into hour time slots into which data will be added. As I will need to come up with a number of similar sheets in the future I was wondering if there is a way to automate through vba, a quick way to copy down the hour slots for each day, 365 times, rather than the laborious task of copy and paste manually.
View 5 Replies
View Related
Nov 5, 2007
I'm in the process of creating a budgeting spreadsheet for monthly expenses. I have one column (D) as "Paid" and column (F) as "Date Paid". Is there a formula that can automatically insert today's date of entry into the "Date Paid" column, once the "Paid" column has been filled in with an amount?
For example: I enter $20 in the "Paid" column, then the "Date Paid" column is self populated with that particular days date. I would like to do this for every sheet.
View 9 Replies
View Related
Oct 30, 2008
I'm trying to automate the process of exporting a range of cells to a text file. I think the Print# command will work because I don't want the quotes at the end of each line. Each range of cells will be a separate text file, and I want to save and name the file according to the column it's in. This bit of code from dominicb seems to be what I'm looking for, in principle.
View 5 Replies
View Related
Feb 13, 2014
AUTOMATE TRANSPOSE 2-13-14.xlsx In the attached file, I am looking to automate the transposing of the date and numbers under each bold number. Data is truck # in bold, the engine oil change date and mileage below. I copied the data from a pivot and need the date and mileage in columns, date on top with mileage below. I can do it with paste special one truck at a time, the big chunk of data is about 2000 rows deep and was hoping the transpose paste special could be automated, I've made a few attempts on how to do it but can't get it.
View 6 Replies
View Related
Feb 8, 2010
I m use Excel 2003 to automate the following sequence of events that I currently do manually?
AutoFilter to filter the City in column B
AutoFilter to filter the Rotation in column X
AutoFilter to filter the Positional Status in column Y
Hide columns B:W
Use the computer mouse to select a range of that includes columns A, and X to AR (inclusive).
Copy 'n' paste this selected range to worksheet: Data to Text at cell A1
Insert a new column by selecting column A and right-clicking on the column and selecting Insert
Populating the new column A starting at cell A1 with number 1 and autofilling down to the last record (1, 2, 3, 4, 5, etc...)
Delete the folowing columns: C, D, E, G, J, L, M, N, P, Q R, S, T and V
Copy 'n' paste the all the remaining data to a new documnet in MS Word 2003
Select the entire table in MS Word
From the MS Word file menu - Select Table - Convert Table to Text
In the Convert to Table to Text pop-up window - select Other radio button and type in a forward-slash "/" in the input box adjacent to the radio button (without the quotes).......................................
View 3 Replies
View Related
Feb 11, 2010
I need to extract for each individual dive: Onset/start of dive (when the whale has completed its surface interval and begins its descent)
Rate of Descent (meters/second)
Onset/start of bottom phase (the whale has stopped descended and begins the "foraging" phase of the dive...not necessary the deepest point reached)
Number of lunges (you can see these if you plot the data. They are lunges at depth indicative of feeding behavior)
End of bottom phase (begins to ascend)
Rate of Ascent
End of Dive (surface intervals begins)
I've attached a brief sampling of the data in case you're curious.
Over the years I have identified these points through visual inspection of Excel charts. It's very easy, but extremely tedious and not at all efficient. I am interested in expediting this whole process and am more than willing to learn programming (i.e. visual basic or the like)...but only IF it is the best approach for this application.
I have attached two dives worth of data, so we can see what the data looks like. Each data set can contain from 1 to 8 hours worth of data.
View 14 Replies
View Related
Sep 23, 2011
how to automate data from excel into word. This vba code takes every sheet from excel and puts it into a word document:
Code:
Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
[Code]....
My problem is that it doesn't style the data into a table, is there any way to do this with the code I have posted? perhaps with a .Style code or something of that sort?
View 1 Replies
View Related
Mar 30, 2009
I am trying to automate data import, instead of doing it manually all the time. I recorded a macro and now I am trying to edit it so that it can be used with any file.
Here's my code -
View 3 Replies
View Related
Jun 25, 2002
I put together a little table for one of my LAN based facilities to track their Overtime Savings as compared to each of their individual units. It works great.
What I would like to do is to export the data on a weekly basis to Access. I set up an access table to recieve the data and I can get it all to work just fine except I want the users to just click on a menu item and have the export take place in the background.
I know how to create on the fly menus and get them to accecpt commands from Excel Macros so that is not an issue.
I know how to share data in Excel from Workbook to workbook, closed or open. I'm sure it can be done Excel to Access I just can not for the life of me find anything to jumpstart me in the right direction in either application's help files. Anybody out there linking their Excel data to Access?
View 9 Replies
View Related
Jan 21, 2010
I recorded a macro to open the Form on the Data Menu. Sample Worksheet Attached. After starting the macro recorder I selected the Food table cell (located to the right on the worksheet). Then went to the Menu bar.... Data Forms... and Clicked new.... followed by close and then stopped the macro Recorder. It produced the following code.... which reflects a code error when run.
Sub McrEnterFood()
'
' McrEnterFood Macro
' Macro recorded 20/01/2010
Range("P1:U16").Select
ActiveSheet.ShowDataForm
End Sub
View 2 Replies
View Related
Jul 18, 2014
Excel spreadsheet, that contains several columns. Three of these columns contain text data that is selected by using a simple drop-down menu sourced from a list. If an attempt is made to enter text data not in the associated list an error message is displayed. Easy so far.
I need need to extend the text data validation across all three columns so that only specific combinations of the text in the three columns can be used which is reflected in a further list. These combinations will be counted elsewhere and so needed. Therefore, if i attempt to enter a combination of text columns that is not reflected in the list, this too will present an error message stating 'combination not valid', or words to this effect.
View 2 Replies
View Related
Aug 8, 2008
Daily Iam Entrying 100`s Of Supplier Data And Material Detail,
I Want Data Should Be Poped Out While Entering In Dataentry Sheet And Data Selction Should Be From Ohther Prefilled Cells. Auto Merged Post Until 24 Hrs Passes;Here Is The File
View 4 Replies
View Related
Apr 18, 2010
I have the office 2007 Suite.I have a small business, where i sell online mostly. I'm creating a simple spreadsheet using Excel 2007, a workbook that has simple financial statements.
I'm creating a list of customers along with what they order, what they paid, how they paid, (paypal, cheque) etc, The first column is the invoice number, and each is unique to each customer. I add as i get orders. so my list of invoices has the following fields as column headers
Quote:
Invoice Number Customer Name, Paid by Paypal, Paid by Cheque, Amount, Cusotmer email, Customer address, There are more and i won't list them all. Now each time i send an order, i have to fill in the excel worksheet. I then create the invoice separately in Word 2007 which i print to send to the customer with the order in the mail. The third step is sending an email to the customer upon despatch, using Outlook 2007.
I don't want to use accounting software as that is too complicated for my simple little business. What i do want to do however is combine all three tasks instead of having to open different programs and copying and pasting between them. I'd like to be able to add the data to Excel either using a form (ideally) or direct, then i'd like to be able to click on the "customer email" field to send a confirmation email to the customer, and also to be able to have an automated invoice, either in excel or Word, which draws the information automatically for each invoice, so that i only have to print it.
At the moment i'm doing all 3 tasks separately, when i know they can be automated.
1. I have about 20 columns of data for each invoice, ideally i'd like to be able to enter them from a form as it gets cumbersome going through them all in Excel. The form could be either in Word or Excel, which would be easier?
2. Using the invoice number as the unique field, how can i create an invoice that would draw out the required fields from the Excel data automatically, just by clicking a button? I dont' mind using either Excel or Word, whichever is easier, and i do have Access too, but i don't know if i should use that, or not. I don't need to draw any other data. I only want to print invoices and send confirmation emails.
3. After printing the invoice and posting the order to the customer i sent them an email confirming. Again i have to manually enter or copy and paste data in the email, which i know could be automated, if i create a template, and have some fields that are automaitcally populated using the fields in the date already entered.
These fields ususally include the total paid, currency paid, address, name and how many ordered. All of this could be automated, but i'm not sure how to do it.
View 9 Replies
View Related
May 6, 2014
Data Validation setup: "Allow" is: List "Source" is: =$E$12:$E$15
D___E____F
12__CA__CASH
13__OT__ONLINE TRANSACTION
14__DD__DIRECT DEPOSITE
15__AW__AUTOMATIC WITHDRAWAL
The codes in E column are what is entered in a worksheet range (user cannot see the list). Needed is to show both the two-character code in column E and the text definitions in column F in the dropdown. As data validation now works, users only see the two-character code and are not remembering what the code stands for - they need to see the definition.
Is there a way to show both the two-character code from column E along with the text in column F? When user selects, only the code would be entered in the target cell. (Is data validation the correct technique?)
I have set this problem up in closely located cells to construct the issue. In reality, the validation list is well off-screen from where the user is actually inserting the codes and there are many more codes/definitions.
View 3 Replies
View Related
May 21, 2014
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies
View Related
Jan 26, 2010
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
View 2 Replies
View Related
Feb 11, 2009
I have a workbook that I use for my purchase orders which is pretty simple. Each row is a separate PO, with 10 columns (po#, description, cost, date, etc.).
Is there a way to automatically generate new workbooks that contain all of the info from each individual row? For example: if I have 100 rows on my spreadsheet, which means I've issued 100 POs, then I want to have 100 individual xls workbooks, with the filename for each workbook representing the PO #s (206904.xls, 206905.xls, etc.).
View 14 Replies
View Related
Feb 22, 2007
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example:
A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
Could this be done by date formula?
View 9 Replies
View Related
Nov 9, 2011
I have a VBA Macro that starts with a user form that gives me two options. I always need the same option selected, so is there a way to automate this? I want it to select Option B and click "OK" automatically. I don't want to get rid of the user form completely in the background because it has some other functions that need to remain intact.
View 3 Replies
View Related
Feb 2, 2013
Is it possible to set up 2 tables whereby if you change a field in 1 table it automatically cuts and pastes a row of info in to another table. Or possibly by clicking a macro hot button? E.g. An entry in 1 table is no longer 'live' so moves to the 'archived' table when a field is changed.
View 3 Replies
View Related
Oct 1, 2013
I have the table below and I am trying to automate the YTD calculation.
Jan
1
Feb
1
Mar
1
Qtr 1
3
[Code] ........
YTD
Need to automate.
View 4 Replies
View Related
Oct 27, 2006
I find that the picture toolbar opening automatically when I click on a picture useful (sometimes).
Is there anyway of automating other toolbars to open (such as 'chart') when I click on an object?
I've been looking at various add-ins in google - but can't find one to do that.
View 9 Replies
View Related
Jun 10, 2007
When I save an Excel workbook, I would like to automate it to also save as a web page in 3 different locations (and automatically close after save as a web page). I would like to be able to automate the save as a web page in the 3 different locations and close these web pages. But my Excel workbook (which I have saved as an .xls) I will close manually.
One location to save the web page would be on my desktop and the other 2 locations would be on different shared drives. Can this be done with ******* event or something?
View 9 Replies
View Related
Aug 22, 2008
My goal is to automate an excel userfrom. So from outside of excel, i would look to be able to interact with the controls (simply fill in a textbox for example). I'm thinking this may be possible using windows API (user32) and system hooks, but not entirely sure where to start.
I saw this previous thread, that suggests it may not be possible :
[url]
View 9 Replies
View Related
Nov 17, 2008
i have 110 different columns. i want to do the same thing to each of these formulas (ie i want to calculate things like ln (A2 / A1)
is there some way i can automate this process so i only nhave to type in my formulas once and it will automatically enter in the same formulas for the other columns?
View 9 Replies
View Related
Feb 27, 2010
I work in the accounting department. We have an excel workbook for each account with sheets for every month. These sheets contain a template with pre-formulated cells.
For example, the 2010 workbook for account XYZ will have sheets named - Jan 10, Feb 10, Mar 10 and so on.
What we do, is move and copy the previous month sheet, and rename it to the current month.
But each person is responsible for over 100 accounts, so it becomes rather tedious.
So I wrote a code which would automate this process.......
View 9 Replies
View Related
Oct 30, 2004
I have Adobe 6 and would like to automate the printing of Worksheets to PDFs using coding.
I want to control the location of the output i.e. specific directory. I can do this manually by selecting File...Print Select the Adobe PDF printer....go into options and deselect view PDF results. On printing I then get asked to soecify a location and all works fine....but how can I do this in code.
Unfortunately it also appears that when you restart Excel, the printer properties for the Adobe PDF printer are reset.
How can I code in the change in properties so that I can fully automise this task?
View 22 Replies
View Related
