• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run-time Error '1004': unable to set the ShowDetail property of Range Class

Burnie

New Member
Hi, I'm new to the group and have hit a wall writing a macro. I am trying to create a Bill of Material Macro for work, breaking the BOM down by size and separating everything into different sheet tabs. It all seems to work fine until it tries to filter and sort my second pivot table. I attached two files, the test file is pre-macros and the template file is where I created the macros, as well as the macro text. Not sure if I do it correctly, I always create two files. One to show how I want it to be while creating the macro, and the second to do a test run before creating the button for me and the guys I work with. Any help would be greatly appreciated. Please let me know if any further information is needed.
 

Attachments

  • Hanger BOM - Template-test.xlsm
    32.1 KB · Views: 3
  • Hanger BOM - Template.xlsm
    261.4 KB · Views: 3
  • Hanger BOM Macro.txt
    26.4 KB · Views: 3
I have tried to google the error code multiple times, but it sounds like it's not always the same cause or the same resolution. Any help would be appreciated. Please let me know if there is any more information needed. I tried to embed the code here but it says there are too many characters, so I uploaded it as a text file.
 
Burnie
Why You need to make those 10-11 sheets?
How many sheets can You see in one time? One ... 12?
If Your answers would be 12 then ... interesting ... monitor.

Hanger BOM Macro.txt
... which seems to have a macro
... which I cannot run for some reasons ... even after 'clean it'.
>> If I cannot run it ... It's a challenge for me.

>> >> Ideas?
 
I have to make those sheets in order to separate everything out by size. I have the overall data sheet, then I have a sheet that gives the hanger count for purchase. All of the rest of the sheets are to separate those hangers by rod size, so the products can be prefabricated at the lengths shown on the pivot tables that are causing the error. At times there will be hundreds of hangers on a large project, so it would get crazy on a single sheet. I'm not great at excel though, sure the way I am doing things, isn't the best. My template excel sheet should have the macro in it, it's the file a created it in. The test sheet is so I can check it to run. Thank you for looking into this with me.
 
Not sure I did it correctly, but here is the .bas file exported out of Excel for the macro.
 

Attachments

  • Module7.bas
    26.4 KB · Views: 1
In the attached is a version of your macro. When you run it, it will fail at the line:
Selection.ShowDetail = False
For the moment, just comment-out the line with an apostrophe and continue with F5. The reason I let it fail there is to ask you what that line is supposed to be doing?

There's a Stop line later on with its comment in the code.

But do you need the sheets 3-8", 1-2", 5-8", 3-4", 7-8"? They're all just copies of the same thing with a different autofilter applied?
1. All the pivot tables could all be based on the Data sheet instead of these sheets. The fact that these sheets are filtered doesn't make any difference to their respective pivot tables; the pivot table uses the data as if it were unfiltered anyway. It would save resources by having only one pivot cache.

2. Are these 5 sheets needed for some other purpose? If they were needed, instead of having them filtered, would it be better to have them contain only the data pertinent to their own Totals sheet (and not have a filter in them at all)?

I've temporarily removed a lot of formatting code from the macro, it made it easier to see what you were aiming at. I can put it back again later.
 

Attachments

  • Copy of Hanger BOM - Template-test v06.xlsm
    45.8 KB · Views: 2
Burnie
You didn't answer to my questions ... how many...?
My opinion is that You could see one sheet in one time.
That means,
You could filter needed information to that sheet and
it won't take so much time.
... and it would be possible to do this even without VBA - not tested.

... as p45cal also seems to offer lighter version.
 
Yes sir, sadly, all of the sheets are needed. The end users don't know how to use filters, or excel, and many refuse to learn anything technology based. So I have to give it to them as simplified as possible. It means more headache and work for me, but in the end, it is my job to make it to where they don't have to think. I appreciate you taking your time to help.

As far as the line that is causing the error, I'm not sure why it is there. When I googled the error code, a few people had errors with that same line in the current version of excel, but the fix was different in each one.

As far as the sheets not being filtered, the filtered pivot tables were the only way I could get it to show the final information the way I needed it to look. I couldn't think of any other way to show it.
 
Maybe if I explain the steps I have to take and the end delivery it would help?

We have plumbing software that we model our piping and hangers in in 3D. After we model and coordinate our hangers for hanging the plumbing and duct work, we export a data file for the project, into Excel.

