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 RepliesI have an existing macro which I am enhancing and I would like to have the user provide a date, either with a popup text box in a userform and then use a command button to hit OK and have the box dissapear, or in a calender which the date is selectable, and the date supplied go into a variable.
View 2 Replies View RelatedI 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.
please annotate the code to explain the workings and how it all fits together,
View 10 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'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 created a standard input box for the date (UK)
datereceived = InputBox("Please enter the DATE the text message was received, please enter in format DD/MM/YY", , Date)
Sheet1. Range("e" & x).Value = datereceived
I have formatted the cells for row 'E' for 'Date' and DD/MM/YYYY in English UK, But when I have inputted the date into the input box ( e.g. 01/05/2008 ), it inputs into the spreadsheet 05/01/2008 (American Obviously),
Let's say I have cell A1 on Sheet 1 of Book 1. I need this cell to be a range variable for a macro in Book Two, but cell A1 varies from time to time (A1 this time, A7 next time).
In Book Two, I write "A1" as the contents of cell C3, for example. How do I turn that string into a range variable for use, not the containing cell? Since the cell needs to manually changed each time I run the macro, having it as a text field somewhere on my Book 2 Sheet seems the only simple way to identify it but IS there another way? Selecting it as the active cell and using another subroutine defeats the use of the macro.
I am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
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 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'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 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?
I 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‎
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 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.
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 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.
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 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 a data where i need to collect a total quantity bases on different product. And these product will repeataly appear in a table several time. I have different datasheet every time i need to collect.
View 9 Replies View RelatedIs it possible to get a inputbox to validate an entry as time in the format of HH:MM
ie: user must enter a valid time in the above format before the macro will proceed to the next line of code