Searching through each sheet in a workbook

[gregtgonzalez]

Hello all!
I have posted here before and am SO thankful there are so many willing people to help novice XL-ers......
So I have attached a sample workbook, I have a few macros going on this one right now, however, I have come across another dilemma that I cant seem to cross yet. As of right now in each sheet with each name, has Column "AA" which indicates the current status of the line item based on the values of other cells in that Row. If the conditions are met it populates with a simple "YES", what I have to do is manually go in and go to each sheet, filter that column and take all the "yes" line items and transfer them to another sheet called "Send". I have to do the same thing with Column AB, with the "yes, COMPLETE" answers and pull those to a "completed" sheet


I have tried to record a macro but it doesn't seem to be effective for what I am trying to do. Any insight would be of great help!

thanks guys!

https://www.dropbox.com/s/zv1sod4e44pv92y/sample book.xlsm?dl=0

 

[vcoolio]

Hello Greg,

I don't see a "Completed" sheet and you have a "Sends" sheet not a "Send" sheet. Is it to be "Sends" or "Send"?
Also, do you need one code that will transfer data to the "Sends" (Send?) sheet and to the "Completed" sheet?

Cheerio,
vcoolio.

 

[gregtgonzalez]

Hi Vcoolio,
Yes that was supposed to be "Send" not "Sends". Yes I do need both, but i was hoping for help with one and i can take the information and from one code (moving items to the "send" sheet) and try to alter that code to accommodate pulling items to the "completed" sheet (this way it allows me to look at the vba code break it down and try to understand what is being done). OF course i am more than willing to relieve help on both, i apolgoize for the confusion.

 

[vcoolio]

Hello Greg,

I haven't been able to spend much time on this for you but try the following code assigned to a button in your "SEND" sheet:-

Sub TransferDetails()

    Dim lr As Long
    Dim ws As Worksheet
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheet22.UsedRange.Offset(1).ClearContents

For Each ws In Worksheets
    If ws.Name <> "NEW TO DISTRIBUTE" And ws.Name <> "SENDS" And ws.Name <> "RUNNING LOG" And ws.Name <> "Assignment" Then
      ws.Range("AA1", ws.Range("AA" & ws.Rows.Count).End(xlUp)).AutoFilter 1, "Yes"
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
          If lr > 1 Then
            ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp)).EntireRow.Copy
              Sheet22.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                Sheet22.Columns.AutoFit
                  ws.[AA1].AutoFilter
             End If
       End If
Next ws

Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

I've used the sheet code (Sheet22) instead of the sheet name ("SEND" or "SENDS") so it won't matter what you name the sheet.

This should give you a very good idea of what to do with the "Completed" sheet when you get around to practicing with it. Just remember to exclude this new sheet with the other exclusions ( And ws.Name <> "Completed").

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Hello Greg,


Following is another method that you may be interested in:-

Sub TransferDetails2()

    Dim sh As Variant
    Dim lr As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Sheet22.UsedRange.Offset(1).ClearContents

For Each sh In Array("GEOVANNI", "SERGIO", "GREG", "KATHY", "SUE", "RAYNE", "STEVEN", "RUBY")
    Sheets(sh).Range("AA1", Sheets(sh).Range("AA" & Sheets(sh).Rows.Count).End(xlUp)).AutoFilter 1, "Yes", 7
       lr = Sheets(sh).Range("AA" & Rows.Count).End(xlUp).Row
          If lr > 1 Then
             Sheets(sh).Range("AA2", Sheets(sh).Range("AA" & Sheets(sh).Rows.Count).End(xlUp)).EntireRow.Copy
                Sheet22.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                    Sheet22.Columns.AutoFit
                       Sheets(sh).[AA1].AutoFilter
                 End If
          Next sh

Application.Calculation = xlCalculationAutomatic
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

This code includes, in an array, all the sheets that you wish to extract data from whereas the first code above excludes the sheets that you do not wish to extract data from.

Just another option for you.

Cheerio,
vcoolio.

 

[gregtgonzalez]

Awesome Thank you Vcoolio! that code seemed to work wonders! Although I did notice once it has run through it leaves all of the sheets with the names in the "sorted" status from the auto filter on column AA. is there a way to make sure that is turned off once the sheet has been looked at? I was under the impression the [AA1].autofilter would have turned it off?

 

[vcoolio]

Hello Greg,

I was under the impression the [AA1].autofilter would have turned it off?

Oops! The line:-

Sheets(sh).[AA1].AutoFilter or ws.[AA1].AutoFilter in the first code needs to be placed directly after the End If line.

My apologies for the oversight. Let me know if that has fixed it for you.

Cheerio,
vcoolio.

 

[gregtgonzalez]

please no apologies! I am so grateful for your help! that does seem to have fixed it, but i have noticed for some reason it is not pulling the items from the "sergio" tab?

also, How does one go about writing a macro to auto populate a formula in a cell once it has been activated? I was able to find and utilize one for the "date stamp" that is on the individual names sheets, but there are a couple of other places i'd like to have a macro populate the cell with the formula rather than have the formula sitting there. i.e. on the new to distribute sheet the sampleassignment relies on a formula to find the correct assignment, this formula has to be dragged down to from the top. I have been trying to write a macro that would activate upon the value on column "I" being populated, and column "J" populate the correct assignment based on the vlookup equation that is there, i have been trying to loop the equation through until the value of the cell in column "I" is empty but to no avail. How would i go about doing that? i have been trying to research but have not been quite able to understand what i have found.

 

[gregtgonzalez]

also the workbook is here
https://www.dropbox.com/s/rffuwq8wav2yxow/sample book.xlsm?dl=0

 

[vcoolio]

Hello Greg,

I've just tested both codes in the work book that you supplied (with the autofilter line in the correct place!) and it works just fine. There is a total of six instances of the "Yes" criteria in Column AA of each sheet and all are transferred to the "Send" sheet. There is only one instance of the "Yes" criteria in Sergio's sheet and it is transferred over with the others without any problem. So, I don't understand why it wouldn't work for you. I assume that it is the same work book.

As for populating a formula in a number of cells in a column, you can experiment with the following to suit your own situation:-

Sub AddFormulae()

    Dim lr As Long
    Dim cell As Range
lr = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lr)
  If Not IsEmpty(cell.Value) Then
    Range("B2:B" & lr).Formula = "=If(A2="""","""",A2+1)" --------> Change the formula to suit your purposes.
      End If
Next

End Sub

The formula in the code above is a sample (test your own formula with it). The code, in this case, populates each cell in Column B as long as there is a value in Column A. Following is the link to a sample work book for you to peruse:-

https://www.dropbox.com/s/46pyzgnjk3ljtcg/Insert Formula.xlsm?dl=0

Now you have some homework to do!

I hope that this helps.

Cheerio,
vcoolio.