error 400, again

[SPavlyuk]

good day,

I have a simple macro that picks up the file and then deletes some columns:

Sub test()
Dim sap_file As Variant
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With

sap_file = ActiveWorkbook.Name

Responce = MsgBox(sap_file, vbOKCancel)
If Responce = vbCancel Then
Exit Sub
End If

Workbooks(sap_file).Activate
Worksheets("Data").Select

Columns("H:Z").Select
Selection.Clear

Rows("1:1").Select
Selection.AutoFilter

End Sub

on Columns("H:Z").select I get error 400. Strange, if I run this portion of
macro in the "sap_file" itself everything works. Do you have any idea what is
not right?

Thanks
Serg

 

[Bob Phillips]

Worked fine for me, although it can be simplified

Sub test()
Dim sap_file As Variant
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With

sap_file = Activeworkbook.Name
Responce = MsgBox(sap_file, vbOKCancel)
If Responce = vbCancel Then
Exit Sub
End If

With Worksheets("Data")

.Columns("H:Z").Clear
.Rows("1:1").AutoFilter
End With
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[SPavlyuk]

better late then never, but thanks.

in my case I had to make the sub public, not private.