I have a workbook that has a sheet for each month. Everytime something gets worked on in the shop, a line of information gets filled out in that months sheet. On a separate sheet in the workbook, I need to show what the top 5 things(according to how many entries there were for that item) that got worked on that month are. The info I need is all input into the same column, just different rows.
View 5 RepliesDaily 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
I have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.
I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
I'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
I need the information that is entered in a userform to be sent to a separate worksheet. Each time a user fills out the form, I would like the information to be documented on the next completely open row (ex. first form goes in row 2 (column headings are in row 1), second form goes in row 3, etc.). I don't know a ton when it comes to VBA, but I believe I have the code figured out to search for the next open row. Column A will always have data in it.:
Code:
dim currentrow as range
set currentrow=Range("A1").end(xldown).offset(1,0).entirerow)
What I can't figure out is how to insert the information to the correct columns. Each column has a header to it, and I would like the data from the userform to be put in the relevant column each time it is filled out.
For example, if column A's header was "First Name" and columm B's header was "Last Name", each time the form is filled out, I would like the first and last name (which was entered in the form) to be inserted into those columns (with each form entry on a separate row).
I think there might be a way to use the intersect method to find the row/column combination I'm looking for, but I'm not exactly sure how to do it. Using the first name example from above, it would basically look for the intersection of "currentrow" and column A, and insert the value that is entered into the userform.
I have been working with personal record data in multiple workbooks, using sumif, index, vlookup etc to show various required info.
What I now need to do is take all the records of people aged between 16-18 and list this in a new worksheet.
I can get all the records I need in a pivot table but it needs to be something I can add columns to in order to gather further info.
I'm trying to copy certain cells to a new worksheet that keeps a track of previous entries via date and name and location.
The code works somewhat, however the values are not pasted into the respective worksheet. I'll try to explain the code as I see it.
Sub Button2_Click()
So the first section Activates the sheet that data is entered
Sheets("Checks").Select
Sheets("Checks").Activate
ActiveSheet.Unprotect ("fizix")
If the value ST is found in the sheet, the code knows to paste values to another worksheet known as ST_Hist
[Code] ........
Here I have another macro that then deletes the data in the selected ranges denoted by "" or ":", this part of the code works, however I dont have my pasted values in the appropriate sheet!!
Sub ClearAll()
Sheets("Checks").Select
Sheets("Checks").Activate
ActiveSheet.Unprotect ("fizix")
Sheets("Checks").Range("C2:C4").ClearContents
[Code] ........
I have a worksheet designed to look like a form. That worksheet pulls data from an "Application Data" worksheet using INDEX and MATCH formulas. That part works great. The user enters an Application number on row 8 and it populates all of the other cells. In fact, in cell O7 (not pictured), I have it generating the row number the data came from (based on a MATCH function).
Here's my question--if they go in and change a value in one of the other cells, they should be able to press an "Update" button. That will then kick off a macro that goes to the "Application Data" sheet, finds the row that has the Application Number (from row 8) and updates the values based on what the user entered on this form.
How do I write the statement that selects the row based on the Application Number?
I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.
Sub HTSoutput1()
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
Sheets("Nuclei").Select
Range("B10:B17").Select.............
I have designed a spreadsheet and i want a seperate worksheet (sheet3 for arguments sake) to retrieve customer data from worksheet 2 - The data I required is the customer data currently contained on columns A - H and there are around 50 rows. (A2 - I51). I want the seperate sheet to identify entries that have today's date in column I and then list them in Worksheet 3.
Im having difficulties with the syntax for retrieving the data from a seperate worksheet. There may be several entries for the same date and I want to the seperate sheet to report all customer data in worksheet 3? Also, if the date falls on a weekend I would like to retrieve any data for the weekend on the Monday so all cases can be reviewed.
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
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 RelatedI 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....................
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.).
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?
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 RelatedIs 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 RelatedI 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.
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.
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?
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]
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?
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.......
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?
I'm trying to automate unzipping many .gz files on a monthly basis. Previously I used a macro a coworker wrote to do the unzipping using winzip (think he may have used some code snippets from this forum for it - seems like several sites have similar code shown in different postings). But now my office has switched over to pkware's SecureZIP, I can no longer use winzip, and my coworker has moved on. Can I manipulate that old code to work with SecureZIP somehow?
View 1 Replies View RelatedI'm trying to automate a sequence.
I was gonna explain it but I couldn't make myself clear so I took a screenshot:
sequence.PNG
folder_type is what the user types, (for example, the letter "A"), and folder_code is a sequence that should auto-generate according to what was manually inserted in the folder_type field.
What formula could I use for this process?
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