Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
Range("BE1:BE" & x).Select

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

Thanks for the suggestion. I'll give it a try.

Regards,

Bob

"okrob" wrote:

x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
Range("BE1:BE" & x).Select

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

you can combine comands without selecting items:

for example, instead of:
Columns("A:K").Select
Selection.EntireColumn.Hidden = True

you could write
columns("a:k").entirecolumn.hidden=true

and instead of these 3 individual column autofits, since they are all
in order:
Columns("L:L").EntireColumn.AutoFit
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit

you could have
columns("L:N").entirecolumn.autofit

one more combination; instead of:
Range("BE1:BE" & x).Select
Selection.Copy

you could have
range("be1:be" & x).copy

just implementing these 3 suggestions will cut your code drastically.
hth
susan


On Mar 2, 11:28 am, Bob Hopson
wrote:
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- Hide quoted text -

- Show quoted text -

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

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

Using symbolic in Excel Macro
 

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
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

Using symbolic in Excel Macro
 

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