Problem with "Range"

[Guest]

Using Excel 2002 SP3
This code works:
Sub Zloaddata()
phyfile = ThisWorkbook.Name
phypath = ThisWorkbook.Path & "\"
Workbooks(phyfile).Sheets("PHY").Activate
Workbooks(phyfile).Sheets("PHY").Range(Columns(1), Columns(250)).Delete

This code results in "Run-time error 1004 Application-defined or
Object-defined error"
Sub CommandButton3_Click()
phyfile = ThisWorkbook.Name
phypath = ThisWorkbook.Path & "\"
Workbooks(phyfile).Worksheets("PHY").Activate
ActiveWorkbook.Sheets("PHY").Range(Columns(1), Columns(250)).Delete

Why does the 2nd example result in the run-time error ?

 

[Tom Ogilvy]

Because when you move the code into a sheet module, the unqualified

columns(1) and Columns(250) refer to the sheet containing the code rather
than the activesheet. You can fix it with this

Sub CommandButton3_Click()
phyfile = ThisWorkbook.Name
phypath = ThisWorkbook.Path & "\"
With Workbooks(phyfile).Worksheets("PHY")
.Range(.Columns(1), .Columns(250)).Delete
End With
End Sub

or

Sub CommandButton3_Click()
phyfile = ThisWorkbook.Name
phypath = ThisWorkbook.Path & "\"
With Workbooks(phyfile).Worksheets("PHY")
.Columns(1).Resize(,250).Delete
' or .columns("A:IP").Delete
End With
End Sub