I have columns on Sheet 1 with a filters and I want a way to make a list on Sheet 2 of each item (with out repeating) that is in the corresponding column on sheet 1.
This will be programmed in a userform to create the list when the form is loaded.
The end result being this: CopyFilterList.png
This is an example and there could be up to 50 or more options.
I've linked excel to a PLC pulse and download columns of data and these columns are updated live. I would like to copy the data in these columns every hour and paste values only into another spreadsheet for analysis. This will have to continue ongoing.
View 1 Replies View RelatedI am using Excel 2010. I need to copy a list from any Column on the right to the existing Column. See the sample file.
View 10 Replies View RelatedI have a database (ATTACHED) which contain name, roll number, courses taken etc. I want to create different file for different courses showing the roll number and name of the student who have written their name as REGISTER in the course.
View 3 Replies View RelatedI have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
I am trying to write a macro where I have to select a name, copy it and search in a Pivot filter if a certain condition is met. I am stuck at a place where I am unable to paste the selected value as a variable.
Code:
For Each cell In .Range("I2:I" & .Range("I" & .Rows.Count).End(xlUp).Row)
If cell.Value = True Then
Range("G" & N).Select
Selection.Copy
Range("A3").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("QC POC").PivotFilters.Add _
Type:=xlCaptionContains, Value1:="selection.paste"
The portion which is in bold (Value1=) needs to contain my selection (which is actually a name)
i have attached a book with a column of data in it.
This column can change from month to month, i.e. it can contain more or less data.
I need to filter the column "Prog Provider" for "SIN" and copy all of the data to a new sheet.
This is a tiny sample as this usually contains over 25000 lines and I need to grab everything in col G related to SIN.
I attempted to record a macro, but as different data was put in it missed parts out!
i have three sheets in this workbook. i want to filter the the pupils with less than 20 marks in total at the end and copy it to the page named filter, from which i will go to the sheet1 which gives a remedial plan for ONLY those pupils who come in the filter page. this is all good but the problem is that when i update the Cont page, it wont update in the filter page.
View 6 Replies View RelatedI have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
If you open the attached file you'll notice that once clients are entered into the name field of the master worksheet that a new worksheet is created with their name based off a master template. What I need now is a way to create a "on-demand" "click" macro that will allow the user to select a month (Jan-Dec) based on my master tab and autocreate a summary worksheet named that month with each client listed on the Team Roster worksheet in the next available column of the worksheet that was just created.
macro generate a prompt to list the months to allow updates frequently.. so if the same month was run twice it would overwrite the previous.. the woman we're doing this for isn't very excel literate...
NOTE: As you enter names on the Team Roaster sheet it will autocreate tabs.. test file has MrExcel as the worksheet, if deleted the sheet will delete automatically.
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
I would like to paste values from a list into a different list - different tab - that is already filtered. When I try to do so, the values will paste into the hidden cells - being filtered -.
Find attached an easy example of my problem. The list I am talking about is around 1,000 lines.
Pasting Example.xlsx
To explain my example, I filter sheet 1 so only item I need are showing - B, D and F in this case - and I would like to paste the value of Alarm 3 from the Sheet 2 to the corresponding line in sheet 1. But it does not really work - line F is not filled - and when I un-filter values are filled into Item C and D.
Is there a way to take a column of numbers, say UPC numbers, and break out each number separately to paste into a browser window for a web search?
Would this require a macro?
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
I have a very large list of top scorers for a sports team MS Word. The name and number of goals are both on the same line. Is there any possible way I can past them into Excel, so as they are pasted into 2 separate columns?
View 2 Replies View RelatedI have a list of names in a Word doc that is set up like this:
John Doe
Jane Doe
Doug Birch
Mary Birch
Is there a way to copy that list and paste it into Excel and make each name go into its own cell.
Example:
A1 John doe
A2 Jane Doe
A3 Doug Birch
A4 Mary Birch
I have the below code, I want to end up with values in column 'A', and a hard coded value in column 'B' for each row in column 'A'. But, I'm ending up with the hard coded value just in the first row of column 'B'. In other words, I want to repeat a value and copy it down column 'B' the same number of times that there are values in column 'A'. Here is what I am working with so far that is producing just the value in the first row of column 'B' -
Private Sub dispnames()
Dim b As Integer
Dim r As Integer
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
I have two Spreadsheets, lets call it Spreadsheet 1 and Spreadsheet 2.
Spreadsheet 1 has a list of partial employee names in column A.
Spreadsheet 2 has a list of 500,0000 full employee names in column A and their phone number in column B and address in column C through E.
Basically what I want to do is do a wildcard search for the partial employee name, so like lookup Spreadsheet 1' Column A's *name* in column A of Spreadsheet 2. Based on the number of matches. I want to insert that many blank rows underneath the partial employee name, and paste those matched rows there, then continue to the second partial employee name lookup and repeat the process.
This loop has to repeat 1000 times.
I have a consolidation workbook and source files.I would like to convert the data from the source files into a list format in the consolidation workbook.
I have attached a sample of the sheet format of the source files in the attached file, called 'Page 5'. The other 2 sheets are 'Template' and 'Instructions'. The 'Template' sheet is what I imagined would be the list format of the data copied from the 'Page 5' sheet. Instructions is where the lookup table for currency is.
So basically starting from row 8 in 'Template' sheet, I would like to copy and paste from 'Page 5' sheet to 'Template' sheet:
- H2 to A8 & B8
- B2 to C8
- According to the list of currency in 'Instructions' sheet, lookup the currency according to operating unit in C8 and paste to D8
- D5-I5 to E8
- row A8-A23 to column F-N
I actually have had a similar problem before, which Derk has helped me here - link: Use Access or XL? (I've decided to start a new thread because this is a more relevant forum).
I have tried to modify the code but I am rather lost as to which part I am supposed to modify.. Note that in this code, the source data is in separate file instead.
Sub add()
Dim wb As Workbook, f As Worksheet, t As Worksheet, j As Integer, k As Integer, n As Integer
Dim mty As String, yr As Integer, d As Date, bu As String, cur As String, sTodo As Variant
sTodo = Array("Page 5") 'finish adding the names
Application. ScreenUpdating = False
Set t = Workbooks("Example1.xls").Worksheets("Template")
i = t.Cells(65536, 2).End(xlUp).Row
I regularly copy into notepad then copy that into excel because I don't want to carry over the text formatting. Is there a better way to do this? To set up the cells so that they don't try to carry the formatting over?
View 4 Replies View RelatedI am trying to copy information specifically addresses into an excel spreadsheet to turn into labels in a mail merge. My first column is a name and then the next column is the address. I need the address to be in one box, in three or however many lines, instead of three different rows because when I go to merge it into word, it comes out has several different labels and not all the information on one.
View 3 Replies View RelatedI have a sheet with data in columns A to F, sorted by column B
Column B contains a list of names which may occur once or up to one hundred times.
What i would like to do is look at column B and for each name select columns A to F and copy those cells to another workbook which is then saved and closed.
However I would only like a maximum of 20 rows at a time to be copied.
As an example
Bob occurs 16 times so those 16 rows are copied
Fred occurs 26 times so only the first 20 Fred's are copied and then the next 6 are done.
Wally occurs 54 times so the first 20 are done then the next 20 and then the remaining 14
If you want to copy and paste a row from a workbook to another workbook based on a critertia such as that column A must have the number '1213' in it which also has over a 100 other numbers, do you use absolute or relative? And what do you write in the macro so that it search in column A all the rows with '1213' in them and paste them into the other workbook?
View 1 Replies View RelatedI would like a macro that would analyze a range (H15:GI15) to see if they contain FH, FS, FB, FR, or FC and if they do, copy the related picture associated with the 5 variables and paste it in the cell.
I did this code and it worked, but the code is very long. Another problem I have is there needs to be a way to delete the existing pictures before the code runs too. Because the cells may change and the button that runs the macro would be labeled "update" or something to that effect. I don't really need it to run everytime the cell is updated, just when the "update" button is pushed.
I need a macro which goes thru the columns A to K, picks up the value in the filled cells and paste them all in 1 column on another sheet, like sheet2 A1 onwards. Attached is an example of the file.
View 8 Replies View RelatedI am a novice when it comes to excel, but I have a project due at work where I need to search out correct addresses and copy and paste them into the cells where the incorrect info already exists. Here is my problem, I have been working on this no problem for about a week now and today all of a sudden when I copy the text from my search result on google and try to paste it into the cell, it pastes the google link, instead of the text I copied. It does this for the entire row but will not do it for row after row, I might be able to get the next 5 or 6 rows to accepet the correct text, and if I try to just type the info in, it appears as a clickable link.
I cannot figure out what I did, if anything or how to fix it so that I can get this work done.
Basically I would like to copy rows where the name of the person (in Column A) matches a separate this.
E.g.
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Paul
01/2/1966
Lookup list:
Peter
Jim
In Sheet2:
Name
DOB
Peter
30/9/1956
Jim
09/1/1923
Note that there are many names (this is just an abstract) so I think it requires a macro.
I have a sheet which contains a list of products. I have created a check box next to each name with the linked cell in the adjecent column. I have formatted the cell so you can see the TRUE or FLASE.
I need to be able to select different products and then when I run a macro, it places all of the selected products onto the second sheet. I just need it so it lists the products in column A with no blank rows.
Can this be done using Index/Match? Would a macro which achieves all this be possible?
I'm working on a quality control project (for products with Model #'s "1098", "1190", & "1220") for the company I work for and I'm having an issue with a macro.
I have data coming from worksheet "Sheet1" AB6:AB21, that's getting "sent" to worksheets "1098", "1190", & "1220" E86:E101, and "Sheet1" AC6:AC21 -> worksheets in E151:E166
In "Sheet1" cell F4, you enter the Model # ("1098" "1190" "1220"). I'd like this data above to ONLY go to the corresponding worksheet as cell F4 and not ALL model # worksheets. Is there a way that I can do this?
I'm working on a quality control project (for products with Model #'s "1098", "1190", & "1220") for the company I work for and I'm having an issue with a macro.
I have data coming from worksheet "Sheet1" AB6:AB21, that's getting "sent" to worksheets "1098", "1190", & "1220" E86:E101, and "Sheet1" AC6:AC21 -> worksheets in E151:E166
In "Sheet1" cell F4, you enter the Model # ("1098" "1190" "1220"). I'd like this data above to ONLY go to the corresponding worksheet as cell F4 and not ALL model # worksheets. Is there a way that I can do this?