issue copying data
 

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("N45:o57").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?

issue copying data
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sj" 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("N45:o57").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?

issue copying data
 

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"
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("N45:o57").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?