• 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.

Long Code that needs to be debugged and possibly shortened

Y

Yuriy Baron

New Member
Hi,

I have a code that excel has generated from the "record macros" function. It doesn't always execute correctly.
1. Please review the macro and correct any issues that you may find.
2. Is there a way that a macro can include a specific printer and printer settings?
3. is there a way that a macro can be executed 5 days a week (Mon - Fri) at 8:10 AM without opening excel?
4. Can this code be shortened so that it is easier to edit?

Code: 
Sub MacroPint3()
'
' MacroPint3 Macro
'

'
Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Assembly"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"(All)"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Brk SchedPrimeRq").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("SchedGroup").CurrentPage = _
"Brake"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Asy,Lsr,HW,WD,SW,PC SchedRqList").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Powder"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"SpotWeld"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Weld"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Hardware"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"LASER"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
Range("B1").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Punch"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"TRUPUNCH2020"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"TRUPUNCH5000"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"TRUPUNCH50S12"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Asy,Lsr,HW,WD,SW,PC SchedRqList").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"P2"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Grind"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = "(All)"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Asy,Lsr,HW,WD,SW,PC SchedRqList").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Machining"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Other"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"PNCHPRES/TON"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"ASI CELL"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"WLDROBOTIC/LG"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"Other"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"INSP/LASERQC"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc")
.PivotItems("ASSEMBLY").Visible = False
.PivotItems("BLTSANDER/DRY").Visible = False
.PivotItems("BRK/HD/LG").Visible = False
.PivotItems("DEBURR/HAND").Visible = False
.PivotItems("FORM/P2").Visible = False
.PivotItems("GRNDING/VIBRAT").Visible = False
.PivotItems("HRDWRE/HAGER").Visible = False
.PivotItems("HRDWRE/STAMP").Visible = False
.PivotItems("LASER/2030").Visible = False
.PivotItems("POWDERCOATING").Visible = False
.PivotItems("SEND OUTSIDE").Visible = False
.PivotItems("SPTWLD/SNGLE").Visible = False
.PivotItems("TRUPUNCH2020").Visible = False
.PivotItems("TRUPUNCH5000").Visible = False
.PivotItems("TRUPUNCH50S12").Visible = False
.PivotItems("WELDING/MIG.").Visible = False
.PivotItems("WELDING/TIG").Visible = False
.PivotItems("WLD/ALUM/MIG.").Visible = False
.PivotItems("WLDROBOTIC/LG").Visible = False
.PivotItems("MACHINING").Visible = False
.PivotItems("PNCHPRES/TON").Visible = False
.PivotItems("ASI CELL").Visible = False
.PivotItems("BRK/COLLABROBOT").Visible = False
.PivotItems("MATERIALS GROUP").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc"). _
EnableMultiplePageItems = True
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc")
.PivotItems("ASSEMBLY").Visible = True
.PivotItems("BLTSANDER/DRY").Visible = True
.PivotItems("BRK/HD/LG").Visible = True
.PivotItems("DEBURR/HAND").Visible = True
.PivotItems("FORM/P2").Visible = True
.PivotItems("GRNDING/VIBRAT").Visible = True
.PivotItems("HRDWRE/HAGER").Visible = True
.PivotItems("HRDWRE/STAMP").Visible = True
.PivotItems("LASER/2030").Visible = True
.PivotItems("POWDERCOATING").Visible = True
.PivotItems("SEND OUTSIDE").Visible = True
.PivotItems("SPTWLD/SNGLE").Visible = True
.PivotItems("TRUPUNCH2020").Visible = True
.PivotItems("TRUPUNCH5000").Visible = True
.PivotItems("TRUPUNCH50S12").Visible = True
.PivotItems("WELDING/MIG.").Visible = True
.PivotItems("WELDING/TIG").Visible = True
.PivotItems("WLD/ALUM/MIG.").Visible = True
.PivotItems("WLDROBOTIC/LG").Visible = True
.PivotItems("MACHINING").Visible = True
.PivotItems("PNCHPRES/TON").Visible = True
.PivotItems("ASI CELL").Visible = True
.PivotItems("BRK/COLLABROBOT").Visible = True
.PivotItems("MATERIALS GROUP").Visible = True
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc"). _
EnableMultiplePageItems = False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = _
"TRUPUNCH5000"
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = _
"(All)"
End Sub