That data file has all of the different lengths, sizes and diameters for the hangers we exported. The data doesn't give total counts of hanger sizes or anything, it's just raw with all the information.

So I have to break it out to give a total count of all of the hangers by pipe size and by strut lengths. That total is just so the end user knows how many of those specific sizes to order from the vendor that supplies our hanger types.

The extra tabs, with the 3/8" and up, are the all thread sizes. These all threads have to be separated out by size so the end user can cut them to the lengths they need. I used the pivot table there to merge the similar lengths of all thread so instead of them having to go through and look at each length individually, they would just know they have 5 rods to cut 1'-3 1/2" long, or whatever.

Then since they don't know how to use excel, I have to print off everything as a PDF to send to them.

The way I did it, trying to use a macro to do everything, just seemed the quickest. Since we will be doing this a lot for each project we have and for each floor separately. I figured I would try to spend the hours up front to where later on it will just be minutes, but my weakness in Excel shows. There may be a better way to show all of the information, I just don't know enough about excel to do it.
 
As I look at it more, I think the "Selection.ShowDetail = False" line is being created when I run the Macro.
 
Burnie
Yes sir, sadly, all of the sheets are needed...
Can those press the button, which has eg text [ 3-8" ]?
or any other needed as for 'sheet'?
.. and those no need to know Excel at all!
(of course, sometimes that would help or make more difficult )

Same time,
if there would be more than 'one sheet' then those would look to wrong sheet!

If 'one sheet'-system,
then they have to focus to press correct needed button and smile.


Also ... one windy day, You have to modify Your 'basic layout'
... then (with Your way) You have to do same by code
... (modify code first ... 'few' times testing ... testing )
... or with 'one sheet'-system ... You'll need to modify
... ONE sheet as You need!
 
Not all of the end users will have computers, the fabricators work strictly off of a paper copy. So they never see the Excel. Earlier you mentioned just coding out the error line and it will work, how do I do that? As I said, still a lot I don't know about excel.
 
Burnie
You could printout that sheet to paper too
or make PDF
or those could use that file.

If never seen Excel then ...
1) if those could click that file open
2) if those could press needed button
3) if those could read 'output' ... hmm?

Earlier you mentioned ...
I checked again ...
I wrote:
... which I cannot run for some reasons ... even after 'clean it'.
>> If I cannot run it ... It's a challenge for me.


... and that code has ... a lot of extras, which anyway, have to solve manually before get code :(
... and to check others code ... would be challenge! = could take time.

>>> >>> >>>

Everybody knows something about Excel ... somebody maybe more ;)
Few things are sure
a) You need to know needed 'input' - fixed layout
b) You need to know needed 'output' - fixed layout
... between those would need some coding
= how to get from 'input' needed 'output' as user friendly as possible.
 
I apologize if I seem stubborn or like I'm not listening while you two are trying to do your best to help, it is not my intent. I am self taught when it comes to excel and there is a lot I am still learning, and even more that I don't know. I was given a task by my boss to make this work and in a format where a "child" could look at it without having to input in any information or click anything, (short of switching tabs) and have the information they need. They don't want our fabricators clicking anything because they are afraid if they do, then it may cause user errors. So they want it to come from my group already complete, so all the fabricators do is take the PDF and mark off their counts and totals.
 
P45
In the attached is a version of your macro. When you run it, it will fail at the line:
Selection.ShowDetail = False
For the moment, just comment-out the line with an apostrophe and continue with F5. The reason I let it fail there is to ask you what that line is supposed to be doing?

There's a Stop line later on with its comment in the code.

But do you need the sheets 3-8", 1-2", 5-8", 3-4", 7-8"? They're all just copies of the same thing with a different autofilter applied?
1. All the pivot tables could all be based on the Data sheet instead of these sheets. The fact that these sheets are filtered doesn't make any difference to their respective pivot tables; the pivot table uses the data as if it were unfiltered anyway. It would save resources by having only one pivot cache.

2. Are these 5 sheets needed for some other purpose? If they were needed, instead of having them filtered, would it be better to have them contain only the data pertinent to their own Totals sheet (and not have a filter in them at all)?

I've temporarily removed a lot of formatting code from the macro, it made it easier to see what you were aiming at. I can put it back again later.

Do you know how I can get rid of that error, or stop it from failing? As far as I know that line doesn't serve a purpose.
 
