Log In
Register
Home
August 25, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
There is no attachment
August 25, 2017
Offline
Sorry. Here is the file.
Thanks
VIP
Trusted Members
June 25, 2016
Offline
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
August 25, 2017
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
August 25, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
Hope you don't mind that I hard-coded the ranges.
Cheers
August 25, 2017
Offline
Sunny
Thank you so very much!! I appreciate all of your wealth of knowledge...
VIP
Trusted Members
June 25, 2016
Offline
Hi Melinda
Thanks for your feedback.
Sunny
August 25, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
August 25, 2017
Offline
Sunny, thank you for taking the time to explain. I have a better understanding of the macro.
Much appreciated!
August 25, 2017
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
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.
August 25, 2017
Offline
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?
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
