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
Range("Y1:Y" & x).Select
Application.CutCopyMode = False
Selection.Copy
Windows("SAP Template.xls").Activate
Range("N13").Select
ActiveSheet.Paste
Columns("O:O").Select
Selection.NumberFormat = "General"
Columns("P:P").Select
Selection.NumberFormat = "General"
ActiveWindow.ScrollColumn = 1
Columns("I:I").Select
Selection.NumberFormat = "General"
ActiveWindow.SmallScroll ToRight:=7
Range("O13").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC[-13]0,""I0"","""")"
' Selection.AutoFill Destination:=Range("O13:O191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("O13:O" & y), Type:=xlFillDefault
' Range("O13:O191").Select
Range("O13:O" & y).Select
ActiveWindow.ScrollRow = 13
Range("P13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-14]0,""9900000000"","""")"
' Selection.AutoFill Destination:=Range("P13:P191"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("P13:P" & y), Type:=xlFillDefault
' Range("P13:P191").Select
Range("P13:P" & y).Select
ActiveWindow.ScrollRow = 13
ActiveWindow.SmallScroll ToRight:=-2
Range("I13").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]0,""40"",""50"")"
Selection.AutoFill Destination:=Range("I13:I" & y), Type:=xlFillDefault
' Selection.AutoFill Destination:=Range("I13:I191"), Type:=xlFillDefault
' Range("I13:I191").Select
Range("I13:I" & y).Select
' Sort Macro
' Macro recorded 2/28/2007 by HopsonR
' Sort by Cost Center field
' Range("A13:T191").Select
Range("A13:T" & y).Select
Selection.Sort Key1:=Range("C13"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
Regards,
Bob
"Don Guillett" wrote:
Is row 179 the LAST row? If so,
x=cells(rows.count,"be").end(xlup).row
Also, I'll bet the selections are NOT necessary. Why not post ALL of your
code for comments.
--
Don Guillett
SalesAid Software
"Bob Hopson" wrote in message
...
Hi Don,
When I was manually recording the macro, I selected the 1st through row
179
for column BE. The macro provided me with the code and the reference to
.select . Now I am trying to automate it for the user so they do not
need
to
be concerned with any manual intervention.
Thanks for your response!
Bob
"Don Guillett" wrote:
Close. But, WHY select?
Range("BE1:BEx").Select
x=22
'or
'x=range("a2").value
Range("BE1:BE" & x).Select
--
Don Guillett
SalesAid Software
"Bob Hopson" wrote in message
...
Hi,
I have a numeric value x representing the number of rows. I would
like
to
have the range statement use x so the user does not have to manually
modify
the range, as the number of rows for a file will vary. Can someone
provide
me
with the syntax to use for the Would like scenario? Is there a
symbolic
parameter I can use to do this?
Currently:
Range("BE1:BE179").Select
Would like:
Range("BE1:BEx").Select
Thanks,
Bob