Creating a sorting and printing macro using VBA

bmt216a

New Member
Joined
Dec 15, 2022
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Hello everyone,

I want to create a sorting and printing macro that is able to dynamically adjust the range of of values that is sorted and printed. I have a really simple worksheet with 4 columns A-D and varying rows at which the data ends. I have attached a screenshot. The data needs to be sorted by Column C, Cell values, Smallest to largest. The important thing here is to sort Column B along with it. Below is my code. I need help to adjust the range so that it picks up whatever the last row is and then sorts and prints the entire worksheet. Can anybody help? Thank you
Screenshot (150).png
?

VBA Code:
Sub Macro22()
'
' Macro22 Macro
'

'
    Range("B5:C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort.SortFields.Add2 Key:= _
        Range("C5:C94" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("10-6-18_Coax_Data").Sort
        .SetRange Range("B4:C94" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F4").Select
End Sub
Sub Macro23()
'
' Macro23 Macro
'

'
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=51
    Range("A1:D62").Select
    ActiveWindow.SmallScroll Down:=27
    Range("A1:D94").Select
    ActiveWindow.SmallScroll Down:=-114
    Selection.PrintOut Copies:=1, Collate:=True
End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Perhaps something like this.
VBA Code:
Sub SortCol()
    Dim WS As Worksheet
    Dim rng As Range
    
    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    
    With WS
        Set rng = .Range("A4:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    rng.Sort Key1:=rng.Columns(3), Order1:=xlAscending, Header:=xlYes
End Sub

Sub PrintSheet()
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    WS.PrintOut
End Sub
 
Upvote 0
Solution
Perhaps something like this.
VBA Code:
Sub SortCol()
    Dim WS As Worksheet
    Dim rng As Range
   
    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
   
    With WS
        Set rng = .Range("A4:D" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With

    rng.Sort Key1:=rng.Columns(3), Order1:=xlAscending, Header:=xlYes
End Sub

Sub PrintSheet()
    Dim WS As Worksheet

    Set WS = ThisWorkbook.Worksheets("10-6-18_Coax_Data")
    WS.PrintOut
End Sub
Hello, thank you for your response. After messing around with it a bit I have found the solution for the correct sorting. However, the print function has become a separate macro. I would like there to be one macro for both sort and print and one button I can press to automate both processes for me. Is there a way to do that? I will attach the edited code and the macro window.
VBA Code:
Sub SortCol()

    Dim WS As Worksheet

    Dim rng As Range

  

    Set WS = ThisWorkbook.Worksheets("Data")

  

    With WS

        Set rng = .Range("B4:C4" & .Range("C" & .Rows.Count).End(xlUp).Row)

    End With



    rng.Sort Key1:=rng.Columns(2), Order1:=xlAscending, Header:=xlYes

End Sub

Sub PrintSheet()

    Dim WS As Worksheet



    Set WS = ThisWorkbook.Worksheets("Data")

    WS.PrintOut

End Sub
 

Attachments

  • Screenshot (153).png
    Screenshot (153).png
    18.6 KB · Views: 5
Upvote 0
Hello, I actually figured it out. I just used the call function. Thank you for your help.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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