Using Macros For Custom Filter Giving 'Or" Criteria And Paste The Data In Sheet2
When I go one column and Click custom filter and give the command one number and or another numbers ( I Have attached an excel sheet with screen shot) This filters the data, and I need to copy the same and paste in the next sheet.
I have to do like this for about 20 times for 20 sets of data). I have already done this and pasted the data in sheet2. I did everything manually. ( sample sheet is attached) I need a macro to do this work for me.
When I run the macro If get 2 text boxes I can enter the numbers. and click ok,the data has to filtered in sheet1, and result has to be pasted in the next sheet.with the header. Again I will run the macro i will give 2 numbers and the result should be pasted in sheet 2 after the 1st set of data leaveing one row as blank. ( exactly like the sample data in sheet 2). If I run the macro for 10 times giving 10 different numbers, the result should be pasted one after the other in sheet 2.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Unable To Use '?' In Custom Filter
I have a column named remarks (amongst many others) and want to apply a custom filter on my data so that all records which have a "?" anywhere in the text should be listed. However I can't do that because in the filter dialog box, the use of "*" and "?" is for wildcards...
View Replies!
View Related
Can't Run Macros Off A Custom Toolbar Anymore
A couple years ago i made a giant spreadsheet and had 2 custom toolbars to run macros from to manipulate the sheet. This year i re-did the sheet, meaning all my old data was replaced (it's a giant schedule basically). However, the Macros in the custom toolbars stayed exactly the same and performed the same function and are directed to the exact same cells, rows, columns, etc.. But now when i hit the buttons on the custom toolbars to run my macros, i get the following message... all the time... ** A document with the name "NHLsheet.xls" is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that's currently open, or rename one of the documents. ** Trouble is, i have only 1 document open! There's something in the macros that thinks i'm trying to open a new document but i'm clueless as to where or why.. they are very simple macros that do not require the opening of any new workbooks.. All they do is point to another sheet within my open workbook, "NHLsheet", copy a particular row from that sheet, and paste it to a specific spot onto another sheet within my workbook, "NHLsheet". Here is a sample:
View Replies!
View Related
Automatic Custom Filter On Dynamic Data
I need a function/macro that will find all rows that have a specified value in column A and extract selected columns to a new spreadsheet. More, I need it to do it for every value in column A. I would also like it to skip creation of new worksheet if value in selected row and column is null.* I've been trying to combat this problem with advanced filters, which helped, but due to size of the data and range of values in column A it takes an entire day to process manually. Because the data is exported to another program after it's processed, it can't stay in the same sheet, also, linking back to the original sheet doesn't work because the data changes all the time.
View Replies!
View Related
Protected Sheets With Macros Giving Runtime Error 1004
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View Replies!
View Related
Giving '400' Error
Sub testDateCompare() Dim nmiDate As String nmiDate = CDate("23/4/08") Dim origDate As String origDate = CDate("23/04/2008 15:05") origDate = Left(origDate, InStr(origDate, " ")) Dim minDate As String minDate = WorksheetFunction.Min(origDate, nmiDate) MsgBox minDate End Sub Giving '400' error!?
View Replies!
View Related
Show Custom Error Message When No Data To Paste
Below is a macro I recorded to help copy 'Paste Special - values only' data between two spreadsheets that have different colour and conditional formatting in cells. However, if this is run and there is no data to paste, it throws up the End and Debug error. Any chance that someone knows that if there is no data sitting on the Clipboard, then a msg box would appear saying "Please select the data you require to be copied" in the message box. Sub data_input() ActiveSheet. Unprotect Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B5:AG4804").Select Selection.sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("B5").Select ActiveSheet.Protect End Sub
View Replies!
View Related
Special Cells Paste Giving Error
I am trying to use the Special Cells Goto and Paste, but I am getting an error saying 'this operation requires the merged cells to be identically sized' when I do the Copy the value that I want, Goto Special|Constants, then Edit|Paste Special|Values|Multiply. The cells containing constants are selected and if they are together, e.g. B7 & B8, then there is no problem, but if they are apart e.g. B7 & B9, I get the error.
View Replies!
View Related
Filter The Data By Each Store And Paste In The New Sheet
I have the master data in sheet 1 with the some details of different stores eg store 1, store2, store3 etc which are in Column D. I need to filter the data by each store and paste in the new sheet. If i use advance filter>copy to another location, the system is not allowing to select different sheet. is ther any way that If I run the macro, the data is filterd by Store names and the same data should be pasted in a different sheet with the store name. that is all the data related to Store1 should be pasted in Store1 Sheet.
View Replies!
View Related
Use Cell Data For Filter Criteria
I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.
View Replies!
View Related
Filter Data By Criteria Into New Worksheet
I have problem with a database, I want to export data that is linked on a autofilter to a new worksheet,but I don't achieve in doing this. basically I want to copy all the data of a column which is selected by the autofilter (but not on the column i want to export) to a new worksheet; the layout of the database is fixed and may not change. I have made a replica of the database showing the same kind of layout but with less data so it would like to export all data in column D that is actived from the active autofilter (which can change )
View Replies!
View Related
Copy One Cell And Paste Into Next Available Row On Sheet2
I have a live stock quote that refreshs every second (for reference, Sheet1A4), I would like to paste value A4 into next available row on sheet2 of the same workbook. If possible, I would like to be able to copy several cells on Sheet1 and paste them in various sheets within the same workbook. Can you help me out or point me in the right direction to a previous thread.
View Replies!
View Related
Show/Filter Data From Formula Criteria
Im looking for a way to select between multiple choices linked to one criteria. Eg criteria beeing Chain x Article 1, which should give me the option to show and select from multiple unique values linked to that criteria (in attached example values 1 or 30 in column AQ marked in yellow). If possible solution should be formulas/functions.
View Replies!
View Related
Advanced Filter For Data Not In Criteria Range
I m Playing around with AdvancedFilters. Using the code below I can filter for data in the CriteriaRange, but I want to filter for data not in the CriteriaRange. I can't seem to find out to do this. I'm wanting to execute this sub from a button on a userform. Sub Filter1() Range("Data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("CRng"), Unique:=False End Sub
View Replies!
View Related
Filter Table To Show Data Meeting Wildcard Criteria
My goal is for any of my team members to be able to use a macro to delete all of the unwanted text from a fairly large worksheet and only show their specific information. I created it for myself and posted a slimmed down version of it below. Instead of the macro deleting any rows that do not have BRANDON SCOTT in column D, I want a pop up box to enter your name, and then any rows that do not have your name in column D will be deleted. I’m sure it is something simple, but I have not been able to find another forum with this information. Sub MyTeam2() With Columns("D") . AutoFilter field:=1, Criteria1:="<>BRANDON SCOTT*" .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete .AutoFilter End With End Sub
View Replies!
View Related
Insert Rows Based On Criteria Match From Sheet2
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example: Sheet1 looks like this:...................
View Replies!
View Related
Exporting Macros To Another Workbook From The 'sheet' Code Module
I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the ' Sheet' code module to the 'Sheet' code module of another workbook. Sub Import_Macro() With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule strRet = .Lines(1, .CountOfLines) End With Set wkb = Workbooks("Test Workbook.xls") wkb.VBProject.VBComponents. Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas") wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas") With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule .DeleteLines 1, .CountOfLines '//Delete Codes already wrriten .InsertLines 1, strRet '// Write Code End With End Sub
View Replies!
View Related
VBA- Can't Paste Into A Newly Selected 'activecell'
I am new to VBA and was just wondering if there is logic behind this or am I doing something wrong.... ie, 1 Cells(1,1).Select 2 Activecell.Copy 3 Cells(2,1).Select 4 Activecell.Paste The above doesn't work but if I use the same above 3 lines with 4 Activesheet.Paste it does work What is logic here as to why it can't paste into a newly selected 'activecell'?
View Replies!
View Related
Macro- Custom Filter Containing
I'm trying to write a macro that will custom filter a column for cells containing the value in a cell, the macro I have so far is: Sub Filter() ActiveSheet.AutoFilterMode = off LookupVal = Range("C3") Range("A8").AutoFilter Field:=4, Criterial:=LookupVal End Sub However that only returns cells that are equal to C3, normally to do contains I would put "* *" around the value but then that removes the reference to cell C3.
View Replies!
View Related
Sharing Custom Menus & Their Macros
Our ERP system generates reports in CSV. I have created a custom excel menu, which houses a lot of buttons that launch various macros to format these reports. I have been asked to share these formatting macros with other users. I understand that the custom menu structures are stored in an excel11.xlb file. My thought was to simply replace the users *.xlb file. This only half worked. I get the menus and the macros, but when the users try to run the macros from the menu excel can't find them because the path is pointing to my user name.
View Replies!
View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E. What I am looking for 1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that. 2) I would like this validation/formula to be applied to all the cells in a particular column. 3) Nice-to-have. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
Macro Code To Filter Out 'Exact Matches' In 2 Columns
I have a large worksheet in drive C: called 'hits.csv' that has manufacturer part numbers in column G and column L. I need macro code to find all rows in the worksheet where the part number in each cell in column G is exactly the same as the part number on the same row in the corresponding cell in column L. The macro should then delete/remove all rows where there is no exact match. Note: The part number in every cell in column G is the only thing that is in each cell in that column. However just to warn you that the part number in every cell in column L is at the beginning of each cell, followed by a space and then a description of the part. Once it has done the above, I would also like this macro code to open a worksheet called 'partnumbers.csv' in drive C:. It should then compare the part numbers in column A of this file with the part numbers in the above worksheet ('hits.csv'). The cells in Column D of the file 'partnumbers.csv' all contains a price. Where the macro finds an exact match on the part numbers in these 2 files, it should copy the price from the cell in column D in the file 'partnumbers.csv', into Column K of the other file, 'hits.csv' where the match exists.
View Replies!
View Related
Using The AutoFilter/Custom Filter Function
I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg: Filter: does not begin with - 3 and / or does not begin with - 9. I want to add a third 'and' criteria .. is it possible, and if so, how? The column that I am trying to filter has numbers formatted as text.
View Replies!
View Related
Custom Auto Filter Command Syntax
How can I perform a custom autofilter operation on the Column A data shown below that will filter out any cell that does not follow a "#.#.#" format. In other words I only want to see Level 3 paragraph numbers that contain two periods and suppress out all the other levels (variations containing 3 or more periods). I tried the following syntax in the custom aotofilter field with no success - "^#.^#.^#" 3.2.1 3.2.1.1 3.2.1.1.1 3.2.1.2 3.2.1.2.1 3.2.1.2.2
View Replies!
View Related
Custom Function To Sum Visible Cells Clashing With Macros
I have a custom function that is interferring with VBA. If I remove the function the VBA works fine, if I put it back to do what it needs to do, the VBA does absolutely nothing. Any ideas on how to make things work together? Here is the custom function and it is in a module where the VBA that isn't working is in the main Workbook page, but it is a Worksheet Change VBA affecting the same page as the custom function. Function Sum_Visible_Cells(Cells_To_Sum As Object) Application.Volatile For Each Cell In Cells_To_Sum If Cell.Rows.Hidden = False Then If Cell.Columns.Hidden = False Then total = total + Cell.Value End If End If Next Sum_Visible_Cells = total End Function
View Replies!
View Related
Automatically Copy/Paste Data From Input Range Based On Criteria
I have a workbook with 2 - worksheets "DATA" and "LETTER". The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc. The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet. After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below. In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....
View Replies!
View Related
Custom Filter To Select A Value And Values Divisible By That Number.
As I have not tried to do this yet this is a hypothetical scenario. Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36? On the attached example sheet, column 'O' will be filtered.
View Replies!
View Related
VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1. Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
View Replies!
View Related
Custom Paste
I am working on a excel file to help me interpret survey data. I am having some problems transposing data from tab1 to tab2. So basically, I need all the data from Z on tab1 to be transposed on individual lines on tab2, attached.
View Replies!
View Related
Copy & Paste Macro Won't Paste
I want this macro to find in this case "406" in column A which is at the very end of the last block of 160 rows of information. I then want it to move up 159 rows and copy 160 rows of information underneath the last block of information. i.e.go to A5280 , then go to a5121, copy rows 5121:5280 to 5281. It falls over on the very last line of code I can see A5281 selected but it won't paste....
View Replies!
View Related
Code 'To Invoice' Copy The Filtered List, And Paste On Sheet
I have recorded a macro to filter data on sheet 'To Invoice' copy the filtered list, and paste on sheet 'Invoice' in C16. The code just keeps looping (not looping in a code sense, it just seems to keep flickering the screen like its going over & over) until it locks up 5-10 seconds-ish and then I have to re-start Excel. The range B2:E22 is not always populated, it could possibly be B2:E2 (one row), I dont know how to copy the exact data so I expanded the range to what I think would capture any eventuality....
View Replies!
View Related
Dde Data Copied From Sheet1 To Sheet2
I am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that. The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.
View Replies!
View Related
Coppying Data From Sheet1 To Sheet2 Using (VLOOKUP)
Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP? I had an interview where they asked me to copy data from sheet1 to sheet2. I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function. Please confirm if VLOOKUP can be used in this regard.
View Replies!
View Related
Pulling Of Data From Sheet1 To Sheet2 Or Any Backup
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
View Replies!
View Related
Copying Updated Data From Sheet1 To Sheet2
I have workbooks which have data broken out of a larger file, I have solved the issue of updating the "Master List" from these broken out sheets. My problem is updating sheets. This has been done cut and paste manual style for some time and I am trying to streamline the work since being assigned to this project. I am trying not to change the workflow so I have to keep the sheets and work around the setup. Sheet1 contains identical data as sheet2 with the exception of the completed work which is removed. In other words sheet2 contains an "assigned" list of work maybe 200 rows, this includes any work not yet completed in sheet1 as well as all the work previously completed but removed from sheet1 which may only contain 50 rows. Very simply, I want to parse through column A in both lists containing a specific ID number and match accross sheets, if column G and H on sheet1 match my needed criteria and column A from sheet 2 then copy that row from columns G:AT or 7 to 46, but only the values since there are formulas and formats I don't want in the updated sheet2 because the script I have for pulling from these sheets to the MasterList would fail. As a bonus to eliminate the need to run another bit of code if I could delete the copied rows from sheet1 after they have been sent over to sheet2 would be great. Headings for all the sheets are the same so the data is identical. Column A is a customer ID number, G assumes initials from the clerk, and column H is date work completed. So if record A found and G=XX and H=Date then copy this rows columns G to AT values only. After which rows copied over will be deleted from sheet1. Headings are in row one and data begins on row two accross all sheets.
View Replies!
View Related
|
TRACKER
