• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Sort Macro Multiple Columns|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Sort Macro Multiple Columns|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosSort Macro Multiple Columns
sp_PrintTopic sp_TopicIcon
Sort Macro Multiple Columns
Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
1
May 5, 2018 - 2:28 am
sp_Permalink sp_Print

Hi

Can you help me with adjusting my macro?  My goal is to create a macro that can sort multiple columns together.  Meaning, if there a name in column 2 but should be in column 1 because  it is an alphabetical listing.  Would like for it to view three columns and place in the appropriate column.

 

Thank you

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
May 5, 2018 - 10:42 am
sp_Permalink sp_Print

There is no attachment

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
3
May 6, 2018 - 2:11 am
sp_Permalink sp_Print

Sorry.  Here is the file.

 

Thanks

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
4
May 6, 2018 - 2:33 am
sp_Permalink sp_Print

Hi Melinda

I don't quite understand your question. Is it sort by columns or rows?

If you can provide us the expected result from your attachment, it will help us understand better.

Sunny

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
5
May 6, 2018 - 8:10 pm
sp_Permalink sp_Print

After all the names are entered in columns B, D, and F.  I would like for it to look at all 3 columns and sort them all together.  A comprehensive sort where all the names are alphabetical order.  It would wrap to the next column.  For example, once the names have been entered in Column B row 70, then the next name in alpha order would be placed in Column D, row 6.  And  once the names have been entered in Column D, row 70, it would place the next alpha name in Column F, row 6.  

I hope this helps.

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
May 7, 2018 - 12:36 pm
sp_Permalink sp_Print

Hi Melinda

Give this a try.

I created a temp sheet with the names, sorted them and write back to the worksheet.

The temp sheet is then deleted.

Hope this helps.

Sunny

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
7
May 7, 2018 - 1:21 pm
sp_Permalink sp_Print

Sunny

It works perfectly as requested.  However, I did not factor in what happens when I enter the date.  Is there a way to adjust the macro?

Thanks

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
May 7, 2018 - 2:19 pm
sp_Permalink sp_Print

Hi Melinda

Hope you don't mind that I hard-coded the ranges.

Cheers

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
9
May 8, 2018 - 1:23 am
sp_Permalink sp_Print

Sunny

Thank you so very much!!  I appreciate all of your wealth of knowledge... 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
May 8, 2018 - 10:48 am
sp_Permalink sp_Print

Hi Melinda

Thanks for your feedback.

Sunny

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
11
June 26, 2018 - 4:03 am
sp_Permalink sp_Print

Sunny

Can you "walk me through" or explain something to me?  For example, how did you know what to put the ts.Range as?.  If I wanted to change the  ws.Range("B6:C50").Copy for "D6:E50" and "F6:G50" would the ts.Range stay the same as below? 

 

ws.Range("B6:C70").Copy ts.Range("A1")
    ws.Range("D6:E70").Copy ts.Range("A66")
    ws.Range("F6:G70").Copy ts.Range("A131") 

 

 

Sub SortColumns()
    Dim ws As Worksheet
    Dim ts As Worksheet
   
    Application.ScreenUpdating = False
    Set ws = Sheets("Sheet1")
   
    'Create temp sheet
    Sheets.Add.Name = "MyTemp"
    Set ts = Sheets("MyTemp")
   
    'Copy data to temp sheet
    ws.Range("B6:C70").Copy ts.Range("A1")
    ws.Range("D6:E70").Copy ts.Range("A66")
    ws.Range("F6:G70").Copy ts.Range("A131")
   
    'Sort data
    ActiveSheet.Range("A:B").Sort Key1:=Range("A1"), _
    Order1:=xlAscending, Header:=xlNo
   
    'Copy sorted data back to sheet
    Range("A1:B65").Copy ws.Range("B6")
    Range("A66:B130").Copy ws.Range("D6")
    Range("A131:B195").Copy ws.Range("F6")

    'Delete temp sheet
    Application.DisplayAlerts = False
    ts.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
June 26, 2018 - 11:11 am
sp_Permalink sp_Print

Hi Melinda

What I am doing is to copy your data (65 rows) below one another into a temporary worksheet, sort them and copy them back to the original worksheet.

So ts.Range("A1") will start at row 1 to row 65. The next row will start from ts.Range("A66") at the 66th row etc.

If you have 45 rows, then you will need to change all the row numbers to an increment of 45 (instead of 65)

When you copy back, you will need to adjust the range accordingly e.g. Range("A1:B45").Copy ws.Range("B6") etc

I had hard-coded the ranges as I have no idea what your actual data looks like.

I hope I have explain it clearly.

Sunny

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
13
June 26, 2018 - 11:51 am
sp_Permalink sp_Print

Sunny,  thank you for taking the time to explain.  I have a better understanding of the macro.  

Much appreciated!

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
14
January 16, 2019 - 3:03 am
sp_Permalink sp_Print

Sunny

The macro was working perfectly.  Today I am receiving error message "Run time error 1004" Method 'Add'...

Highlighted in yellow:   Sheets.Add.Name = "MyTemp"
                                 Set ts = Sheets("MyTemp")

Do you know how to fix it?

Sub SortColumns()
    Dim ws As Worksheet
    Dim ts As Worksheet
   
    Application.ScreenUpdating = False
    Set ws = Sheets("Sheet1")
   
    'Create temp sheet
    Sheets.Add.Name = "MyTemp"
    Set ts = Sheets("MyTemp")
   
    'Copy data to temp sheet
    ws.Range("B6:C70").Copy ts.Range("A1")
    ws.Range("D6:E70").Copy ts.Range("A66")
    ws.Range("F6:G70").Copy ts.Range("A131")
   
    'Sort data
    ActiveSheet.Range("A:B").Sort Key1:=Range("A1"), _
    Order1:=xlAscending, Header:=xlNo
   
    'Copy sorted data back to sheet
    Range("A1:B65").Copy ws.Range("B6")
    Range("A66:B130").Copy ws.Range("D6")
    Range("A131:B195").Copy ws.Range("F6")

    'Delete temp sheet
    Application.DisplayAlerts = False
    ts.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
15
January 16, 2019 - 3:46 am
sp_Permalink sp_Print

I can see 2 reasons:

-the sheet might already exist

-the workbook structure might have been protected, that means no sheet can be added or deleted. Check the workbook structure protection.

Avatar
mymalone
Member
Members
Level 0
Forum Posts: 104
Member Since:
August 25, 2017
sp_UserOfflineSmall Offline
16
January 17, 2019 - 12:36 am
sp_Permalink sp_Print

Catalin

Four of my worksheets have the following code, but each one has a different Set ws = Sheets (" ").  They are  "OR CST Scrub", "OR ASTs", "OR RNs"

 Application.ScreenUpdating = False
    Set ws = Sheets("7J Ante")
   
    'Create temp sheet
    Sheets.Add.Name = "MyTemp"
    Set ts = Sheets("MyTemp")
   

The sheets were already created, there are no new sheets being added. The workbook is protected, so does the macro add a temp sheet each time it is activated?  Is this the problem?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
17
January 19, 2019 - 10:38 am
sp_Permalink sp_Print

Hi Melinda

Based on the macro you provided, the MyTemp sheet will be deleted unless the macro encountered and error before that and fail to delete it.

In that case, you will need to manually delete the MyTemp sheet.

Catalin have already mentioned the likely cause of the error.

Can you post the file for us to have a look?

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Misael Gutierrez Sr., Bright Asamoah
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.