Thank you in advance for taking a look at this mess.
 
Hi ,

Rather than posting the code , if you can upload your workbook with data , and explain what you want done , members can write the code for you.

Narayan
 
Unfortunately, the workbook takes all the information from SQL. So if I upload it, the workbook would be blank. That's why I have written out what I need in 4 steps.

Thanks,
Yuriy
 
Hi ,

You have mentioned 4 steps , but none of them deals with what the macro does.

If you can explain what you want done , by way of a macro , the macro can be written from scratch ; I doubt that there will be takers for debugging the macro you have posted.

Of course , you can wait and see if others respond.

Narayan
 
Yuriy Baron said:
Unfortunately, the workbook takes all the information from SQL. So if I upload it, the workbook would be blank. That's why I have written out what I need in 4 steps.

Thanks,
Yuriy
Click to expand...

In response to your 4 steps, although @NARAYANK991 has responded, let me add
1. Reviewing the macro without seeing the workbook is akin to asking one of your engineers to program a CNC machine without a drawing. You are not going to get worthwhile results. BTW, its guaranteed that it can be cut down as record macros produces bloated code.
2. Yes it can, but you'll need to provide details of your printer and it's connection
3. Macros can be programmed to run on any schedule you desire.
4. See 1.

BTW, at some point in time your workbook will contain data - that is the point at which you share it.

You must remember that this is essentially a volunteer fire department you have at your disposal; they like putting out real fires, but would prefer not to have to battle blazes that could have been avoided with a little forward thinking and a willingness to take on board their accumulated wisdom.
 
I would restructure the code around using loops for each group of similar actions

Refer below

Code: 
Sub MacroPint3()
'
' MacroPint3 Macro
'
Dim Item As Variant

Dim myArr(1 To 2) As String
myArr(1) = "Assembly"
myArr(2) = "(All)"
'or alternatively
'myArr = Array("Assembly", "(All)")

Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select

For Each Item In myArr
  ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").ClearAllFilters
  ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = Item
  ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next Item

Sheets("Brk SchedPrimeRq").Select

ReDim myArr(1 To 6) As String
myArr(1) = "Brake"
myArr(2) = "Powder"
myArr(3) = "SpotWeld"
myArr(4) = "Weld"
myArr(5) = "Hardware"
myArr(6) = "Laser"

ActiveSheet.PivotTables("PivotTable1").PivotFields("SchedGroup").ClearAllFilters

For Each Item In myArr
  ActiveSheet.PivotTables("PivotTable1").PivotFields("SchedGroup").CurrentPage = Item
  ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
  Sheets("Asy,Lsr,HW,WD,SW,PC SchedRqList").Select
Next Item


Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
Range("B1").Select

ReDim myArr(1 To 7) As String
myArr(1) = "TRUPUNCH2020"
myArr(2) = "TRUPUNCH5000"
myArr(3) = "TRUPUNCH50S12"
myArr(4) = "P2"
myArr(5) = "Grind"
myArr(6) = "(All)"
myArr(7) = "Machining"

ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = "Punch"

For Each Item In myArr
  ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
  ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = Item
  ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next Item


Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select

ReDim myArr(1 To 5) As String

myArr(1) = "Other"
myArr(2) = "PNCHPRES/TON"
myArr(3) = "TRUPUNCH50S12"
myArr(4) = "ASI CELL"
myArr(5) = "WLDROBOTIC/LG"
' or alternatively
' myArr = Array("Other", "PNCHPRES/TON", "TRUPUNCH50S12", "ASI CELL", "WLDROBOTIC/LG")

ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").ClearAllFilters

