Excel vba sorting macro

Question

I am designing a macro that will copy and sort data by a key and then insert 2 columns before both the original sheet in vba. I have begun to write the code and it has worked well so far but when I try to Insert the 2 columns it gives me a Select method of Range class failed error at the line:Sheet2.Columns("A:A").Select I am confused why this is happening. Any help would be greatly appreciated.

Sub crossUpdate()

Dim rng1 As Range, rng2 As Range, rng1Row As Range, rng2Row As Range, Key As Range, match As Integer


'Unhide and Unfilter columns and rows on original sheet
 Sheet1.Cells.EntireColumn.Hidden = False
 Sheet1.Cells.EntireRow.Hidden = False
 Sheet1.Cells.AutoFilter

'Copy and paste original sheet to new temp sheet
 Sheet1.Cells.Select
 Selection.Copy
 Sheets.Add.Name = "SourceData"
 ActiveSheet.Paste
 Range("A1").Select

'Sort temp sheet by key
N = Cells(Rows.Count, "A").End(xlUp).row
Set rng1 = Sheets("SourceData").Cells.Range("A2:A" & N)
Set rng1Row = rng1.EntireRow
rng1Row.Sort Key1:=Sheets("SourceData").Range("A1")



'Unhide and Unfilter columns and rows on original sheet
Sheet2.Cells.EntireColumn.Hidden = False
Sheet2.Cells.EntireRow.Hidden = False
Sheet2.Cells.AutoFilter

'Update sheet sorted by key
N = Cells(Rows.Count, "A").End(xlUp).row
Set rng2 = Sheets("Sheet2").Cells.Range("A2:A" & N)
Set rng2Row = rng2.EntireRow
rng2Row.Sort Key1:=Sheets("Sheet2").Range("A1")


Sheet2.Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("SourceData").Columns("A:A").Select
Selection.Insert Shift:=xlToRight

End Sub

user1274820 · Accepted Answer · 2014-09-26 16:25:18Z

2

I think it's because you are using a Select on a different sheet than the active one.

One solution is to do:

Sheet2.Select
Sheet2.Columns("A:A").Select

Another solution is to not use select at all (which is almost always better)

Sheet2.Columns("A:A").Insert Shift:=xlToRight

edited Sep 26, 2014 at 16:25

answered Sep 26, 2014 at 16:17

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel vba sorting macro

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related