Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using symbolic in Excel Macro
x = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange. Cells.Count).Row
Range("BE1:BE" & x).Select |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I show radians in symbolic format in excel? like 90 degrees would show (Symbol Pi/2) tia sal2 | Excel Worksheet Functions | |||
Better example I hope how can I show radians in symbolic format in excel? like 90 degrees would show (Symbol Pi/2) tia sal2 | Excel Discussion (Misc queries) | |||
Better example I hope how can I show radians in symbolic format in excel? like 90 degrees would show (Symbol Pi/2) tia sal2 | Excel Worksheet Functions | |||
How can I show radians in symbolic format in excel? like 90 degrees would show (Symbol Pi/2) tia sal2 | Excel Discussion (Misc queries) | |||
How can I show radians in symbolic format in excel? like 90 degrees would show (Symbol Pi/2) tia sal2 | Excel Worksheet Functions |