excel VBA to sort a different sheet than the active one

Question

I was wondering if there is a way to have my macro do the below:

I want it to run this code on sheet2:

Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer

'Place the column headers in the end result order you want.
arrColOrder = Array("*Item1*", "*Item2*", "*Item3*", "*Item4*")

counter = 1

Application.ScreenUpdating = False

For ndx = LBound(arrColOrder) To UBound(arrColOrder)

    Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

    If Not Found Is Nothing Then
        If Found.Column <> counter Then
            Found.EntireColumn.Cut
            Columns(counter).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
        counter = counter + 1
    End If
Next ndx

Application.ScreenUpdating = True

End Sub

Then run this code on sheet1:

'This will add three columns for the Item2, Item3, and the Item4 datapoints based on Item1 in column A.

    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Item4"
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Item3"
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Item2"
    Range("P1:R1").Select
    Range("R1").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[10],Sheet1!C[-15]:C[-14],2,FALSE)"
    Range("P2").Select
Selection.AutoFill Destination:=Range("P2:P" & Cells(Rows.Count, "Z").End(xlUp).Row)
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[9],Sheet1!C[-16]:C[-14],3,FALSE)"
    Range("Q2").Select
Selection.AutoFill Destination:=Range("Q2:Q" & Cells(Rows.Count, "Z").End(xlUp).Row)
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[8],Sheet1!C[-17]:C[-14],4,FALSE)"
    Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R" & Cells(Rows.Count, "Z").End(xlUp).Row)

End Sub

Is there a way? So far all it does it run both on the active sheet and that makes everything mess up.

All I want is to re-order the things on sheet 2 so when I do the V lookup on sheet 1 they are in the right order!

Please help, thanks!

score 1 · Accepted Answer · edited May 23 '17 at 12:17

So far all it does it run both on the active sheet and that makes everything mess up.

Aha, you've found one of the pitfalls in using .Activate and .Select. Here's a good SO thread that outlines how to avoid using .Select.

Further to your question, how do you run two codes on two different sheets? It's not too hard - just create two Worksheet variables, and use With statements.

For example, here's a code that places "A" in Sheet1, cell A1, and "A" in Sheet2, cell A2:

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet

'Let's define our variables.  For worksheets, like Ranges, you need to use 'Set'
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

 'Now, we want to work with each specific worksheet. Let's choose one at a time.
With ws1
    .Cells(1, 1).Value = "A"
    ' Do other code here, to happen on ws1.  Note the use of the '.' before '.Cells', this tells excel to use
    ' ws1.cells(1,1) ...
End With

With ws2
    .Cells(2, 1).Value = "A"
    'Do other stuff here for worksheet2
End With

End Sub

A 'quick and dirty' fix for your code is, right before the line that you want to use on Sheet2, enter the line Sheets("Sheet2").Activate.

Edit: Just more notes on using the ws1 and ws2:

With ws1
 .cells(1,1).Value = "A"
End with

is the same thing as ws1.cells(1,1).Value = "A". You use With because you can put all your code in there, that is to be run with Worksheet1. In your code, if you create a ws1 and ws2 variable, and set these to the right sheets, you can just put ws1. before all the ranges you want to select in that worksheet, and ws2. for the second worksheet. Does that make sense?

If you want to select range "A1:B10" in worksheet 1, then delete the range, you could do

With ws1
 .Range("A1:B10").Delete
End with
' is same as ws1.range("A1:B10").Delete

or

With ws1
 .Range(.Cells(1,1),.Cells(10,2)).Delete
End with
' is same as ws1.Range(ws1.Cells(1,1),ws1.CElls(10,2)).Delete

Note the . before Cells. This is due to the fact that you want this range to refer to sheet1's range, not any other sheet. Leaving out the . could potentially cause issues if another sheet becomes active during the macro.

edit2:

After all the above, I've edited your OP code to avoid using .Select. You should be able to study this and think, and see what I did:

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

