VBA - Search For Value Across Multiple Worksheets

[Steve Belsch]

Yes I did google it. Didn't find a reason why. Maybe it is my excel version. Not sure. I will call my IT.

[Steve Belsch]

One more question. If there are blank cells in column A will this code stop and not continue to check for a match? The reason I ask is because I set up a macro that creates subtotals of every tab and then it creates 5 blank rows after every subtotal.

Thanks.
Steve

[paulked]

You'll have to try it, it may delete the rows that have a blank in column A.

[Steve Belsch]

I am trying to skip multiple pages before I start the code and it won't let me do it. Here is what I am trying to do. But it turns red and highlights the first And_ when I try to move on. Why is that? I have used the And_ in multiple Macros. No idea why it is not working?

If sh.Name <> "PO Accrual Data" And_
sh.name <> "Tab Name List" And_
sh.name <> "Inpute Date" Then


Then

[paulked]

Leave a space between the And and underscore: And _

[Steve Belsch]

I just tried that and it didn't work. Strange. I have attached a slimmed down version of my document. Let me know what I am doing wrong. I just need to skip the first 13 tabs

Thank you.
Steve

[paulked]

Which routine?

[Steve Belsch]

Paulked,

I have attached a file. I just need to skip the first 13 tabs before I run the code and it is not working. Even if I do the space And _ after the and. Any ideas why?

Thanks.
Steve

[paulked]

I was asking in which routine (Sub, Macro or whatever you call it) are you having trouble with I don't want to go through the whole lot checking!

[Steve Belsch]

The routine that you built for me. Titled Macro1. The part where it codes to skip the <>”PO Data”. I can’t get it to skip more tabs.

[paulked]

My routine is not in that workbook!

The only routine in the workbook you attached with reference to PO is the RunMacroAcrossAllTabs, and in that you have done the line split correctly!!!!

       If xSh.Name <> "Instructions" And _
       xSh.Name <> "Accrual & PO Data" And _
       xSh.Name <> "Tab Name List" And _
       xSh.Name <> "Subtotal Macro Button" And _
       xSh.Name <> "Input Date" And _
       xSh.Name <> "Summary FY19 F1" And _
       xSh.Name <> "EP Local" And _
       xSh.Name <> "Driver Definitions" And _
       xSh.Name <> "EP Global" Then
       xSh.Select 'Run Subtotal Loop on all tabs except these

[paulked]

Can you now see the importance of attaching a workbook when requested?

[Steve Belsch]

My apologies. I meant to attach your code. I was so busy removing formulas and tabs to reduce the size of the document to an acceptable size that I forgot to include the awesome code you created.

I am wondering what I did wrong. Because I tried to replicate that code. Maybe I didn’t do it in the correct placement of the code? It wouldn’t let me do it. Maybe it was not included in the if and then loop the correct way.

I apologize again. I am up against a deadline and I have adequate VBA skills, but nothing at your level. And to that I am great full.

so what do you think I am not doing to make this work? Thoughts?

thanks,
steve

[paulked]

I can see exactly what you are not doing... following instructions! How can I sort it if you don't post it?

[Steve Belsch]

Paulked,

I have attached the document with your code. If you could please take a look and let me know what i am doing wrong with the And _ for the 1st 13 tabs. I can't make it work for some reason. Your Macro that you built for me is Macro 1.

Thank you!
Steve

[paulked]

Are you winding me up on purpose? Where is the code you tried to add? All you've given me is exactly what I gave you... am I supposed to guess which 13 sheets you want to miss?

[Steve Belsch]

paulked,

I am sorry. No I am not trying to wind you up. I updated the Macro with what I was intending to get to work.

This is what is not working. It is in Macro 1 module. It looks correct to me. But it is still red and won't accept the code for some reason.

For Each sh In ThisWorkbook.Worksheets        'Don't include PO Accrual Data
        If
        
       Sh.Name <> "Instructions" And _
       Sh.Name <> "Accrual & PO Data" And _
       Sh.Name <> "Tab Name List" And _
       Sh.Name <> "Subtotal Macro Button" And _
       Sh.Name <> "Input Date" And _
       Sh.Name <> "Summary FY19 F1(5)" And _
       Sh.Name <> "Summary FY19 F1(4)" And _
       Sh.Name <> "Summary FY19 F1(3)" And _
       Sh.Name <> "Summary FY19 F1(2)" And _
       Sh.Name <> "Summary FY19 F1" And _
       Sh.Name <> "EP Local" And _
       Sh.Name <> "Driver Definitions" And _
       Sh.Name <> "EP Global" Then

[Paul_Hossler]

Keep the "If" on the same line as it's checking

        If sh.Name <> "Instructions" And _
       sh.Name <> "Accrual & PO Data" And _
       sh.Name <> "Tab Name List" And _
       sh.Name <> "Subtotal Macro Button" And _
       sh.Name <> "Input Date" And _
       sh.Name <> "Summary FY19 F1(5)" And _
       sh.Name <> "Summary FY19 F1(4)" And _
       sh.Name <> "Summary FY19 F1(3)" And _
       sh.Name <> "Summary FY19 F1(2)" And _
       sh.Name <> "Summary FY19 F1" And _
       sh.Name <> "EP Local" And _
       sh.Name <> "Driver Definitions" And _
       sh.Name <> "EP Global" Then

[paulked]

You can't have line spaces after If!!!

For Each sh In ThisWorkbook.Worksheets        'Don't include PO Accrual Data
        If Sh.Name <> "Instructions" And _
       Sh.Name <> "Accrual & PO Data" And _
       Sh.Name <> "Tab Name List" And _
       Sh.Name <> "Subtotal Macro Button" And _
       Sh.Name <> "Input Date" And _
       Sh.Name <> "Summary FY19 F1(5)" And _
       Sh.Name <> "Summary FY19 F1(4)" And _
       Sh.Name <> "Summary FY19 F1(3)" And _
       Sh.Name <> "Summary FY19 F1(2)" And _
       Sh.Name <> "Summary FY19 F1" And _
       Sh.Name <> "EP Local" And _
       Sh.Name <> "Driver Definitions" And _
       Sh.Name <> "EP Global" Then

It compiled but I haven't tested it because the sheet numbers have changed, that code refers to Sheet2 which doesn't exist in this workbook.

[Steve Belsch]

That worked! No more red. But now I am getting this error message, see attached screen shot.