I'm new to all of this and I'm stumbling through as best as I can so forgive me if I don't explain this very well.
I have a workbook that has over 180 worksheets in it.
I store ongoing data on each worksheet in cells a6:i6 through a2506:i2506.
At the start of the new year I only need to keep "Active" data.
I want to be able to compare cell b6 through b2506 individually and make sure it doesn't match ANY cell in the range k8:k508 collectively and then clear contents of range a:i for each row b6:b2506 that doesn't match.
I can't use a simple compare b to k because column k is a consolidated (removing duplicates) list of b and therefore I need to keep every row in b that matches ANY of the rows in k.
I can't delete rows either, I have to use clear contents because there is information in the other columns that populate off information entered in columns a through i.
If anyone has a quick way to do this using VBA then I can run it on each sheet individually or consolidate to run on each sheet at once by just calling the new worksheet and then the sub to clear contents.
Thank you in advance for any help or thoughts to point me in the right direction.
Related:
Excel vba clear contents based on cell value
Clear cell based on another cell value - Best answers
Excel clear cell contents if another cell changes - Best answers
You explain it better then a lot of others :). There is no need to run the code on each sheet manually, the code will loop through all sheets. I added a line where you can add sheets you might want to exclude from the cleansing.
Here is the code:
Sub RunMe()
Dim ws As Worksheet
Dim mFind As Range
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
If ws.Name <> "Not this sheet" And ws.Name <> "Not this sheet either" Then 'Add the sheet names that need to be excluded here
For Each cell In Range("B6:B2506")
Set mFind = Range("K8:K508").Find(cell.Value)
If mFind Is Nothing Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "I")).ClearContents
End If
Next cell
End If
Next ws
Application.ScreenUpdating = True
End Sub
I put the code in it's own module and added the sheets (I had to add the ( ) before and after the " " for it to recognize the sheet names - remember I'm still learning) that I didn't want disturbed because at first it wiped out information on these sheets. I'm using a back-up copy of the file so it's ok.
It seems that on the sheets that I want to look at it's not actually doing anything though. Before I added the ( ) to the sheet names I wanted to skip it was wiping out the contents on those sheets, but still wasn't doing anything on the sheets that I wanted it to look at.
The sheets that I want to skip don't have anything in column K if you think that is making the difference.
Is there anything else that I might be missing that you thought I might know instinctively? I told you I'm pretty new to all this so I don't know much....lol
Sorry to hear you are having trouble with my solution.
The only change you might have to make, was to change the sheetnames on code line 9. No need for brackets, otherwise I would have put them there. It is a working code.
Let me post my testbook, so you can see the test environment I used to come up with the code:
http://www.ge.tt/6r1dnGA3
Maybe you see something that I misunderstood and you can let me know.
Otherwise post the download link to your own workbook (always be carefull with sensitive data), so I can have a look as to what you do different then what I did to create the code.
I got ahead of myself, sorry I did mention I'm learning.
The "k" column reference needed to be modified for what I wanted to happen, but works the way you said it would.
Thank You!
I did run into an issue though when comparing to column k it sees 50 as part of 150 and 33 or 34 as part of 1334 because it is looking for any match at all I'm guessing. Is there a way to only match exactly cell by cell still?
I posted the code I'm currently using so you can see I had to modify it a little. I also added a msgbox to tell the user it is done since it does take awhile to perform this.
I really do appreciate all your help, it has turned what took me an hour or so into less than a minute or two. So thank you for your help so far.
The code I'm using:
Sub clear_sheets_new_year_active_only_dies()
Dim ws As Worksheet
Dim mFind As Range
Application.ScreenUpdating = False
For Each ws In Worksheets
ws.Select
If ws.Name <> ("Last Time Die Ran") And ws.Name <> ("Active Dies") And ws.Name <> ("Check Out Sheet") And ws.Name <> ("Summary Report") And ws.Name <> ("Graphs") Then 'Add the sheet names that need to be excluded here
For Each cell In Range("B6:B2506")
Set mFind = Range("K8:K38").Find(cell.Value)
If mFind Is Nothing Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "I")).ClearContents
End If
Next cell
End If
Next ws
Application.ScreenUpdating = True
Update_complete_msgbox
End Sub
Sub Update_complete_msgbox()
VBA.MsgBox "Update Completed!", , "Update Sheets New Year"
End Sub
PERFECT!!!!!! Thank you so much. This really helped me a lot.