VBA to sort Columns from named ranges stopped working

C

cbarryb

New Member
Joined
Jun 1, 2012
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. Web
Hi All, today my macro stopped working for some crazy reason. I press the button and I get a error that says "Error, Ledger column not found, it worked about an hour ago, but now has stopped. There is a column with the title Ledger but this is not recognised now! can someone please help, its the last 15 or so lines that is causing the error:

VBA Code: 
Sub PrintLayout()
'
' PrintLayout Macro
'
        Dim col As String, cfind As Range
        Dim credcol As String, credfind As Range

    On Error GoTo ErrorHandler
'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .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

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .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
   
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
   
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 0
        .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
        Columns("A:A").Select
        Selection.EntireColumn.AutoFit
        Columns("C:H").Select
        Selection.EntireColumn.AutoFit
       
       
        ActiveSheet.Select
       
      
        col = "*Ledger*"
        credcol = "*Credit*"
       
        Set cfind = Cells.Find(what:=col, lookat:=xlWhole)
        Set credfind = Cells.Find(what:=credcol, lookat:=xlWhole)
       
        ActiveSheet.Cells.Sort Key1:=cfind, order1:=xlAscending, key2:=credfind, order2:=xlDescending, Header:=xlYes
  
    Range("A1").Select
    Selection.CurrentRegion.Select
    Exit Sub
  
ErrorHandler:
    MsgBox "Error, Ledger Column Not Found!"
    Exit Sub[/COLOR]
  

End Sub

A Bit of a mess I know sorry. On each of my WorkSheets, I have a title row and there is a column titled 'Ledger Clerk', I can't figure this out for love nor money! any help would be much appreciated thank you.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Your code will produce that message for any error at all, so it doesn't necessarily mean what it says. Having said that, have you done any other Find/Replace operations in your current Excel session, either manually or in code? Any settings you make while doing that can affect your code.
 
Upvote 0
RoryA said:
Your code will produce that message for any error at all, so it doesn't necessarily mean what it says. Having said that, have you done any other Find/Replace operations in your current Excel session, either manually or in code? Any settings you make while doing that can affect your code.
Click to expand...
Ah Ok. No I was just going through my daily sheets, finished one, code worked fine, and onto the next, pressed the button and I then got that error.
 
Upvote 0
Change the last part to this:

VBA Code: 
        col = "*Ledger*"
        credcol = "*Credit*"
       
        Set cfind = Cells.Find(what:=col, lookat:=xlWhole)
        If cfind Is Nothing Then
         MsgBox "Could not find Ledger column"
         Exit Sub
      End If
        Set credfind = Cells.Find(what:=credcol, lookat:=xlWhole)
        If credfind Is Nothing Then
         MsgBox "Could not find credit column"
         Exit Sub
      End If
       
        ActiveSheet.Cells.Sort Key1:=cfind, order1:=xlAscending, key2:=credfind, order2:=xlDescending, Header:=xlYes
  
    Range("A1").Select
    Selection.CurrentRegion.Select
    Exit Sub
  
ErrorHandler:
    MsgBox "Error: " & Err.Description

then run it again and see what message comes up.
 
Upvote 0
Solution
RoryA said:
Change the last part to this:

VBA Code: 
        col = "*Ledger*"
        credcol = "*Credit*"
      
        Set cfind = Cells.Find(what:=col, lookat:=xlWhole)
        If cfind Is Nothing Then
         MsgBox "Could not find Ledger column"
         Exit Sub
      End If
        Set credfind = Cells.Find(what:=credcol, lookat:=xlWhole)
        If credfind Is Nothing Then
         MsgBox "Could not find credit column"
         Exit Sub
      End If
      
        ActiveSheet.Cells.Sort Key1:=cfind, order1:=xlAscending, key2:=credfind, order2:=xlDescending, Header:=xlYes
 
    Range("A1").Select
    Selection.CurrentRegion.Select
    Exit Sub
 
ErrorHandler:
    MsgBox "Error: " & Err.Description

then run it again and see what message comes up.
Click to expand...
I will add this / change my code, its much cleaner and professional. I disabled the error handler, and ran the code, it was the line for "Application.PrintCommunication = True" there was no printer setupfor some reason! So this has been rectified, and Thank you so much for looking at this, I will add your solution to my code, its better than what I have, thank you again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
PrintArea = "TableName" but visible cells only
Replies
2
Views
383
willow1985
willow1985
J
  • Question
Excel VBA error 400 while link to another sheet
Replies
3
Views
391
rollis13
rollis13
L
  • Question
Macro recorded print comments anyway
Replies
1
Views
440
sijpie
sijpie
P
  • Question
Macro to automatically print
Replies
1
Views
1K
pure vito
P
T
  • Solved
PDF Export - Fit to page
Replies
9
Views
13K
jolivanes
jolivanes

Forum statistics

Threads
1,214,795
Messages
6,121,624
Members
449,041
Latest member
Postman24

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top