Problem with "Range"
 

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 ?

Problem with "Range"
 

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
--
Regards,
Tom Ogilvy


"Baruche" wrote in message
...
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 ?