MACRO not working on two different files  
Author Message
leochen





PostPosted: Mon Jul 18 17:27:08 CDT 2005 Top

Excel Programming >> MACRO not working on two different files

I have the code below on my PERSONAL workbook and works fine. When I try to
copy it to another workbook, it doesn't get past the first RANGE line. The
error I get is "Select Method of Range class Failed."

Why would this happen?

Thanks.


Company = InputBox("For which Company is this TB for?", "Company Code",
"112, 115, 127, 176, 177")
ChDir "C:\Documents and Settings\marco.rodas\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\marco.rodas\Desktop\Trial Balance
TEMP.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:=Array(Array(0, _
1), Array(1, 1), Array(13, 1), Array(34, 1), Array(75, 1), Array(94,
1), Array(113, 1))
Range("B11:G20192").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=" & Company & "*",
Operator:=xlAnd
Selection.Copy

Excel262  
 
 
jamest





PostPosted: Mon Jul 18 17:27:08 CDT 2005 Top

Excel Programming >> MACRO not working on two different files Try adding activesheet just ahead of the range statements...

Activesheet.Range("B11:G20192").Select
Activesheet.Range(Selection, Selection.End(xlDown)).Select

--
HTH...

Jim Thomlinson




> I have the code below on my PERSONAL workbook and works fine. When I try to
> copy it to another workbook, it doesn't get past the first RANGE line. The
> error I get is "Select Method of Range class Failed."
>
> Why would this happen?
>
> Thanks.
>
>
> Company = InputBox("For which Company is this TB for?", "Company Code",
> "112, 115, 127, 176, 177")
> ChDir "C:\Documents and Settings\marco.rodas\Desktop"
> Workbooks.OpenText Filename:= _
> "C:\Documents and Settings\marco.rodas\Desktop\Trial Balance
> TEMP.txt", Origin:= _
> xlWindows, StartRow:=1, DataType:=xlFixedWidth,
> FieldInfo:=Array(Array(0, _
> 1), Array(1, 1), Array(13, 1), Array(34, 1), Array(75, 1), Array(94,
> 1), Array(113, 1))
> Range("B11:G20192").Select
> Range(Selection, Selection.End(xlDown)).Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=3, Criteria1:="=" & Company & "*",
> Operator:=xlAnd
> Selection.Copy
>