VBA code working on one file, giving error on another

Printable View

03-08-2023, 05:46 AM
wenifede

VBA code working on one file, giving error on another

Hi, I've two Excel with Macro files. I copied a couple of pages from one to another. I had two macros, one for exporting to Pdf which is working fine also in the new file, and another one that would hide/show a section of a document.<br>This second one keeps giving error, while in the older file it's still working fine.<br>I don't understand why it's giving error. Can somebody help?

Code:

Sub ShowHideHP2() ' RemovePbreak Macro Sheets("QUOTE").Select Rows("318:536").Select If Selection.EntireRow.Hidden = True Then Selection.EntireRow.Hidden = False 'Add Row Page Break Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakManual Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakManual Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakManual Else Selection.EntireRow.Hidden = True (ON THIS LINE IS GIVING ERROR) 'Clear Row Page Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakNone Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakNone Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakNone End If Sheets("Main calcs").Select End Sub
03-08-2023, 10:36 AM
June7

In future, please post code between CODE tags to retain structure and readability.

If you want to provide file for analysis, follow instructions at bottom of my post.
03-08-2023, 11:54 AM
Aussiebear

Does this work for you?

Code:

Sub ShowHideHP2() ' RemovePbreak Macro With Sheets("QUOTE”).Rows("318:536") If Selection.EntireRow.Hidden = True Then 'Clear Row Page Rows(318,405,471).PageBreak = xlPageBreakNone Elseif Selection.EntireRow.Hidden = False 'Add Row Page Break Rows(318,405,471).PageBreak = xlPageBreakManual End If Sheets("Main calcs").Activate End Sub
03-09-2023, 12:55 AM
wenifede

Hi Aussiebear,
thanks for this. It doesn't work. It gives me syntax Error in lines
With Sheets("QUOTE”).Rows("318:536")
and Elseif

However, I found what was wrong in my code... nothing... I locked the Excel page and that prevented the Macro to work property.
However, thanks for this, because you made me realise what the error was.

Thanks
03-09-2023, 01:06 AM
Aussiebear

Thank you for that. I am struggling with the logic of your argument however. If Rows (318:536) are visible insert page breaks but if Rows (318:536)are not visible remove page breaks..... my question is if the rows are not visible why do anything with them?
03-15-2023, 04:46 AM
Grade4.2

Greaty point Aussiebear. I wonder if he has any merged cells in range 318 to 536?
If so, the following might help:

Code:

Sub ShowHideHP2() ' RemovePbreak Macro Dim cell As Range Sheets("QUOTE").Select Rows("318:536").Select ' Unmerge any merged cells in the selected range For Each cell In Selection If cell.MergeCells Then cell.MergeArea.UnMerge End If Next cell If Selection.EntireRow.Hidden = True Then Selection.EntireRow.Hidden = False 'Add Row Page Break Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakManual Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakManual Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakManual Else Selection.EntireRow.Hidden = True 'Clear Row Page Worksheets("QUOTE").Rows(318).PageBreak = xlPageBreakNone Worksheets("QUOTE").Rows(405).PageBreak = xlPageBreakNone Worksheets("QUOTE").Rows(471).PageBreak = xlPageBreakNone End If Sheets("Main calcs").Select End Sub