Find Value On Multiple Sheets & Delete Corresponding Range

Jun 11, 2008

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

View 5 Replies


ADVERTISEMENT

Add Or Delete Rows From Multiple Sheets Based Table Range

Aug 14, 2009

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.................

View 2 Replies View Related

Find Part Text On All Sheets & Delete The Column

Sep 26, 2007

I am trying to move through the worksheets and delete all columns with "Accession" in contained in them. I have to do this with an external macro as the spreadsheet with the data is created from another program. I tried this and get an 'Object or With block variable not set' error and the debug highlights the Cells. Find line of the code.

Also, the number of columns could be variable within the spreadsheet as it is compiled by the other program.

Sub DelAccessionNum()
Dim Wrkst As Worksheet
For Each Wrkst In ActiveWorkbook.Worksheets
Cells.Find(What:="Accession", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
On Error Goto Completed:
Selection.EntireColumn.Delete Shift:=xlToLeft
Completed:
Next
End Sub

View 9 Replies View Related

Delete Rows On Multiple Sheets Below

Apr 22, 2007

One more short macro code I would like to have.

I would like to delete rows on all worksheets in a workbook below where the value "Total" exists in column B (same on all sheets).

View 9 Replies View Related

Paste And Delete In Multiple Sheets

Feb 21, 2007

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.

View 2 Replies View Related

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")

If FinalWarning = vbNo Then

View 4 Replies View Related

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

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.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Delete Sheets Where Sheet Name Not In Range Of Cells?

Mar 13, 2014

I am looking for some code to scan my sheet names and delete all the sheets that don't have names corresponding to the values within a cell range contained on another sheet in the workbook. For example, if any sheet name, excluding a few reserved sheets, doesn't equal one of the values contained in cells A2:A23 on sheet "XYZ" then delete it.

The best solution I could come up with using my experience was to loop through each sheet and compare the name to each cell in the named range, but if it's possible to somehow declare the named range so that each sheet only has to compare to it that would be ideal;

View 10 Replies View Related

Macro Button To Add / Delete Rows Across Multiple Sheets

Jul 24, 2014

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

View 1 Replies View Related

Delete Only Cell Values (not Formulas) In Multiple Sheets

Nov 3, 2009

I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following

View 5 Replies View Related

How To Delete Rows With Strikethrough On Column A Among Multiple Sheets

Nov 24, 2013

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

[Code]....

View 3 Replies View Related

VBA - Find A Value In Multiple Sheets

Oct 2, 2012

In Sheet1 A3 values which I want to find value

In sheet2,3,4 are DB

For example I looking for AVAI and if AVAI is in Sheet2 copy or just showing a row where is AVAI value and these VBA should find in all Sheets (2,3,4).

Just I want to find data in other sheet and show in one sheet.

View 1 Replies View Related

Find Across Multiple Sheets

Jul 31, 2008

I am using the following code to search a worksheet for a 10 digit number (derived from input box). I would like to expand the search to more work sheets (i.e. Sheets(4) to sheet count). There will only be one entry for each input number in the above work sheets. What is the best way to ammend the code?

num = InputBox("Scan Transfer Tube")
If num = "" Then Exit Sub
Set r = Cells.Find(What:=num, After:=Cells(1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
Rows(r.Row).Copy

Sheets(2).Range("A2:S2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheets(3).PrintOut Copies:=1, Collate:=True

View 4 Replies View Related

Find Lowest Value From Multiple Sheets

Jul 13, 2009

Im creating a list of cash and carry places to buy drinks but im so clueless on how to go about doing it.

Heres the situation:

In sheet 1 I have a list of Drinks and the prices the shops are selling it for.
I have duplicates of the drinks so say for bacardi i would have one row with one shop with its price and another shop with another price. I cant put it into columns because there is other information such as the quantity the shop sells in one box etc.

On another sheet (sheet2) i have a kind of shopping list. This has all the drinks listed in Sheet 1 as mentioned above but NO DUPLICATES. What i need it to do is find the drink is Sheet 1 and pick the row with the lowest price from the multiple entries and copy that price and shop name over to sheet2.

I hope this is easy to understand. Please let me know if I need to explain some bits again. Im trying my best to figure out how to do this. I have no clue how to do it in Access.

View 14 Replies View Related

Find Across Multiple Sheets/Worksheets

Sep 3, 2006

Is there a way to use the Find tool (Ctrl + F) to search across multiple tabs (they may be called worksheets) in a single workbook?

View 2 Replies View Related

Find Highest Value Across Multiple Sheets

May 26, 2007

I have a workbook that contains sheets of sales data from year to year. Each sheet has the same data in the same range of cells. For example b2 thru b26 would contain the sales for Day 1 of a route system for each week of the year, and each sheet in the workbook contains the same data in b2 thru b26 regardless of the year (2006, 2007, etc). I would like to be able to have a cell that would contain the record high sales for that particular route day, but have a formula that would watch that column and change the value in the selected cell when a new high was entered.

Is there a way to check the range of cells for a high value, or would I need to check each cell against the current high value in the cell with the record high, and how would I go about constructing this formula? Or as I'm now thinking about it, would this be more of a job for a macro that would run when data was entered?

View 9 Replies View Related

Find Dates Over Multiple Sheets

Jun 21, 2008

I have 3 sheets in my workbook. (List, Half 1, Half 2)

I am using the following code to search from a list of dates which is in the list sheet, and then find them on the other two sheets.

My code works fine until the 7th date, and then I get a runtime error 91.


Sub Find_Date()
Dim Variable, Variable2

Sheet1.Activate
Range("Y14").Select
Start:
Sheet1.Activate
ActiveCell.Offset(1, 0).Select
Do While ActiveCell.Value <> ""
Sheet1.Activate
Variable = ActiveCell.Value
Sheet5.Activate.....................

View 9 Replies View Related

Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets

Feb 9, 2014

I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.

I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.

Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.

I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.

View 12 Replies View Related

Find Duplicate Values In Multiple Sheets?

Aug 23, 2013

I have 2 sheets that i need to find what the same valued cells are.

1 sheet has names and i need to find the rows where those same names are on the 2nd sheet.

The formats are different (upper-lower case) and there is additional text as part of the cells in the 2nd sheet. (i.e prod123.web.corp.com) and i just need to find the prod123 parts.

View 9 Replies View Related

Multiple Find And Replace Over An Array Of Sheets

Jul 6, 2007

I have a list of cell entries that I want to find and replace with different text or a number.
My code below is 4 of them.
I am trying to do the replace over 3 different sheets at the same time but I am only changing the 1st sheet with my efforts.

Sub Find_And_Replace()

Sheets( Array("Resolution", "Response", "Open")).Select
Sheets("Resolution").Activate

Cells.Replace What:="1 Widespread*", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="2 Critical*", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="3 Non*", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Sheets(Array("Resolution", "Response", "Open")).Select

Cells.Replace What:="4 Require*", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub

View 8 Replies View Related

Find Matching Value In Range On Different Sheet And Delete?

Jan 28, 2014

I am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project. It's a huge puzzle and is now my "baby".

Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)

Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?

Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...

View 2 Replies View Related

Find Matching Value In A Range On Different Sheet And Delete

Jan 18, 2014

I am an inventory specialist for a dish network company and as such I track inventory in and out of technicians vans, both serialized and not. I've done a great deal of work updating a broken excel sheet they use so that it functions again but I didn't build it. I've learned a lot but I'm only self taught with Excel and had never even heard of VBA code until I dived into this project.

Anyway, basically I have one sheet that has a list of all the items I need to keep track of. One section of this Sheet1 I've designed to have cells with dependent drop down lists that are Named Ranges on Sheet2. The tech can choose item A B or C in the first dropdown box and then the next cell shows only the serial numbers from the named range on Sheet2 of A B or C. (Was that english?)

Since the receiver comes out of the techs van once its used I want to figure out a way to delete the serial number that the tech has chosen without deleting the row or cell, just the value in it so that it can then have another serial number typed in. How can I do that?

Also, since I'm here, my 2nd drop down list seems to always start scrolled down and I have to scroll up to see my serial numbers. Why is that? The receiver list starts at the top but the dependent one doesn't...

View 4 Replies View Related

Find And Highlight Duplicate Values In Multiple Sheets

Aug 22, 2013

SM extract 8.21.2013 Cust-sample.xlsx

I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"

The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots

View 8 Replies View Related

Find And Replace Matching Cells Across Multiple Sheets

Dec 11, 2013

Following Excel task I am trying to complete:

I have an Excel file with multiple sheets and I want to find and replace matching cell data on the same row across all of the sheets. For example, I have two columns, Column A and Column C and 10 sheets. I want to only replace the content in Column A if text matches both Column A and Column C on the same row. So, I want to be able to search for the following data across all sheets:

Column A = "car"
Column C = "yellow"

If both "car" and "yellow" are found in Column A and Column C on the same row, then replace "car" in Column A with "truck".

Is there a way to do this automatically as I have few hundred to find and replace?

View 5 Replies View Related

Find Range And Delete Duplicate Value In Specified Column With VB Macro

Apr 21, 2009

See attached doc "Find and Delete.xlsx". Inside, the value in F12 is a very important cell. Using the text value from F12, I first want to use visual basic code to find the range in column B because it will vary. This means the 'text words' column might have 50 words or it might have 2000. I have racked my brain trying to find a universal way to find a variable range.

Then I would like to search for the exact text value within this range in column
B. For example, in F12 is the text word 'windows xp registry cleaner' and you can see that this value is in B9. There will only be one exact match in column B.

I then would like the vb script to delete and remove this found value, then move left one space in column A to also delete the ID number associated with the text word. Then shift both rows up to fill the empty gap. I have tried numerous different codes but to be honest, I'm not really sure what I'm looking for. I've tinkered with find and replace, vlookup, match, find, search, and many more.

View 2 Replies View Related

Search Multiple Sheets Within Report And Then Find Average Of Result?

Jun 6, 2014

Average.xlsxHi

I have changed the attachment to use CSV's

I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.

What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.

Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.

Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1

I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)

View 5 Replies View Related

Find Common Values Across Multiple Sheets Based On Unique ID

Oct 24, 2013

The old thread is here: [URL] ....

There are three sheets in the workbook, Project, Tasks and Details and the expected resulting sheets are RESULT, In_Tasks_but_NOT_in_Projects and In_Details_but_NOT_in_Projects .

But now what I am looking for:

1. Copy the Projects data as is in the RESULT sheet.

2. Then in the Tasks sheet, if the ID matches paste the matching rows under the data from Projects (as in the result sheet with Orange colour)

3. If the ID is present in Tasks but NOT in Projects then copy it into the In_Tasks_but_NOT_in_Projects sheet.

4. Then If the ID and the Name in the Details tab matches with the data in the RESULT sheet then paste it under the ID and Name (as in the result sheet with Green colour)

5. If the ID does not match the ID in the results sheet then copy that row into the In_Details_but_NOT_in_Projects sheet.

The result of the current macro that RHCPgergo worked with are in the last sheet.

The formatting and colour of the rows doesn't matter, it is more of nice to have.

View 14 Replies View Related

Find Data & Paste In Pervious Column On Multiple Sheets

Dec 14, 2008

I have a workbook that has 397 sheets. On many of the sheets (but not all) they have some of the same data. On sheet 1 cell B5 is ContactID, Sheet 15 cell B32 is also ContactID. Sheet 1 A5 needs to reflect Contact ID and Sheet 15 A32 needs to reflect Contact ID. I need to find all sheets that contain the data "ContactID" and place (paste?) Contact ID in the same row, but in Column A.

Right now I am doing a Find all and clicking through the sheets 1 at a time (some of these finds though have 30+ sheets).

View 14 Replies View Related

Macro To Find And Replace Values Across Multiple Sheets In Same Workbook?

May 27, 2014

I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?

View 5 Replies View Related

Find First Blank Cell In First Column Of Known Range / Select And Delete From That Point

Jun 6, 2014

I have multiple tables like the one in the picture and have to duplicate this code for different known ranges.

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved