Need a macro between documents for business use!

[Kanger14]

Hello everyone,

I am looking for assistance in building a more efficient macro for my business use between two documents.

So for simplicity sake, I will call the two documents Data and Work.

Document Data is where we pull our raw data from, hence the name of the file will change daily.
Document Work is what we use to put that raw data in, and make accessible for our staff to work off of.


For Document Data:

The first thing that we need to do is select all columns and autofit column width.
Then we need to delete, in this order, columns A to G, columns B to C, columns D to V, column F, and then columns G to AD. For each deletion, we naturally need the non cleared columns to slide as far left as they can.

This is what I have for this section, though unsure if its the most efficient/effective:

Columns("A:G").Select
Selection.Delete Shift:=xlToLeft
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Columns("D:V").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:AD").Select
Selection.Delete Shift:=xlToLeft

Once this is done, we need to set up filters on columns A B and C.
Column A needs to be filtered from newest date to oldest.
Columns B and C need to be filtered to only show only "TRUE".


So up to this point I have been able to create to some degree... but this next part is where I am clueless.


Now what we need to do is go to Document Work, and look at the email address in row 2. What we need to happen now is to take that email address, find it in Document Data, and delete all rows beneath it.
Then we need to take all the remaining rows in Document Data (everything above the found email address, and insert them above row 2 in Document Work.



The last thing I will mention is that Document Work is a shared sheet, whereas Document Data is not and can have a macro added. We have the ability to completely restart Document Work in order to add a macro, and then share it again.

If anyone has a macro they think could fit this need, I am all ears!

Thank you!

 

[CSmith]

See if this helps ?

Once this is done, we need to set up filters on columns A B and C.
Column A needs to be filtered from newest date to oldest.
Columns B and C need to be filtered to only show only "TRUE".

  ActiveSheet.AutoFilterMode = False 'Clear filter
  ActiveSheet.UsedRange.Sort _
    key1:=Range("A:A"), order1:=xlAscending
  ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:=True
  ActiveSheet.UsedRange.AutoFilter Field:=3, Criteria1:=True

 

[CSmith]

Opps wrong thread