issue copying data

[Sj]

Hello,

I have a macro as follows :

'
Range("A45:A57").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=bt*", Operator:=xlAnd
Range("A48:I56").Select
Windows("Odd workbook.xls").Activate
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Customers"
Sheets("BT").Select
Range("A3").Select
ActiveSheet.Paste
Range("M3:N3").Select
Windows("Odd workbook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Range("N4557").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=bt*", Operator:=xlAnd
Range("N46:w56").Select
Selection.Copy
Windows("Customers.xls").Activate
Range("M3:N3").Select ***
ActiveSheet.Paste
Range("A3").Select ***
Windows("Odd workbook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Range("A53").Select
End Sub


My problem is that as a macro this works fine and I get the data transferred
properly, however when I try and copy this code to a command button it starts
to go wrong and I get error messages and when I debug the lines I have typed
the 4* are highlighted in yellow and I don't understand why. I tried to
delete these and all that happened was that the first data got overwritten by
the second, any help please?

 

[Don Guillett]

You may like this better. Put in the Source workbook and change to suit

Sub FilterCopyToOtherWB()' Assign to shape or button
Workbooks.Open Filename:="C:\yourfolder\destinationfilename.xls"
Sheets("sheet1").Select
With Workbooks("sourcefilename.XLS").Sheets("sourcesht").Range("A1:z57")
.AutoFilter Field:=1, Criteria1:="2"
.Range("a2:b57").Copy Range("a3")
.AutoFilter

'second field col N is field=14
.AutoFilter Field:=3, Criteria1:="14"
.Range("c2:c57").Copy Range("m3")
.AutoFilter
End With
ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[Jim Cone]

Whenever dealing with more than one sheet in Excel,
you should qualify all range callouts with the sheet name...
Worksheets("Customers.xls").Range("M3:N3")
--
Jim Cone
Portland, Oregon USA



"Sj" <[email protected]>
wrote in message
Hello,
I have a macro as follows :
'
Range("A45:A57").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=bt*", Operator:=xlAnd
Range("A48:I56").Select
Windows("Odd workbook.xls").Activate
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Customers"
Sheets("BT").Select
Range("A3").Select
ActiveSheet.Paste
Range("M3:N3").Select
Windows("Odd workbook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Range("N4557").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=bt*", Operator:=xlAnd
Range("N46:w56").Select
Selection.Copy
Windows("Customers.xls").Activate
Range("M3:N3").Select ***
ActiveSheet.Paste
Range("A3").Select ***
Windows("Odd workbook.xls").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Range("A53").Select
End Sub

My problem is that as a macro this works fine and I get the data transferred
properly, however when I try and copy this code to a command button it starts
to go wrong and I get error messages and when I debug the lines I have typed
the 4* are highlighted in yellow and I don't understand why. I tried to
delete these and all that happened was that the first data got overwritten by
the second, any help please?