Code To Search For Items Copy To Different Sheet And Delete Them
Jan 27, 2014
I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".
I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.
There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.
Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).
Transfer Macro : [Code]....
Delete Macro : [Code]....
Overall Macro : [Code]....
View 7 Replies
ADVERTISEMENT
Oct 25, 2006
I have a worksheet containing code to hide itself when the sheet is deselected:
Private Sub Worksheet_DeActivate()
Sheet16.Visible = xlSheetVeryHidden
End Sub
After unhiding this sheet and modifying it, I copy it. This new copy becomes the 'production' copy, and the self-hiding sheet is blanked, leaving me with a hidden blank template and a new sheet. However, the self-hiding code is copied along into the new sheet. I'm looking for a way to delete the sheet code from the copy. The best I've found is how to remove a module from the workbook.
Sub New_Sheet()
ActiveSheet.Copy After:=Sheets(1)
ActiveSheet.Name = Range(" F8").Value
Sheet16.Visible = True
Sheet16.Select
Range("B13:F22").ClearContents
Sheets(2).Select
ActiveSheet.Shapes("Button 1").Delete
Range("A8").Select
ActiveSheet.PageSetup.PrintArea = Range("A1:R35").Address
Call Sort_Sheets
End Sub
View 5 Replies
View Related
Feb 22, 2007
Option Explicit
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
'(2)
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'(4)
Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup)
'(5) Give the control a caption................
I have created several spreadsheet with customized menus. In step 5 when I type
a period the options for selecting Caption does not appear. In step 6 the 'Control' word didn't appear in list. Sometimes the options in step 6 do appear BUT not the option for Controls. I have created spreadsheets with menus that work but the only way I could do it was to cut and paste from the website and then modify it.
View 3 Replies
View Related
Dec 12, 2013
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXY226#INITIATION MonthINITIATION DATECRM Transaction IDSales OrderSalesSHIP PlantCUSTOMERCITYST/PROVPRODUCTProductLbs InvolvedPROBLEM Complaint
[Code]....
View 9 Replies
View Related
Apr 14, 2014
I have worksheet that contains the wording "Total For Page" in columns A:N. I would like to find that wording delete that row and 3 rows below it. found the code below that works, but I have to continually run it to find the wording and delete the rows. The code doesn't search all and delete in one shot.
[Code]....
View 3 Replies
View Related
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies
View Related
Jun 26, 2007
The below code works well if it is on the proper sheet, if the active sheet is not active then it errors- Why? I think I have properly qualified the path to the desired sheet I wish to search on.
Error msg: Activate method of Range class failed
And how do I handle this?
Set Ws = Workbooks("ViewRenameDeleteFiles.xls").Sheets("Item Record List")
What = InputBox("Enter the Name You are Searching for its Record#", "Item Name Searching On")
Ws.Cells.Find(What:=What, After:=ActiveCell, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
View 9 Replies
View Related
Jan 26, 2014
I have a spread sheet with Column A = Document number, Column B = revision.
I am trying to get a macro that, when run, increases the revision letter in column B. The problem is that we do not use a few of the letters such as I, O, P and Q.
I thought it would be best to have a table of the used revision letters (Say in Z1 to Z40)
Look at the text in column B of the row currently selected and find it in the Z1:Z40 table
Move one cell down the Z1:Z40 table and copy that text back into column B on the row initially selected.
View 3 Replies
View Related
Aug 19, 2013
Is there a (relatively) easy way to use vba to search a whole workbook (5 sheets) for a value entered via a user in an inputbox and then if any instances are found the entire row of the sheet it's in is copied to a new sheet, then any further instances found also would repeat that building a single sheet view?
View 4 Replies
View Related
Mar 16, 2009
How do you delete a single sheet from a workbook by name? I assumed something like this, but it doesn't work
View 2 Replies
View Related
Oct 2, 2009
I want DELETE the rows that contains 0 (zero or -) in column F (SALDO BRUTO), when I click the Button (Clear). And How I can copy from this sheet to a new sheet in this workbook, but the names of new sheet automatic rename to next date or 2, and next when I click the Button "Copy to New Sheet".
View 2 Replies
View Related
Apr 17, 2014
I have a main worksheet called 'uren'. In this main worksheet I load data every week for around 40 persons. Every person has their own sheet, named equal to their name in the main worksheet 'uren'.
What I try to figure out is how to copy alle the data below their name in the main worksheet to the sheet which is named equal under the right weeknumber.
My main worksheet is build up like this ( used --- to keep the numbers in place when posting )
Weeknumber 1 ---Andre----Wiebe----Kayleigh----Sandra----etc
Complaints --------23-------45--------87---------34---------
Help-------------23.56------45------12,89--------67-------
Call---------------44--------4---------56---------12-------
hours-------------2,4-------56---------6--------34,67-----
etc
When the data is loaded ( the numbers you see) I want to copy all the data below a persons name ( number behind complaint, call etc) to the sheet which has the same name as the person. the data has to be copied to the correct weeknumber I use in the main worksheet.
The 40 additional sheets are build up like:
Weeknumber-------wk1-------wk2------wk3------wk4------etc
Complaints
Help
Call
hours
etc
So the data has to be copied under the correct weeknumber. The data in the main worksheet is loaded with a function and I quess the data also has to be copied without the fucntion (?)
View 14 Replies
View Related
Jan 1, 2008
I have a workbook that is an export from an inventory database. Basically I need to search Sheet1 column B for any item with "DDC" in its value, select the entire row, copy/cut it, and paste it into Sheet3. It would be nice to have it delete the row from Sheet1 after successful paste. I want to stress the fact that I'm a no0b with excel macros and have a very small understanding of programming in general. I've tried taking a few different macros with similar goals and slapping them together, this is what I've come up with so far with still zero luck.
At the moment the error is "Application-defined or object-defined error" with "With Worksheets(1). Range("B")
Sub DDC()
With Worksheets(1).Range("B")
Set c = .Find("*DDC*", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & Worksheets("Sheet2").Range("A65536").End(xlUp).Row + 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
View 2 Replies
View Related
Jun 6, 2008
I need to search a sheet for a specific phrase "phrase". When the phrase occurs I need to copy adjacent cells into another sheet. Then, I need to continue searching through the end of the sheet for all other occurrences, copying them into the other sheet. For example: "Phrase" occurs in cell B2, B20, B42, and B82 of Sheet1.
When it occurs in B2 I need to copy C2 and D3 into Sheet 2, cells B2 and C2.
Then when it occurs again in B20 I need to copy C20 and D21 into Sheet 2, cells B3 and C3.
View 4 Replies
View Related
Mar 30, 2014
Example, I have a sheet named DATA1, I want to add a new worksheet, copy a certain range from the DATA1 sheet to the new sheet and rename it the same name DATA1 and delete the old DATA1 sheet.
Also, the sheetname will be unknown, the macro must get the sheet name first.
View 2 Replies
View Related
Sep 22, 2009
I have a Excel file with alot of data in it. I need a macro that will create a report for me and relive me from alot of manual work. The 1st sheet is named "Projects". This contains the search arguments. The 2nd sheet is named "Database". This is the sheet where I want to search in. The 3rd sheet is named "Report". This will contain the results of the search. So the going will be something like this:
Copy row 7 from "Projects" into row 7 in "Report". Then use the value in that row, column E (named Search code). Search for rows that has this value in column E in the "Database" sheet. Copy all those rows to "Report" sheet. Copy row 8 from "Projects", leave two rows of space and paste into "Report". Repeat the procedure mentioned above. Repeat this until reaching a row in "Projects" that has no value in Column A.
View 5 Replies
View Related
Sep 15, 2006
I am pulling from odbc into an excel sheet
Thier is one column with our Sales Order Numbers.
I would like to have a search box or box pop up asking a user to enter the Sales order number they need.
Once they do that it will search thru the list and copy the rows with that Sales Order Number, then paste them to another sheet.
I need help on setting the searching part up and selecting the range to copy....I think I can take it from thier after that.
View 9 Replies
View Related
Jun 16, 2008
I am trying to find a function that will search for a particular name in a range and return ALL rows (or, preferably, specific cells in that row) which have that name in it, onto a new sheet.
eg. Sheet 1
Fred 45m $50 6/2
Joe 30m $35 5/28
Pete 60m $60 6/4
Fred 30m $30 6/6
If this range was on sheet 1, I would like to show on sheet 2 all times and dates for Fred in consecutive rows. ie the result on sheet 2 would be: Sheet 2
Fred 45m 6/2
Fred 45m 6/6
If selecting individual columns cannot be done, then the whole row would be fine.
View 6 Replies
View Related
Jun 13, 2014
I have a workbook with 50+ sheets. Within each sheet are rows of data in column A that I'd like to search for specific text.
I'd like to search each row from every sheet for specific words (e.g. "7 days" AND "Monday" AND "Tuesday" etc.) then copy the entire row containing all my search text in a new sheet on column A along with the name of the sheet it was found in in column B and the row number it came from in column C.
What I am trying to accomplish is to search through all the sheets and post results in new sheets for each search string.
View 14 Replies
View Related
Jun 3, 2008
I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.
View 5 Replies
View Related
Apr 26, 2013
I would Need a macro which would Search a Keyword in the excel sheet and copies and pates the data in Cell "A2". for Example "Market" and then after the search it copies 12 rows upwards and 10 columns from the Cell that the word "Market" is placed. And then it copies 12 rows downwards and 10 columns from the Cell that the word "Market" is placed.
I have tried recording the same but it does not work if the Word "Market" is placed in different cell value.
View 2 Replies
View Related
Mar 20, 2008
I am using following codes to find and delete number in series.
HTML Option Explicit
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
If Val(Me.TextBox1) > Val(Me.TextBox2) Then
MsgBox "End number must be greater than start number"
Me.TextBox2.Value = ""
Me.TextBox1.SetFocus
Me.TextBox2.SetFocus
Exit Sub
End If
lVal = Val(Me.TextBox1): uVal = Val(Me.TextBox2)
Application.ScreenUpdating = 0
For i = 1 To Sheets.Count
With Sheets(i)
Set Rng = .Range("a1", .Range("a1").SpecialCells(xlCellTypeLastCell))
For Each r In Rng
If r >= lVal And r
View 9 Replies
View Related
Jun 20, 2007
I have a worksheet that is composed a header row, and columns of data (client call results).
Each client is identified by a unique ID number in column A, a seperate column F has the 'call number, from 1 to x'.
So a client can have a single or multiple rows of data. Each client group of rows is sequenced with call 1 as line 1, call 2 at line 2, etc. etc. as your proceed down the rows.
The entire worksheet is already sorted (with a header row) ascending by client id and call #
What I want to accomplish is this
1 - copy the existing worksheet (CallRecords) to a new worksheet (CallToday)
2 - in the new worksheet, scan down (or up) from first data row (2) to the last row {ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row} and delete duplicate rows of the Client ID retaining the row with the max call number for that client id.
3 - sort the new worksheet (CallToday) ascending by column (G) date, and column (H) time.
View 6 Replies
View Related
Sep 5, 2006
I have to rewrite this code so that when i copy the sheet and paste it, to paste one Toggle button with its assigned macro. I don't want the whole workbook cause there are one combo box, another four toggle buttons and a command button, that i dont' need in the new file. So only to insert one toggle button with its macro.
Sub FileSaveCopyAs_Click()
Dim strFileName As String
Dim theNumber As Variant
Dim theFirm As String
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
theNumber = Sheets(1).Range("F13")
theFirm = Sheets(1).Range("E1")
strFileName = Right(theNumber, 4) & " " & theFirm
ActiveSheet. SaveAs Filename:="C:Documents and SettingsSecretaryDesktopFacturi" & strFileName & (".xls")
End Sub
View 9 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Mar 6, 2008
I am having a pricelist that is coded, If someone wanted to copy the pricelist to a new file. Is it possible to just copy the tekst without the codes?
View 9 Replies
View Related
May 16, 2014
My vba code to copy row A110 of "Mod" worksheet to A9 in Table A of "Email Data" worksheet using some logic like - in Mod worksheet it searches for data i.e. "COUNT(DISTINCTM.TRANS_ID)" if found then it searches for "row selected" and it searches for data between the two . And then it pastes 34864 to A9 of Email Data worksheet . I have written code for this but it doesn't works.
Also i need vba code similarly for searching "CAP_ACTV_LN_SEQ" and then the values inside it to be pasted i.e. row 128 and row 129 in Mod worksheet to be pasted in table e in Email Data worksheet . Remember there are 2 rows but there can be any number of row anytime.
Also the vba code for "NULLPOINTEREXCEPTION'" is there in the sheet which is working fine. It can be used for your reference.
Button for Vba code to work is present in "Email Data" worksheet.
code for "COUNT(DISTINCTM.TRANS_ID)" given below also excel sheet attached : Worksheet .xlsm
View 6 Replies
View Related
Aug 25, 2009
I am trying to create a a command button using the control tool box that will take info from specif cells from sheet1, and paste them into sheet2 in specif cells, but the most important part is that it has skip to the next row after each paste.
for example take A1, B1,C1 from sheet 1 paste it to B1, C1, D1 on sheet2, then take A1,B1, C1 from sheet1 paste it to B2,C2,D2 on sheet2
View 5 Replies
View Related
Mar 25, 2007
Error:
Run-Time Error: 438
Object doesn't support this property or method.
Two things to note: This code was placed in the worksheet, something new I am trying and two the error line is not highlighting in yellow (as it normally does) indicating the line w/ the infraction.
Option Explicit
Sub QuickView()
Dim Wss As Worksheet 'Source Worksheet
Dim Wsd As Worksheet 'Destination Worksheet
Dim LRow As Long
Set Wss = Workbooks("TGSItemRecordMaster.xls").Worksheets(" Record Creator")
Set Wsd = Workbooks("TGSItemRecordMaster.xls").Worksheets("Quick View")
LRow = Wss. Cells(Rows.Count, "w").End(xlUp).Row - 4
Wss.Range("W3" & LRow).Copy Wsd("A3")
End Sub
[/code]
View 9 Replies
View Related
Nov 3, 2009
a macro or code to copy row B10 To another sheet as a record but paste it down a row as i keep overwriting previous paste, tried offset etc but still stuck. I HAVE THIS SAME PROBLEM ON 2 SPREADSHEETS NOW. All i need it to clone the top row everytime data is put there, but where the data goes to paste it down a row so creating a record. Also i need another macro to search the G10 cell of the same row for the word replace and if so copy that row to another sheet as well.
View 9 Replies
View Related
