I have a workbook with multiple worksheets and would like to use a macro to do the following:
1. get data from cells: c2, m29, n29 & o29 in individual worksheet
2. enter in summary sheet
3. do the same for next worksheet and put info on next row
I have approx 11 files in one folder and one master file with same format. 11 files are split user wise and user inuputting the remarks against the invoice in coloum Y and Z in their respective files. I want macro/forumul to collect all the remarks coloum from all users to master files against the respective invoice no.
user file format :
file name temp-1.xlsx
A B Y Z
USER
INVOICE
Remark
Follow-up Date
[Code]......
- I have total of 13 sheets in a workbook - 12 sheets represent 12 months with data; 13th sheet is single sheet in which i would like to get complete overview of 12 months
- each of 12 sheets has actually sales results for multiple products with following data: internal code, manufacturer part number, name, and qty sold in that month
- situation is that some products have been phased out during the year and some were introduced so each sheet is slightly different in terms of in which row certain product is located
What i would like to achive is to make 13th sheet (whole year overview) do the following:
- there is a list of all the products in it, each product has unique internal code - this code (from each line) should be used to find that code in each monthly sheet, then find its monthly sales value (copy it) and paste it in sheet 13 in cell that represents this product and particular month.
In other words i would like to see for each product what was monthly sales throughout this year, but avoid manually filling in qty for each product per month.
I bought 32 laptops out of my own moeny (used) to get my 5th grade students into the 21st century. I have several programs working on these laptops which output data in text and xls files. I would like to either:
1. Poll the files on each workstation at the end of the day and bring the data into excel (I have worked in VBA). Computers are named Comp01 to Comp33.
2. Better: have the workstations save the files to my NAS device so they are on one hard drive.
I would like to keep it simple (I'm a teacher not a good programmer.) Is the "Import External Data" the way to go for the text files? Can a macro go out and loop through the 32 laptops using that or some other add-in?
I have 2 excel files(X and Y), and I would like to collect data from file X to file Y.
.................
In file X, I have a products prices lists that I got from my supplier. It look like that:
column:
A = LIST NUMBER (NUMBER)
B=ITEM NUMBER (TEXT)
C=DESCRIPTION (TEXT)
D=PRICE (TEXT OR "CALL" OR PRICE LIKE $90.00)
E= LIST NUMBER (NUMBER)
F=ITEM NUMBER (TEXT)
G=DESCRIPTION (TEXT)
H=PRICE (TEXT OR "CALL" OR PRICE LIKE $90.00)
Rows of A,B,C,D,E,F,G,H start from 1 to 400
..................
In file Y, I have 1 column and it looks like that:
column:
A= MODEL NUMBER (TEXT OR NUMBER)
..................
The calculation I would like to do is:
pass in each ROW of Coulmn A in file Y and search if there is the same MODEL NUMBER (TEXT OR NUMBER) in file X at ROWS C,G (The DESCRIPTION (TEXT)) for example:
If in file Y Column A1 = FG45673 (MODEL NUMBER ) the macro will look in file X in Columns C,G to see if it finds this MODEL NUMBER. It can find text like: "3 pcs of FG45673 " so it means it find it !
THEN the MACRO should copy from the price Columns D or H to Column B of file Y.
In other words to collect the products prices from file X according to their model number field and to copy it to the same MODEL NUMBER of file Y.
in file X there are 2 lists of products prices as you can understand from my info.
I am trying to use VBA to copy data (Invoice Process information from a few different excel worksheets and paste it all onto one master sheet). There are probably 10 sheets and each sheet will contain varying rows but a fixed numbers of columns. One sheet may have 20 rows the next 50 then next 75 and so on.
Batch# PO# Invoice serial# Process details Skip reasons Processed/Skipped
With a formula, I don’t know how to read each page and paste into a master sheet.
I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.
Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx
I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.
How can I collect the data from these other pages?
we log all customer communication on a daily spreadsheets a report. Multiple projects are listed on that one sheet every day. Is there a way to extract project related communication to a individual project log adding the date from the daily report to the log. The goal is to generate for each project a log will have the daily (or any) communication for one project from the daily report. either adding a work sheet via project name or a separate file. Also is there is a new project a new project log should be created.
View 4 Replies View RelatedI'm trying to make a spreadsheet that looks up values for a number of individuals and if they meet certain criteria they are collected in a box.
It is for a class of children's test results. I would like to be able to write the results each child got across the page (names at side, headings across top). If a child gets, say, level 2a in his test, his name appears in a cell elsewhere. I can do this with an 'IF' statement... Thing is I want to collect all the children who got that level in the other cell. The cell might end up with 10 or 12 names in it as a result of their scores being looked up. I would have cells for all of the levels, so the children can be automatically grouped together by attainment.
I have an excel file that contains around 8 sheets, each have a table that contains data. I want 2 master sheets that can automatically update itself if i enter data in any 1 of the 8 sheets.
master sheet 1 = summarized sheet that contains lesser columns with only major details.
master sheet 2 = contains all columns of all the 8 sheets.
the 8 sheets have unique names and i want those names to be in a drop down list in master sheet (summarized ) so i can select which ever sheet i want summary from.
Also i used tables for filtering data as i find it easier to track records from filter.
please annotate the code to explain the workings and how it all fits together,
View 10 Replies View Relatedi am trying to do collect data from 4 different spread sheets (they all consists of the same columns but they do not have the same amount of rows) and rank them based upon one of the columns in a fifth spreadsheet. in the fifth sheet i also want to display all of the information found in the four sheets, see simplified example below. Is this possible? and how do i do it? when i googles it i only found ways to do it using macro but i know nothing about macros..
Simplified example:
Sheet 1
A 3 W
B 5 X
Sheet 2
C 2 Y
D 7 Z
What i want excel to do
C 7 Z 2
B 5 X 1
A 3 W 1
C 2 Y 2
I am trying to collect data from a darts team and these values reflect
the number of points won in a team game (ranging from 0 - 3). This will
probably be simple for the experts here, so I'll let fly:
I have a table with six names across the top. In the rows below there
will always be two cells that have number values ranging from 0 - 3
while the rest will be blank. Those two numbers however, will always be
the same. I am trying to write a formula that will determine which
columns have values and return a single letter value (first initial)
for each name. Since all the names are different that is not an issue...
My company would receive RFQs from potential customers inquiring the availability of different products, and we are trying to summarise these requests to find a trend.
Each product has several attributes, such as descriptions, keywords, manufacturer's code, etc. The RFQs are all different, with each request providing some attributes while missing others. The only reliably uniform attribute is the product number.
I am trying to build a database with the maximum amount of details on each requested product, therefore consolidating the different requests. I already put the source data on the same sheet and sorted the product numbers. I also know how many instances there are for each product number. In other words, my data look like this:
Product number (sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
[Code] .........
Note:
1. The product numbers and repeat count are reliable
2. All the attributes are valid, i.e. as long as one can fill a blank I would take it
3. If none of the instances provide a certain attribute, it is acceptable for it to be left blank
And I am trying to turn it into this:
Product number
(sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
bbbb
123456
1
abcde
100x
3
123456
wxyz
I already spent a whole day trying to do it with MATCH, OFFSET, VLOOKUP etc to no avail. It seems I would need some kind of VBS with loop and array functionalities that are beyond me.
I'm working on a spreadhseet which has several macros that can take up to 5 minutes to run. When the macro(s) complete, I pop up a userform to provide instructions and collect some additional data from the user.
The "problem" is that when the user moves to another application to do other work, the user form remains hidden behind the active app. I've added some text to the status bar to let the users know the processing is done, but they would like something more obvious - so, is there any way I can force focus back to Excel?
I'm importing data from the internet. The cell I'm trying to get is B8 on "Temp_Data" onto cell E3 on "Other Data". When it imports the cell into Temp_Data for some reason the cell expands, and I can't have that. I would manually copy and "Paste Special", text, into the proper cell, and that works. I can also copy the fist 7 letters in the string from B8 and paste them into E3. My code currently looks like this:
View 2 Replies View RelatedI have a need to collect cells from different worksheets based on several criterias.
The worksheets all have one column and cell value in common, "Project name".
I need to collect some cells from different worksheets and visualize them on a new worksheet.
The cells I want to collect is:
- Project name
- Status of Project
- Income
I have been able collect these data. But the trick is that I need to sort these collected data on "Status of Project". The status of a project can be one of three: Committed, Uncommitted or Unspecified.
So what I need is three bulks, something like this:
HTML Code:
Committed
"Project name" "Income"
"Project name" "income"
Etc.
Uncommitted
"Project name" "Income"
"Project name" "income"
Etc.
Unspecified
"Project name" "income"
"Project name" "income"
Etc.
The point is that this can be done manually, but to have a macro doing this would obviously save ridiculous amount of time for me..
Is there any way to force some sort of garbage collect in order to free up memory? I am running some code which takes values from one workbook and pastes them into the control workbook. It currently works when 15 sheets of data are being moved however, my data has just grown to 16 sheets and now the code gives me the not enough resources message. If a garbage collect would solve this issue would I need to also reset any variables I had defined in the code?
View 9 Replies View RelatedI have attached my particular spread sheet I an referring to.
I set up a spread sheet to write out all the material I buy to go into a product I make. I work my way through a layout diagram and type all my data in to a spread sheet, one row per item I need to buy.
Now I want to order everything. Is there a way to sort the spread sheet so it brings all my like items from Column A (item) together, so I Can count the quantity required to order?
I have tried to sort, but am worried I might be messing up the rows and column - all the row information must stay together for it to work.
567 BOM REV 0 - 16 12 2013.xlsx‎
I have two worksheets: "1" and "2" Now i want to filter all symbols used in Column A sheet "1" and list them in Column A of sheet "2". The results should contain no duplicates. For example:..........
View 5 Replies View RelatedI have a spreadsheet here that collect information through vb, now when the first agent is done with the information needed and click on the save command button the information is saved in the spreadsheet.
For example:
A | B | C | D | E
1 Date | Name | Birthdate | Gender | Age
2 11/20/11 | Greg White | 04/12/1977 | Male | 34
3 11/20/11 | Greg Blue | 04/12/1977 | Male | 34
4 11/20/11 | Greg Green | 04/12/1977 | Male | 34
5 11/20/11 | Greg Black | 04/12/1977 | Male | 34
Now theres no problem with this instance.... the problem arise when the second agent do the same thing..... when i check the spreadsheet the information was overwritten.
What really should be happening is that when the first agent click the save command button the spreadsheet will be saved so that when the next agent click the save button it will go to the next line.
Another thing that i noticed, since the spreadsheet is shared through the network, it takes time before the information is saved.
1. When Save button is clicked the spreadsheet will be saved.
2. A code (if there is any) to speed up the saving of shared excel file.
I want to select a cell that contains a job number, use the text from that cell as the new file save as name (got the file creation part sorted out) and then continue from there to go back and create a hyperlink in the original cell to the newly generated spreadsheet. I know it involves variables, have tried things like currentcell.value but can't get what I want.
View 9 Replies View Related Date = InputBox("Enter the production month date in YYYYMM format", "DATE") 'This is the only info I want them to enter
Windows("PCN4.xls").Activate
Sheets("Bring the data in here").Select
Range("A" & NextLine).Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;W:C30MonthlyMonthly NEW TCs200610 otconsa.txt", Destination:=Range _ ' I only need the 200610 to change every month
("A" & NextLine))
.Name = "totconsa"
End With
I create a macro which ask the user to input a formula(user can select a range in the excel screen), then I copy this formula to a specific row of every worksheets.
The problem is Application.InputBox("Insert a Formula", "This accepts Formula", 0+2) can not return the formula, it will automatically evaluate this formula and return a value!
This code is:
Sub CreateNewItem1()
Dim bFormula As String
hi=10
Application. ScreenUpdating = True
' here is the INPUTBOX-method :
On Error Resume Next
Application.DisplayAlerts = False
bFormula =Application.InputBox("Insert a Formula", "This accepts Formula", 0+2 )
On Error Goto 0
Application.DisplayAlerts = True
MsgBox "You have inserted bFormula is:" & bFormula
'Cells(hi, 2).Select.FormulaLocal = bFormula
' MsgBox Cells(hi, 2).Select.FormulaLocal
Call CreateNewItem2((hi)) 'call the copy formula macro
End Sub
I was wondering how I would go about witting a vba for that can be connected to a button which would allow me to enter a row of input.
Some of these values I would like entered for example are: Quantity, Shape, Weight, length....
I would like the user to be prompted to enter these values and then have these values entered into columns, ABC... in a row on the spread sheet.
The catch is I would like this new date to be entered on one sheet via its weight (so that lightest material is on top), on another sheet in this workbook entered via its location so material in the same locations are adjacent to one another, and then finally I would like this updated onto another worksheet.
I have a question around making a summary table from a model. I will try to explain this as plainly as possible.
I have a model where you can toggle 3 different inputs across two variables. The final output number of the model will vary accordingly.
I would like to create a 3X3 table with the final output for each combination of variables. Is there a way of doing this using formulas or would I need a macro?
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
I have userform which has got a variable no. of comboboxes. Now I want to collect the selected values of all these comboboxes in a dataunit. There fore I am using the following code
View 2 Replies View RelatedI would like to search a specific text, ex "Change" on all column C on all existing sheets and copy all rows and collect into one Sheet.
View 5 Replies View RelatedI have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]