lookup information on separate sheets to delete

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bramnizzle@gmail.com - 23 Nov 2007 19:35 GMT

Okay, here's a new one for everyone...

I want a macro (that I can assign to a button) that will allow the
user to select a cell and will delete that same cell from certain
other sheets.

For example...
on the sheet "Master Bill Summary" there is a range of cells with
headings
B C D
9 Title Frequency Amount
10 Car ea month $410
11 Phone ea month $65

There are hidden sheets with the names of months (January, February,
etc.)
These hidden month sheets have this same information but start at row
12 on each of those sheets respectively.

For example...
"November"

(column A&B are merged)

AB C D
12 Car ea month $410
13 Phone ea month $65

There is information in cells E through I but that doesn't matter for
what I want.

I have a "Delete Bill" button on the "Master Bill Summary" sheet.
What I want is that when the user clicks the "Delete Bill" button, a
box or something will open that will categorize column A (on the
Master Bill Summary sheet) showing (for this example) 'Car' and
'Phone'...the user clicks a box next to one of the bills...say
'Car'...then the macro will find the row with 'Car' in column A (on
"Master Bill Summary") and delete the entire row and then go to every
sheet with a month name, find 'Car' in column A and delete that entire
row.

Too complicated?

Reply to this Message

Joel - 24 Nov 2007 11:00 GMT

Sub deleterow()

Title = Range("A" & ActiveCell.Row)
Response = MsgBox("Do you want to delete " & Title & " ?", _
vbYesNo, "Delete Row")
If Response = vbYes Then

For Each sht In ThisWorkbook.Sheets

Select Case sht.Name
Case _
"January", _
"February", _
"March", _
"April", _
"May", _
"June", _
"July", _
"August", _
"September", _
"October", _
"November", _
"December"

With sht
Set c = .Rows.Find(what:=Title, _
LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Delete
End If
End With

End Select
Next sht
End If

End Sub

> Okay, here's a new one for everyone...
>
[quoted text clipped - 38 lines]
>
> Too complicated?

Reply to this Message

bramnizzle@gmail.com - 25 Nov 2007 18:27 GMT

Okay, this works great...however, if the month sheet is protected, it
won't work. Can I throw a
ActiveWorkbook.ActiveSheet.Unprotect
statement in the With statement and then protect it again before the
With statement ends?

Reply to this Message

Joel - 26 Nov 2007 13:06 GMT

Sub deleterow()

Title = Range("A" & ActiveCell.Row)
Response = MsgBox("Do you want to delete " & Title & " ?", _
vbYesNo, "Delete Row")
If Response = vbYes Then

For Each sht In ThisWorkbook.Sheets

Select Case sht.Name
Case _
"January", _
"February", _
"March", _
"April", _
"May", _
"June", _
"July", _
"August", _
"September", _
"October", _
"November", _
"December"

With sht
Set c = .Rows.Find(what:=Title, _
LookIn:=xlValues)
If Not c Is Nothing Then
sht.Unprotect
c.EntireRow.Delete
sht.Protect
End If
End With

End Select
Next sht
End If

End Sub

> Okay, this works great...however, if the month sheet is protected, it
> won't work. Can I throw a
> ActiveWorkbook.ActiveSheet.Unprotect
> statement in the With statement and then protect it again before the
> With statement ends?

Reply to this Message