Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup information on separate sheets to delete
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup information on separate sheets to delete
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 " wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup information on separate sheets to delete
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
lookup information on separate sheets to delete
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 " wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching and combining information on two separate worksheets | Excel Worksheet Functions | |||
How do I combine information from two separate spreadsheets | Excel Worksheet Functions | |||
relative information in two separate worksheets? | Excel Worksheet Functions | |||
How do I separate address information from within an Excel cell? | Excel Discussion (Misc queries) | |||
Lookup from a range on separate sheets | Excel Discussion (Misc queries) |