issue copying data

S

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("N45:eek:57").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?
 
D

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
 
J

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional If/Else statement 2
Macro Problem 1
Macro for Costing 0
Macro- Excel 2003 3
Macro - very slow run in 2003 3
Auto filter using macro not woking 2
Help me4 2
code error - memory error 2

Top