change from Active Sheet to a specified sheet

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, I have a VBA which works great in sorting out a column from oldest to newest while expanding the columns and finally refreshing (Using Pivot Tables). I would like the VBA to be change from "With ActiveSheet" to state a another worksheet "Air Outbound". I need my Command Button on a different workseet. Thank you so much,

VBA Code:
Private Sub CommandButton2_Click()

   Dim xlSort As XlSortOrder
   Dim LastRow As Long

   With ActiveSheet

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If (.Range("A2").Value > .Range("A" & CStr(LastRow))) Then
    xlSort = xlAscending
End If

Range("A1") = "Date"
.Columns("A:N").Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes

     End With
      ActiveWorkbook.RefreshAll

  End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
How about
VBA Code:
Private Sub CommandButton2_Click()

   Dim xlSort As XlSortOrder
   Dim LastRow As Long

   With Sheets("Air Outbound")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If (.Range("A2").Value > .Range("A" & CStr(LastRow))) Then
    xlSort = xlAscending
End If

.Range("A1") = "Date"
.Columns("A:N").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes

     End With
      ActiveWorkbook.RefreshAll

  End Sub
 
Upvote 0
Solution
How about
VBA Code:
Private Sub CommandButton2_Click()

   Dim xlSort As XlSortOrder
   Dim LastRow As Long

   With Sheets("Air Outbound")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If (.Range("A2").Value > .Range("A" & CStr(LastRow))) Then
    xlSort = xlAscending
End If

.Range("A1") = "Date"
.Columns("A:N").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes

     End With
      ActiveWorkbook.RefreshAll

  End Sub
I'm afraid nothing happened, it is because they are not in macros and instead use directly on the sheets?
Capture.PNG
 
Upvote 0
That should work regardless of where the code is placed.
 
Upvote 0
That should work regardless of where the code is placed.
Thank you for getting back, for the life of me I can't figure why it's not working. I left the code on Sheet2 "Air Outbound" with your minor change and it worked good I tried using the code and command button in various different sheets and nothing seemed to work.
 
Upvote 0
Did you copy the code in post#2, or just make changes to your existing code?
 
Upvote 0
I got it to work, I just looked at my code for my command button and posted the vba in there. Thank you.
 
Upvote 0
How about
VBA Code:
Private Sub CommandButton2_Click()

   Dim xlSort As XlSortOrder
   Dim LastRow As Long

   With Sheets("Air Outbound")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

If (.Range("A2").Value > .Range("A" & CStr(LastRow))) Then
    xlSort = xlAscending
End If

.Range("A1") = "Date"
.Columns("A:N").Sort key1:=.Range("A2"), order1:=xlAscending, Header:=xlYes

     End With
      ActiveWorkbook.RefreshAll

  End Sub
I got it to work, just had to find my codes for my command buttons and insert the vba where necessary.
.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
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