Is it possible with VBA to sort a range in a non-active workbook, without activating it? If so I would appreciate some guidance on how to do it. My Excel is 2019.
Thanks for all help.
Is it possible with VBA to sort a range in a non-active workbook, without activating it? If so I would appreciate some guidance on how to do it. My Excel is 2019.
Thanks for all help.
Example :Please Login or Register to view this content.
The code is in Book1.xlsm
There is another open workbook which is Book2.xlsx
In Book2.xlsx sheet Sheet1 there is a data with 4 column header from A1 to D1
There are 99 rows of data under each column header, starts from row 2.
So, the range to be sorted is from cell A1 to D100.
Based on the sample code above,
the code will sort the data in Book2.xlsx Sheet1 column 2 without activating Book2.xlsx
Last edited by karmapala; 09-13-2021 at 02:02 AM.
Thank you very much for your response, Karmapala. Code worked perfectly as you wrote it, without activating Book2! I need to add a second key, so the 4th line reads
rg.Sort Key1:=rg.Columns(4), Order1:=xlAscending, header:=xlYes, Key2:=rg.Columns(1), Order1:=xlDescending, header:=xlYes
Column 4 is properly sorted, but column 1 does not sort descending. What am I doing wrong? BTW, I have Excel 2019.
I really appreciate your help with this Karmapala. Prior to reading your post I spent countless hours trying to figure it our myself, without success.
Maybe like this:
Please Login or Register to view this content.
In your code, I think you forgot to change "Order1" to "Order2".rg.Sort Key1:=rg.Columns(4), Order1:=xlAscending, header:=xlYes, Key2:=rg.Columns(1), Order1:=xlDescending, header:=xlYes
Last edited by karmapala; 09-14-2021 at 08:34 PM.
Of course, Karmapala, many thanks. Now if my range is changed from "A1:D11" to "M11:W50", everything else the same how would the code read?
Again, I greatly appreciate your expertise and patience, you are saving me a lot of trial and error time!
If the header is from cell M11 to cell W11if my range is changed from "A1:D11" to "M11:W50", everything else the same how would the code read?
and the rows of data under the headers are from row 12 to row 50,
I think you can just change this line :
intoPlease Login or Register to view this content.
Thank you.Please Login or Register to view this content.
Made the suggested modifications and also changed the columns. My code now reads
Sub Test2()
Set wb2bSorted = Workbooks("Book2.xlsx")
With wb2bSorted
Set rg = .Sheets("Sheet1").Range("M11:W50")
rg.Sort Key1:=rg.Columns(18), Order1:=xlAscending, header:=xlYes, _
Key2:=rg.Columns(13), Order2:=xlDescending, header:=xlYes
End With
End Sub
I get the message:
Run time error '1004'
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.
Any ideas. Thanks again Karmapala.
Programmernovice,
The column should refer to the rg column number, not the sheet column number.Please Login or Register to view this content.
So I think it should be like this :
Please Login or Register to view this content.
Works perfectly, Karmapala, many thanks!
Can you please suggest one or two books (beginner - intermediate) I might get to learn fundamentals of VBA? I have Excel 2019,32-bit, don't know what version of VBA
it contains.
I appreciate your expertise and your willingness to help others like me.
Programmernovice,
I am sorry as I don't know any books to be recommended for you.
FYI, I myself is not an expert.
Most of the time the codes on my answer to someone's question, I get it:
1. by recording a macro one at a time then combine those recorded subs to make a whole one sub
2. by learning from an answered codes to someone else's vba question.
3. by trial and error
I'm quite sure that you and everybody can do the same like me.
For example, as to your question in this post, now you know :
- how to do things in another opened workbook without activating it
- how is the code to sort one level and two levels.
You are certainly more "expert" than me. I am impressed by your willingness to help, Karmapala.
I'm new to the forum, is there some way to acknowledge your help by posting positive feedback on the site? Please let me know if possible.
Programmernovice,
If you want, you can click the "Add Reputation" button
and mark this question as SOLVED from the "Thread Tools" which located right under the question's title.
Thank you
I have done both Karmapala. Thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks