Hi all,
First time poster but I have been reading the posts for the past few weeks as I refresh myself on macros as I've been out of the game for over 20 years (and I was a beginner then).
I'm currently using O365.
Ok so my challenge is pretty big, I get a workbook sent to me weekly with over 70 tabs of somewhat unstructured data and I get the fun of:
- Copying all of the relevant information from each worksheet into a master sheet Underway
- Add the header of each worksheet to the rows copied Complete
- Remove empty rows after macro runs Complete
- Remove rows with irrelevant information after macro runs Complete
- Add 4 columns to the start of the master sheet ready for "Text to Columns" Complete
Since the worksheets can vary on a weekly basis I have set my copy range from each worksheet to a set row count which is why it will clean up the empty rows at the end.
One thing I noticed after building the macro which was over 250 lines, was that column C "Compliance" on one worksheet might be located in column E and called "compliant" in another worksheet. (painful)
My copy method is below for reference
Sheets("BU - State - Site - Function").Range("b2:b2").Copy Destination:=Sheets("Sheet1").Range("a2476:a2975")
Sheets("BU - State - Site - Function").Range("a10:d500").Copy Destination:=Sheets("Sheet1").Range("b2477") What I am needing help with, can anyone think of a way that if there is a way to copy the entire column if a cell within a range of (C10:E10) contains "*compli* (non case sensitive and I've added the wild card to allow for spelling variances).
Also since the macro is pending on the worksheet names, is there a way to have the range be all worksheets? (ie start on the Second tab (first being the master), perform all actions then start next tab etc)
Any help would be appreciated.
~Nick
