Yes I did google it. Didn't find a reason why. Maybe it is my excel version. Not sure. I will call my IT.
Yes I did google it. Didn't find a reason why. Maybe it is my excel version. Not sure. I will call my IT.
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
You'll have to try it, it may delete the rows that have a blank in column A.
Semper in excretia sumus; solum profundum variat.
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
Leave a space between the And and underscore: And _
Semper in excretia sumus; solum profundum variat.
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
Which routine?
Semper in excretia sumus; solum profundum variat.
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
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!
Semper in excretia sumus; solum profundum variat.
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.
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
Semper in excretia sumus; solum profundum variat.
Can you now see the importance of attaching a workbook when requested?
Semper in excretia sumus; solum profundum variat.
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
I can see exactly what you are not doing... following instructions! How can I sort it if you don't post it?
Semper in excretia sumus; solum profundum variat.
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
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?
Semper in excretia sumus; solum profundum variat.
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
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
You can't have line spaces after If!!!
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.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
Semper in excretia sumus; solum profundum variat.
That worked! No more red. But now I am getting this error message, see attached screen shot.