Delete From Found Value Downwards On Multiple Sheets
Dec 19, 2007
error in my code, its the final part of the jigsaw for me to complete my project.
I am trying to find a variable that the user types in my spreadsheet and delete columns from several worksheets. I'm really close but keep getting the above error.
Here is the code.
Sub DeleteEmp()
Dim RemoveEmp As String
''' CURRENTLY DOES NOT WORK FOR SOME UNKNOWN STUPID REASON
MsgBox "Warning. This process will result in the permanent removal of an employees datails"
FinalWarning = MsgBox("Should I continue?", vbYesNo Or vbDefaultButton2, "Confirmation")
I have 3 command buttons that I would like to do the following.
1 - I need to change the macro below to add the record to multiple sheets. (I've been told how to do it but I guess I just didnt hold it in).
Sub Copy() Dim rng, dest As Range Worksheets("sheet1").Activate Set rng = Range("A1:J1") rng.Copy Worksheets("sheet2").Activate Set dest = Cells(Rows.Count, "A:J").End(xlUp).Offset(1, 0) dest.PasteSpecial Transpose:=True Application.CutCopyMode = False End Sub
2 - How can I change this code to find the record by enetring the First and Last name (A1 and B1) click on the command button and some how getting all the fields for sheet2 (A:J) in sheet1 for that record.
3 - Change the code to delete the record (A1:J1) from all the sheets.
I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.
[Code]....
The code works perfectly but with one little issue. It fails in the first run always with the following error message.
VBA error.png
I need to comment out the line
[Code] ....
continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.
I'm building a course registration workbook and am working on the unregistration macro.
I have a seperate worksheet for each course, labelled by the course code.
There is also a page for each registered participant that shows what courses they're in.
The macro reads off the information on the participants page, and then should goto each registered course's page, look in column C for their name, find it, delete the contents of that row for Columns C to G (has their other information) and then shift all CELLS (not rows) below the now deleted cells up 1 to keep track of the order they were added.
I've tried recording my own macro and editing... but it doesn't work very well for this. I've also tried searching and come up with nothing, but if someone can look at my code, and/or point me in the right direction, it would be greatly appreciated.
For Each ws In Worksheets 'checks only course sheets that match the courses the individual is registered in. If ws.Name = unregisterCourse1 Or ws.Name = unregisterCourse2 Or ws.Name = unregisterCourse3 Or ws.Name = unregisterCourse4 Or ws.Name = unregisterCourse5 Then
'Should search for the variable "unRegister" in column C and select it. Cells.Find(What:=unRegister, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select
'Now needs to select the four cells to the right as well (C:G) and delete them
'Select all cells below from C:G and shift them up one. End If Next
I've been piecing a macro together from different sites and have run into a couple issues I can't find resolutions to. I have a excel doc for tracking paint emissions per week. I would like to have a button on my master tab that will add a row and fill the formatting from the line above across all 60 some sheets. Ideally I would like the same button to first prompt if you want to add or delete rows. When adding rows I would like it to add it above the button (there's 3 buttons and for 3 different sections) and if deleting ask which row to delete. If it can't be done in one button that's fine, I can live with that. The big thing I need is for it to replicate across all of the excel sheets. I've attached a copy of the document
I have a workbook with multiple sheets. I want to use VBA macro to delete the row where the first column is strikethrough. I try to the follow VBA code but it doesn't work.
Sub Macro1() Dim cell As Range Dim delRange As Range
I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).
There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).
I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.
Public Sub EditCurrentBlock() Dim rowcount As Integer Dim startrow As Integer Dim endrow As Integer Dim rowcountBal As Integer 'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but ' had problems so scrapped it)
'Reconfigure the GP Revenue block.................
I have two buttons with macros attached. One button inserts a new wave line and everytime a wave is inserted, the number of that wave follows on. I have another button which inserts a new store line. There can be multiple stores per wave. What I want now is another button which when clicked would bring up a listbox of all the waves so that I could select a wave to delete. Once I deleted a wave I want the waves which are left to re number themselves so that they aren't out of order. This would have to also delete each store which was underneath that particular wave.
I have a userform that allows a user to enter data from columns A to P. I successfully installed a macro (which I got from this site:[url]
that deny a duplicate entry in Column A. The macro then "clears" the duplicate value in column A, which is okey. However, I need help for a code so that the rest of the entries from Column B to P (within the same row) will also be cleared automatically. I tweaked the "target.value" quite unsuccessfully.
I have been trying to create a macro vba that will look at a specific row in the current sheet and if it sees a name I need to delete that row. But I am looking to also under stand what it is doing so I can use this script in other instances. I just bought some things off MrExcel's Store but i won't get this for a few days.
I need a VBA code that will search column H for any value that DOES NOT start with "9" (this is a character field). If it finds a cell that doesn't start with "9", I would like it to delete the entire row. It will need to repeat this process for every cell in Column H that has a value and then stop.
I have a list of email addresses in column A of a sheet1, and a list of "Do Not Send" email addresses in column A of sheet2. I'd like to have a macro that would delete any cells in column A sheet1 that were listed anywhere in column A of sheet2.
You can find attached the workbook I am working on.
In my workbook I have four sheets. Only two sheets are relevant in this case: "Sheet1" and "List". On "Sheet1" there in column A there is big range of codenumbers (highlighted with red). On the "List" worksheet I have a smaller list (highlighted with green).
I have a code also in module1 but the code is not working.
I would like my macro to do the following. Check the code number from the "List" worksheet (green) and search for it in "Sheet1" column A (red). If this codnumber can be found in column A then leave the number on the green list. If it cannot be found in column A then, delete it from the green list. If you open the file, you can see on the "List" worksheet that there are three code numbers in the green range. The upper and the bottom code number can be found on the "Sheet1" column A (red) range, but the middle number cannot be found. So if the macro would work correctly then it would delete the middle number, and leave the other two untouched!
I have one master worksheet with 1000 ID's. I also have 10 other spreadsheets with 1000 ID's. I need my macro to use the master worksheet and identify where the IDs are located among the 10 spreadsheets. If there is a match, then my macro should copy the entire row and place it in an output file (another spreadsheet).
The following code only tests the master worksheet and three other sheets (one being null/error is ID not found).
Sub Macro5() ' ' Macro5 Macro Dim Raw, Mix1, Mix2, error As Excel.Worksheet Set Raw = Sheets("RAW_DATA") Set Mix1 = Sheets("Mix1") Set Mix2 = Sheets("Mix2") Set error = Sheets("error") Raw.Select For x = 1 To 1000 For y = 1 To 1000 If Raw.Cells(x, 1) = Mix1.Cells(y, 1) Then.................
I have two workbooks, one is used for importing items to the site while the other is a monthly product list. In each of these I have a list of sku codes. I need a macro that will search each sku from the import to the entire product workbook. If it's found then delete the whole row from the product workbook. I have attached the examples below.
I need a formula or VBA macro to do the following:
I have 2 worksheets, namely sheet 1 and sheet 2. Sheet 1 contains data in columns A to H. Sheet 2 contains data only in column A.
I want Excel to check the data found in column A of Sheet 2 with those in Column A of Sheet 1. Hence, when an exact match is found in Column A of Sheet 1, Excel will delete that entire row.
I'm using this code to find values of "FEP MHS" or "LSD MHS" in column S and if column S containes either of these values it deletes the entire row from the spreadsheet. I need this to work on a spreadsheet that runs on a daily basis and each day it contains a different number of rows. I have used this code to successfully delete most rows that contain these values in Column S but for some reason it does not delete all the rows, typically leaving 6 - 7 rows that contain these values. I'm using Excel 2007 I need code that finds the last row used and deletes the entire row if these values are present.
Dim CelRSLHMHSD As Range, RngRSLHMHSD As Range, iRSLHMHSD As Long Set RngRSLHMHSD = Columns("S").SpecialCells(xlConstants, xlTextValues) For iRSLHMHSD = RngRSLHMHSD.Count To 1 Step -1 If RngRSLHMHSD(iRSLHMHSD).Value = "FEP MHS" _ Or RngRSLHMHSD(iRSLHMHSD).Value = "LSD MHS" _ Then RngRSLHMHSD(iRSLHMHSD).EntireRow.Delete Next iRSLHMHSD
Working with an existing macro from this forum. How do I create the array showing the number of sheets found? I know from a MsgBox that two sheets are found.
I found some useful code for copying specific worksheets into a new workbook based on the sheet name, which I have not been able to alter to suit my needs.
I have a workbook that has a Master Sheet that contains a summary of each claim (Name, type of claim, dates, dollar amounts, etc.) and a sheet for each specific claim.
I know I can use the filter feature on the master sheet to view all of the physical damage claims (and one column has a hyperlink to the worksheet for that specific claim). But every month I have to create a report that shows each type of claim, dates, and so on.
I would like to create a macro that can copy the worksheets to a new workbook based on the type of claim it is. I'm thinking something like for each ws in wb if range ("F15") = 1 & .range ("B4") < 30 days from today copy that sheet into a new wb.
Below is a generic and far smaller version of my workbook. The name column contains my hyperlinks to the specific sheet. Each sheet is also named based on the text in the name column (so John Doe's sheet would be named 'John Doe') and so forth.
i have a workbook with several sheets in it. i would like to make a userform were i could put a number in a text box eg E045698.then use a command button to search all the worksheets for that number and display the sheet number where that number is in another textbox.
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.
I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.
For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
I have a excel 2007 workbook that has 5 sheets "MASTER" , "RED" , "WHITE", "GOLD" & "BLUE". There are 7 columns in each sheet and the master has about 8,000 rows . In column D of each sheet there is a unique number (approx. 8 - 10 digits ) that I would like to at the press of a command button search through sheets "RED" "WHITE" "GOLD" & "BLUE" against the unique numbers in the "MASTER" sheet and if there any duplicates numbers delete the entire row but leave all the data in the master sheet.