For Each Item In myArr
  ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
  ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = Item
  ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
Next Item



Sheets("Pnch, Asy, Oth, Mach SchedRsc").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").ClearAllFilters

ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = "Other"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = "INSP/LASERQC"
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc")
  .PivotItems("ASSEMBLY").Visible = False
  .PivotItems("BLTSANDER/DRY").Visible = False
  .PivotItems("BRK/HD/LG").Visible = False
  .PivotItems("DEBURR/HAND").Visible = False
  .PivotItems("FORM/P2").Visible = False
  .PivotItems("GRNDING/VIBRAT").Visible = False
  .PivotItems("HRDWRE/HAGER").Visible = False
  .PivotItems("HRDWRE/STAMP").Visible = False
  .PivotItems("LASER/2030").Visible = False
  .PivotItems("POWDERCOATING").Visible = False
  .PivotItems("SEND OUTSIDE").Visible = False
  .PivotItems("SPTWLD/SNGLE").Visible = False
  .PivotItems("TRUPUNCH2020").Visible = False
  .PivotItems("TRUPUNCH5000").Visible = False
  .PivotItems("TRUPUNCH50S12").Visible = False
  .PivotItems("WELDING/MIG.").Visible = False
  .PivotItems("WELDING/TIG").Visible = False
  .PivotItems("WLD/ALUM/MIG.").Visible = False
  .PivotItems("WLDROBOTIC/LG").Visible = False
  .PivotItems("MACHINING").Visible = False
  .PivotItems("PNCHPRES/TON").Visible = False
  .PivotItems("ASI CELL").Visible = False
  .PivotItems("BRK/COLLABROBOT").Visible = False
  .PivotItems("MATERIALS GROUP").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").EnableMultiplePageItems = True
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, IgnorePrintAreas:=False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").ClearAllFilters
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc")
  .PivotItems("ASSEMBLY").Visible = True
  .PivotItems("BLTSANDER/DRY").Visible = True
  .PivotItems("BRK/HD/LG").Visible = True
  .PivotItems("DEBURR/HAND").Visible = True
  .PivotItems("FORM/P2").Visible = True
  .PivotItems("GRNDING/VIBRAT").Visible = True
  .PivotItems("HRDWRE/HAGER").Visible = True
  .PivotItems("HRDWRE/STAMP").Visible = True
  .PivotItems("LASER/2030").Visible = True
  .PivotItems("POWDERCOATING").Visible = True
  .PivotItems("SEND OUTSIDE").Visible = True
  .PivotItems("SPTWLD/SNGLE").Visible = True
  .PivotItems("TRUPUNCH2020").Visible = True
  .PivotItems("TRUPUNCH5000").Visible = True
  .PivotItems("TRUPUNCH50S12").Visible = True
  .PivotItems("WELDING/MIG.").Visible = True
  .PivotItems("WELDING/TIG").Visible = True
  .PivotItems("WLD/ALUM/MIG.").Visible = True
  .PivotItems("WLDROBOTIC/LG").Visible = True
  .PivotItems("MACHINING").Visible = True
  .PivotItems("PNCHPRES/TON").Visible = True
  .PivotItems("ASI CELL").Visible = True
  .PivotItems("BRK/COLLABROBOT").Visible = True
  .PivotItems("MATERIALS GROUP").Visible = True
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").EnableMultiplePageItems = False
ActiveSheet.PivotTables("PivotTable2").PivotFields("Rsc").CurrentPage = "TRUPUNCH5000"
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("SchedGroup").CurrentPage = "(All)"

End Sub

As others have said it is a bit hard to comment much more without the file

It's also hard to comment on my changes as I haven't been able to test them, but they should give you the general idea

Also note I have removed the wrap around _
They make reading code so hard
 
Last edited:
You must log in or register to reply here.
Back
Top