Don,

Thanks again for your suggestions. I had a problem using the Range code
below. I emailed you my .bas code. Let me know if you can take a look.

Regards,

Bob Hopson

"Don Guillett" wrote:

Once you have the sorce workbook open you may copy from the destination
workbook without any selections. Notice the placement of the dot .
Just incorporate this into your macro OR call the copy sub from within the
main sub

Sub copyvaluesfromopenworkbook()'Gets value
'NOTICE that range size must be the SAME size
With Workbooks("Menu.xls").Sheets("trythis")
Range("a12:c14").Value = .Range("a2:c4").Value
Range("a22:c24").Value = .Range("a22:c24").Value
'etc
End With
End Sub

Sub copycellsfromopenworkbook()
'copies the range to specified cell
With Workbooks("Menu.xls").Sheets("trythis")
.Range("a2:c4").Copy Range("a3")
.Range("a12:c14").Copy Range("a13")
'etc
End With
End Sub

--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Don,

Please look at the code. I am getting a run-time error noted in the
comments
under ***

Sub COPYUSB()
'
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP
upload
template.
'
' <To be used for Feb and March 2007 files until USBank can create file in
April
' This macro will run against the USBank download file and display only
those fields
' needed to be copied into SAP upload template.
'
' Open usbank pcard file and delete header row
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
Rows("1:1").Delete
' Hide unneeded coluns
Range("A1:K1,O1:X1,Z1:AC1,AE1:bd1").EntireColumn.H idden = True
' Format columns width
Range("L1:N1,Y1").EntireColumn.AutoFit
Columns("AD").ColumnWidth = 40
Columns("N").NumberFormat = "0.00"
' Split Accounting Field into Company, Account Number and Cost Center
fields
'
Columns("AD").TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))
' Columns("AD").TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth
' FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))
' Locate last row in usbank pcard file; to be used to format data in SAP
Excel file
x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
y = x + 12
' Start copying columns from usbank file and paste into SAP Excel file
Range("BE1:BE" & x).Copy
Windows("SAP Template.xls").Activate
' ActiveSheet.Unprotect
' Range("A13").Select
' ActiveSheet.Paste
' ***** Receive run-time error 438 when executing Range("A13").Paste
Range("A13").Paste
Windows("usbank pcard.xls").Activate
' Range("BF1:BF179").Select
Range("BF1:BF" & x).Copy
Windows("SAP Template.xls").Activate
Range("B13").Paste
Windows("usbank pcard.xls").Activate
Range("BG1:BG" & x).Copy
Windows("SAP Template.xls").Activate
Range("C13").Paste
Windows("usbank pcard.xls").Activate
Range("N1:N" & x).Copy
Windows("SAP Template.xls").Activate
Range("J13").Paste
Windows("usbank pcard.xls").Activate
Range("Y1:Y" & x).Copy
Windows("SAP Template.xls").Activate
Range("N13").Paste
Range("I1,O1:P1,").EntireColumn.NumberFormat = "General"
With Range("O13").FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
.AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
End With
With Range("P13").FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
End With
With Range("I13").FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
End With
Range("A13:T" & y).Sort Key1:=Range("C13"), _
Order1:=xlAscending, Header:=xlGuess
' Selection.Sort Key1:=Range("C13"), Order1:=xlAscending,
Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub






"Don Guillett" wrote:

Let's give it a try. UN tested And, the going back and forth between
wbs
can be eliminated.

Sub COPYUSB()
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP
upload
'template.
'
' <To be used for Feb and March 2007 files until USBank can create file
in
'April
' This macro will run against the USBank download file and display only
'those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
range("A1:K1,o1:x1,z1:ac1,ae1:bd1").EntireColumn.H idden = True
range("L1:n1",y1).entirecolumn.AutoFit
Columns("AD").ColumnWidth = 40
Columns("N").NumberFormat = "0.00"
Rows("1:1").Delete
Columns("AD").TextToColumns
Destination:=Range("BE1"),DataType:=xlFixedWidth

'I don't quite understand what you are doing here so will leave it.
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
'--------
Range("BE1:Bg" & x).Copy
Windows("SAP Template.xls").Activate

' ActiveSheet.Unprotect
Range("A13").Paste

Windows("usbank pcard.xls").Activate
Range("N1:N" & x).Copy
Windows("SAP Template.xls").Activate
Range("J13").Paste

Windows("usbank pcard.xls").Activate
Range("Y1:Y" & x).Copy
Windows("SAP Template.xls").Activate
Range("N13").Paste

range("i1,O1:p1,").entirecolumn.NumberFormat = "General"

with Range("O13")
..FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
..AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
..AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
end with

with Range("P13")
.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
end with

with Range("I13")
.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
end with

Sort by Cost Center field
Range("A13:T" & y).Sort Key1:=Range("C13"), _
Order1:=xlAscending, Header:=xlGuess
End Sub

--
Don Guillett
SalesAid Software

"Bob Hopson" wrote in message
...
Don,

Here is the macro code. I am pretty new at this as you can tell. I
would
appreciate any suggestions to streamline the code/processing.

Sub COPYUSB()
'
' COPYUSB Macro
' Macro recorded 1/29/2007 by HopsonR
'
' This macro will copy the data from USBank pcard.cls file to the SAP
upload
template.
'
' <To be used for Feb and March 2007 files until USBank can create file
in
April
' This macro will run against the USBank download file and display only
those fields
' needed to be copied into SAP upload template.
'
Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
Columns("A:K").Select
Selection.EntireColumn.Hidden = True
Columns("O:X").Select
Selection.EntireColumn.Hidden = True
Columns("Z:AC").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.SmallScroll ToRight:=1
Columns("AE:BD").Select
Selection.EntireColumn.Hidden = True
' ActiveWindow.LargeScroll ToRight:=-3
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("Y:Y").EntireColumn.AutoFit
Columns("AD:AD").Select
Selection.ColumnWidth = 40
' ActiveWindow.ScrollColumn = 1
Columns("N:N").Select
Selection.NumberFormat = "0.00"
Rows("1:1").Select
Range("L1").Activate
Selection.Delete Shift:=xlUp
Columns("AD:AD").Select
Selection.TextToColumns Destination:=Range("BE1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(12, 1))

Rows("1:1").Select
x = 1
Do While Cells(x, 1).Value < ""
x = x + 1
Loop
x = x - 1
y = x + 12
' ActiveWorkbook.Save
' Workbooks.Open Filename:="U:\USBank SAP\Test3 Feb 2007\usbank
pcard.xls"
' Range("BE125:BE179").Select
' ActiveWindow.LargeScroll Down:=-4
' Range("BE1:BE179").Select
Range("BE1:BE" & x).Select
Selection.Copy
Windows("SAP Template.xls").Activate
ActiveSheet.Unprotect
Range("A13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BF1:BF179").Select
Range("BF1:BF" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("B13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("BG1:BG179").Select
Range("BG1:BG" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("C13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("N1:N179").Select
Range("N1:N" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("J13").Select
ActiveSheet.Paste
Windows("usbank pcard.xls").Activate
' Range("Y1:Y179").Select