More 1004 Errors...

[Jim Berglund]

Your responses to my last concerns were great and solved the problem. I seem
to have created a new one that it doesn't address...

Private Sub CommandButton1_Click() 'Normalize
Dim i, j, numberofRows As Integer

Application.ScreenUpdating = False
Sheets("YTD BD").Activate
Range("A1").Select
numberofRows = ActiveCell.CurrentRegion.Rows.Count
numberofColumns = ActiveCell.CurrentRegion.Columns.Count
Range(Cells(2, 1), Cells(numberofRows, 5)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

*** The following line gave the 1004 error
ActiveSheet.Range(Cells(1, numberofColumns + 2)).Select

*** So I tried:

With ActiveSheet
.Range(.Cells(1, numberofColumns + 2)).Select
End With

Which also gave me the same 1004 error.

Any advice?

Thanks,

Jim Berglund

 

[Dave Peterson]

You can only select a range on a sheet that's selected/activated.

When you're code is in a general module, the unqualified ranges refer to the
activesheet. But when the code is behind a worksheet (I'm guessing that your
code is), then the unqualified ranges refer to the worksheet owning the code.

This seemed to work for me:

Option Explicit
Private Sub CommandButton1_Click() 'Normalize
Dim i As Long, j As Long, numberofRows As Long, numberOfColumns As Long

Application.ScreenUpdating = False
With Sheets("YTD BD")
.Activate
.Range("A1").Select
numberofRows = .Range("a1").CurrentRegion.Rows.Count
numberOfColumns = .Range("a1").CurrentRegion.Columns.Count
.Range(.Cells(2, 1), .Cells(numberofRows, 5)).Select
Selection.Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.Cells(1, numberOfColumns + 2).Select
End With
Application.ScreenUpdating = True
End Sub

But it's easier to do it without the activating and selecting:

Option Explicit
Private Sub CommandButton1_Click() 'Normalize
Dim i As Long, j As Long, numberofRows As Long, numberOfColumns As Long

Application.ScreenUpdating = False
With Sheets("YTD BD")
With .Range("a1").CurrentRegion
numberofRows = .Rows.Count
'numberOfColumns = .Columns.Count
End With
.Range(.Cells(2, 1), .Cells(numberofRows, 5)).Sort _
Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
Application.ScreenUpdating = True
End Sub