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