delete all matching rows

R

Rich

have a workbook which contains 14 pages,
the first page contains employee info, and the other 13 pages read info from
the first page
the first page also reads info from the other 13 pages

if a row is deleted on page one is their a way to delete all the matching
rows on the other pages

thanks in advance
 
D

Don Guillett

Select all sheets or just the ones you want. Then use the right arrow key
delete function on any of the sheets.
 
A

Alan Beban

Don said:
Select all sheets or just the ones you want. Then use the right arrow key
delete function on any of the sheets.
Click to expand...
This only works if the matching records are on the same row as the
record to be deleted.

Alan Beban
 
R

Rich

thanks

yes all the matching data is on the same row, , what i am looking for is a
way to delete the matching records, without the need to select all sheets,
because using that option if someone starts to reenter data when all the
sheets are selected it will also enter the data on all the other sheets.

i guess it will have to be some kink of makro, ? but rather than have a
macro on each line i guess i would need one that asks who to delete then
deletes him off eash page along with his matching data

thanks anyway
 
R

Rich

at the min i am using the following:-

Sub line9()
Resp = MsgBox(Prompt:="This Action Will Reset All Pre Entered Data For This
Employee Continue?", Buttons:=vbYesNo)
If Resp = vbNo Then Exit Sub
Resp = MsgBox(Prompt:="Are You Sure You Want To Continue - This Action Can
Not Be Undone", Buttons:=vbYesNo)
If Resp = vbNo Then Exit Sub
Application.ScreenUpdating = False
Sheets(Array("Jan", "Feb", "March", "April", "May", "June", "July", "Aug",
"Sep", "Oct", _
"Nov", "Dec")).Select
Range("E9:AI9").Select
Selection.ClearContents
Sheets("Overview").Select
Range("B9:E9").Select
Selection.ClearContents
Range("B9").Select
Application.ScreenUpdating = True
End Sub

using this macro i would have to write it approx 130 times down the main
page next to each persons name. i was hoping to be able to do a "one hit"
button which when i click on it, it will ask me which row to delete rather
than having 130 macros saying the same thing
 
D

Don Guillett

try this. Correct word wrap and add the other months. NO selections
necessary or desired so no screen updating.

Sub DeleteRequestedRowsInAllSheets()
Dim sh As Worksheet
x = InputBox("Put in Row NUMBER to Delete")

Resp = MsgBox(Prompt:="This Action Will Reset All Pre Entered Data For This
Employee Continue?", Buttons:=vbYesNo)
If Resp = vbNo Then Exit Sub
Resp = MsgBox(Prompt:="Are You Sure You Want To Continue - This Action Can
Not Be Undone", Buttons:=vbYesNo)
If Resp = vbNo Then Exit Sub

For Each sh In Worksheets(Array("Jan", "Feb", "Overview"))
sh.Rows(x).Delete
Next sh

End Sub
 
D

Don Guillett

You could also do for ALL sheets except by

for each sh in worksheets
if sh.name<>"dontdeletethisone" then sh.rows(x).delete
next sh
 
R

Rich

thanks don, thats works better than i expected

Don Guillett said:
You could also do for ALL sheets except by

for each sh in worksheets
if sh.name<>"dontdeletethisone" then sh.rows(x).delete
next sh
Click to expand...
 
R

Rich

would the same princibles be able to enter new names in a specific row ? it
would also need to be able to copy the formulas from the row above ????
 
R

Rich

using the macro code you gave me i altered it so that it could insert a line
as well as delete one. that works fine,
however on each page jan - dec their are formulas in colums b threw d , when
i reun the macro to insert a new line i also need to to imput the formulas,

have tried telling it to copy and paste the formulas from the row below the
new row, but cant seem to get it to work with the X variable
 
D

David

Don,
You seem to have a real command of this subject and I need some help. I'm
creating a consolidated workbook of data that is imported from multiple sites
each week. Sheet1 is graphs, so nothing to do with this. Sheet2 is the Master
List of locations, column A is location number, column B is location name.
Starting with Column 3 are ending dates for each week (Sundays). There are 15
pages, each with one datapoint from the location reports (which has 15 totals
that I'm posting from like Daily Sales Total, on one sheet, Calls Run, on
another sheet, etc.). The macro copies datapoint1 from the location's report,
goes to the consolidated workbook, sheet2 and finds the cell that intersets
with the location number and the date from the location report and writes the
result. It does this 15 times, one datapoint per sheet with each datapoint,
moving to the next sheet.
My problem comes when I need to add a new location or need to sort the data.
I have started with giving range A1:B200 a range name (NameList) and then
using the formula =IF(Sheet1!A1="","",OFFSET(NameList,0,0)) on the following
sheets. But, as I started testing, this works great on the first two columns,
so that if I enter a row and put new data, the number and name are correct (I
have a macro that autofills if any changes are made on the first sheet), but
all the other columns are of course not affected.
What I'm really after is to have sheet2 be my master sheet and the other 14
sheets "act" like sheet2 for columns A & B and then all the data for each
week stay on the same row for the location. If I insert a row, I want all
sheets to have a row inserted. If I sort sheet2 (always by columns 1 and 2),
I want all sheets to be sorted the same way, along with all the corresponding
data.
I saw your option of highlighing all sheets, and inserting or deleting a
row. This would be perfect until I get to the sorting part. Would you have a
macro solution, or is there a better approach to what I'm trying to do.

Thanks much and hope you have a Great Christmas!
 
D

Don Guillett

This looks more like a "project" than an answer to a question. Contact me
privately with a workbook and details if you want custom work.
 
D

David

After looking at all my options, I'm just going to add the new entries at the
end and not worry about sorting. Thanks!
 
Top