As I look at it more, I think the "Selection.ShowDetail = False" line is being created when I run the Macro.
That line will, depending on what's selected, do the equivalent of clicking the little button(s) with a little negative (minus) sign in part(s) of the pivot table.
Your code up to that point produces this:
upload_2018-10-30_19-1-32.png

Now I'm guessing; are you wanting this?:
upload_2018-10-30_19-3-19.png

If so, in my code replace:
Code:
Selection.ShowDetail = False  'this will fail; what are you trying to do here?
with:
Code:
PT.PivotFields("Rod Length").ShowDetail = False
This line should be deleted:
Code:
PT.PivotSelect "'Tag #'[All]", xlLabelOnly + xlFirstRow, True 'probably unnecessary.
 
That line will, depending on what's selected, do the equivalent of clicking the little button(s) with a little negative (minus) sign in part(s) of the pivot table.
Your code up to that point produces this:
View attachment 56177

Now I'm guessing; are you wanting this?:
View attachment 56178

If so, in my code replace:
Code:
Selection.ShowDetail = False  'this will fail; what are you trying to do here?
with:
Code:
PT.PivotFields("Rod Length").ShowDetail = False
This line should be deleted:
Code:
PT.PivotSelect "'Tag #'[All]", xlLabelOnly + xlFirstRow, True 'probably unnecessary.
That is exactly what I'm looking for. So can I delete the stop line as well, so that it goes straight through without hitting F5? You are a miracle worker.
 
I went ahead and deleted the line to test, as well as a few other lines you said were not needed, and it worked beautifully. It is a little slow, but worked with the output that I was required to give to our fabricators. Thank you so much for the help, I owe you a beer. I'm going to test it on all of our other hanger sheets to make sure it works on them as well. I do see one change we will have to manually make. We will have to change the initial sheet name in order for the macro to work consistently, since each file name is different between jobs, and makes the initial data name different as well. Again, thank you.
 
Burnie
Okay ...
If You're still interesting ... check this too.

There are 'buttons' to press ...
 

Attachments

  • Hanger BOM - tst.xlsb
    56.8 KB · Views: 2
I do see one change we will have to manually make. We will have to change the initial sheet name in order for the macro to work consistently, since each file name is different between jobs, and makes the initial data name different as well. Again, thank you.
If there is only one sheet in the workbook before you run the macro then we can make it that sheet - we don't even have to change its name. If there are several worksheeets before the macro is run, we could make the assumption that the active sheet is the data sheet, again, no name change required.
I'm working a little on streamlining the code to have only one pivot cache and to have the sheets which aren't '~Totals' just plain sheets with no filters. This should make the whole process less resource-hungry.
The slowness, I believe, is down to the last part where you prepare for printing - I might look at that too, later.
 
In the attached is a bit of a re-write of your code.
Look at the comments in the code too.
There are many lines of yours that I've commented-out, especially those which add borders to the selected cells (often this selection is the whole sheet!). Instead I've added a line:
.PrintGridlines = True 'new
which is (surprise, surprise) an option to print the sheet's gridlines which I suspect will be enough.
There is also an added line:
'Application.ScreenUpdating = False
which is currently commented-out. Remove the apostrophe at the extreme left of that line and it will speed things up tremendously. BUT, if, while you're debugging the code, the code should stop/error/fail before it executes that line's brother:
Application.ScreenUpdating = True
you might have difficulty navigating around the workbook/worksheet; it might be very (or totally) unresponsive! If that happens, you need to execute that statement manually in the Immediate pane of the VBE by typing:
Application.ScreenUpdating = True
and pressing Enter on the keyboard.
Later you can move that ScreenUpdating=True line all the way to the bottom of the macro to just before End Sub.

You may have difficulty fitting some data onto a single page's width - we can fix that.
Later on in the code, you select many or all of the sheets. I suspect you expect the subsequent code to affect all the selected sheets, but this is not always the case; you may have to loop through them and process them one at a time.
 

Attachments

  • Copy of Hanger BOM - Template-test v08.xlsm
    55.3 KB · Views: 1
Last edited:
Burnie
Eight buttons to 'play'
Makes PDFs, Printouts ...
maybe Your boss wouldn't figure this?
 

Attachments

  • Hanger BOM - tst.xlsb
    63.5 KB · Views: 3
Back
Top