''' RUN THE BELOW ON SHEET 2
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer

'Place the column headers in the end result order you want.
arrColOrder = Array("*Item1*", "*Item2*", "*Item3*", "*Item4*")

counter = 1

Application.ScreenUpdating = False

For ndx = LBound(arrColOrder) To UBound(arrColOrder)

    Set Found = ws2.Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                      SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

    If Not Found Is Nothing Then
        If Found.Column <> counter Then
            Found.EntireColumn.Cut
            ws2.Columns(counter).Insert Shift:=xlToRight
            Application.CutCopyMode = False
        End If
        counter = counter + 1
    End If
Next ndx

Application.ScreenUpdating = True

''' RUN THE BELOW ON SHEET1
With ws1
    .Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("P1").FormulaR1C1 = "Item4"
    .Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("P1").FormulaR1C1 = "Item3"
    .Columns("P:P").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("P1").FormulaR1C1 = "Item2"
   ' .Range("P1:R1").Select 'Don't need this, since you don't do anything with it.
    With .Range("R1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    .Range("P2").FormulaR1C1 = "=VLOOKUP(RC[10],Sheet1!C[-15]:C[-14],2,FALSE)"
    .Range("P2").AutoFill Destination:=Range("P2:P" & Cells(Rows.Count, "Z").End(xlUp).Row)
    .Range("Q2").FormulaR1C1 = "=VLOOKUP(RC[9],Sheet1!C[-16]:C[-14],3,FALSE)"
    .Range("Q2").AutoFill Destination:=Range("Q2:Q" & Cells(Rows.Count, "Z").End(xlUp).Row)
    .Range("R2").FormulaR1C1 = "=VLOOKUP(RC[8],Sheet1!C[-17]:C[-14],4,FALSE)"
    .Range("R2").AutoFill Destination:=Range("R2:R" & Cells(Rows.Count, "Z").End(xlUp).Row)
End Sub

I just tried to impliment the fix above with ws1 and ws2 and it still sorted the data on the active sheet only.. — CrypTrick, Aug 17 '15 at 18:37
I also tried the "dirty fix" however after making sheet 2 the active sheet how do i switch back to sheet 1? Thanks — CrypTrick, Aug 17 '15 at 18:40
OH! sorry again, on the "quick fix" changing the active sheet, that works, but I cant get it to use the wild card EX: Sheets("name*").Activate -- cause sometimes it will be name_1 name_2 ect. Thoughts? — CrypTrick, Aug 17 '15 at 18:43
I've updated my post - check it out and hopefully it works! Let me know if not, and I'll look into your question. Edit: Can you clarify? Your sheets won't always be "Sheet1", "Sheet2"? If no, what's the naming convention for them? — BruceWayne, Aug 17 '15 at 18:46
well it pulls based on how many times you have exported something so if only once it is numbers_all then the 2nd time you export its numbers_all-1 and the third time is numbers_all-2 ect. so i wanted to use the wild card * behind the numbers_all* to catch any variable — CrypTrick, Aug 17 '15 at 19:07
And that's to get the Sheet names? How do you know, if there are up to ten sheets ( `numbers_all-10`), which one is going to be your "worksheet 1" and "2" for the macro? — BruceWayne, Aug 17 '15 at 19:09
I will always run this macro on numbers_all but if i have exported the list 10 times then the name would be numbers_all-10. It will be the same data, and sheet 2 will always be named the same, just the first worksheet will change names — CrypTrick, Aug 17 '15 at 19:16
Do you know enough VB to be able to change/edit the worksheet names in that macro, or would you like me to help guide? — BruceWayne, Aug 17 '15 at 19:22
no I can write that, I just didnt know if i could use a wildcard to prevent more code. Thanks for everything! the code above works great! thank you very much for taking the time to help!! — CrypTrick, Aug 17 '15 at 19:26
Glad to have helped! (If you don't mind, marking as answer?) And I think you're okay in using the wildcard, just make sure it's a good use of one, and not like `"she*"` which would get `Sheet1`, `Sheet2`, etc. etc. (I guess for this example use `"sheet1-*"` or something. — BruceWayne, Aug 17 '15 at 19:31

score 0 · Answer 2 · answered Aug 17 '15 at 18:25

0

Create an excel worksheet object. You can sort that.

Dim ws As Excel.Worksheet
Set ws = Worksheets("Sheet2")

'Then you do a sort on ws.Range("A:A").Sort

answered Aug 17 '15 at 18:25

MatthewD

6,719
5
22
41

excel VBA to sort a different sheet than the active one

2 Answers2