Macro help

[Dan Wilson]

Good day. I am running Excel 2018 on Windows 10 Pro. I have a Workbook containing 7 worksheets. One of the worksheets contains buttons that are attached to Macros. After creating all the necessary Macros to sort the worksheets by Date, Name, Percentage, etc., I realized that each of the sorts prints out a generic Header. I then created more Macros to do nothing more than change the header on a given worksheet according to the sort method. Those Macros work perfectly. I then attempted to include the header macros into the sort macros so that when a sort by Date is done, the Header will identify the printed page as “XXXX by Date” with XXXX being the worksheet name. I tried adding the Call statement prior to the “End Sub” but that didn’t work. I moved the Call statement to the first line of command text in the macro, but that didn’t work either. One of the worksheets contains a macro that contains only Call statements to execute a generic sort of the other worksheets. This macro contains only Call statements and it works fine. What am I doing wrong here? Any help will be appreciated.
Thank you, Dan Wilson…

 

[The_Macrotect]

Hi Dan - Can you post the code?

 

[Dan Wilson]

Good day Macrotect. Thank you for responding. I meant to include an example, but forgot. Below is the macro that works if I remove the Call statement. I get the same error if the Call statement is at the end just before the "End Sub". Below the macro is the macro being called by the Call statement. Thanks for any help.
Dan Wilson...

Sub Rides_by_Date()
'
' Rides_by_Date Macro
' sort Rides by Date
' 11/6/18

'
Call Set_Rides_by_Date
Sheets("Rides").Select
Range("A2:K26").Select
ActiveWorkbook.Worksheets("Rides").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Rides").Sort.SortFields.Add2 Key:=Range("A2:A26") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Rides").Sort.SortFields.Add2 Key:=Range("B2:B26") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rides").Sort
.SetRange Range("A1:K26")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-152
Range("A2").Select
End Sub


Sub Set_Rides_by_Date_Header()
'
' Set_Rides_by_Date_Header Macro
' set Rides by Date header
' 11-7-18
'

'
Sheets("Rides").Select
ActiveWindow.FreezePanes = False
ActiveWindow.View = xlPageLayoutView
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "Printed on &D"
.CenterHeader = "Demo Chapter 9999" & Chr(10) & "Rides 2018"
.RightHeader = "Page &P of &N" & Chr(10) & "Rides by Date"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveWindow.View = xlNormalView
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub

 

[The_Macrotect]

I think I recreated the problem. In the Sub Set_Rides_by_Date_Header code, I commented out the two lines shown below. Try commenting these out. Does this fix your issue?

Sub Set_Rides_by_Date_Header()
'
' Set_Rides_by_Date_Header Macro
' set Rides by Date header
' 11-7-18
'

'
Sheets("Rides").Select
ActiveWindow.FreezePanes = False
ActiveWindow.View = xlPageLayoutView
'Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = "Printed on &D"
.CenterHeader = "Demo Chapter 9999" & Chr(10) & "Rides 2018"
.RightHeader = "Page &P of &N" & Chr(10) & "Rides by Date"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
'Application.PrintCommunication = True
ActiveWindow.View = xlNormalView
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub

 

[Dan Wilson]

Good day again Macrotect. I feel like a real dummy. While I was trying your fix, I discovered that my Call statement was not complete. I was calling Rides_by_Date instead of Rides_by_Date_Header. I also found that having the Call statement at the start of the sub was causing the Freeze Panes to freeze in the middle of the page instead of the Top Row. Moving the Call to the end of the sub fixed that. Putting the comment on the two Application lines as you suggested did not have any effect on the operation of the sub. Thank you for your help. I always learn something using this forum.

 

[The_Macrotect]

I’m glad it was a simple fix, Dan. Take care!