Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to improve this code?
Hi,
I save all the data of each invoice in one row of my DATA sheet. Belo is my sub doing that. In fact, it is much bigger, I truncated it a bi in this example. I offset one column for each individual data of the invoice. I suspect that: ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L12") is not the ultimate way of doing it. But it is working fine. I jus want to improve my coding as much as I can. Any suggestions? Thanks! Alain Sub Save_And_New_Invoice() Dim Line Application.ScreenUpdating = False Sheets("Data").Visible = True Sheets("Data").Select Line = LastLine(Sheets("Data")) Range("A1").Select ActiveCell.Offset(Line, 0).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L6") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L12") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E12") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E13") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E14") Range("A1").Select Sheets("Invoice").Select ActiveWorkbook.Save Sheets("Data").Visible = False Application.ScreenUpdating = True Application.Run "New_Invoice" End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to improve this code?
Alian,
Try something like this. You'll have to do all your lines the same way ActiveCell.Offset(Line, 0).Range("A1") = Sheets("invoice").Range("L6") ActiveCell.Offset(0, 1).Range("A1")= Sheets("invoice").Range("L12") HTH Charle -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to improve this code?
Alian
This is the code I would use Sub Save_And_New_Invoice() Dim WsData As Worksheet Dim WsInvoice As Worksheet Dim lRow as long Set WsData = Sheets("data") Set WsInvoice = Sheets("invoice") Application.ScreenUpdating = False lrow= LastLine(WsData) WsData.Cells(lrow, "a") = WsInvoice.Range("L6") WsData.Cells(lrow, "b") = WsInvoice.Range("L12") WsData.Cells(lrow, "c") = WsInvoice.Range("e12") WsData.Cells(lrow, "d") = WsInvoice.Range("e13") WsData.Cells(lrow, "e") = WsInvoice.Range("e14") ActiveWorkbook.Save Application.ScreenUpdating = True Application.Run "New_Invoice" End Su -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to improve this code?
You suspect right. The mantra of the group is that it is rarely necessary to
do a select. Sub Save_And_New_Invoice() Dim Line Application.ScreenUpdating = False Line = LastLine(Sheets("Data")) With ActiveCell .Offset(Line, 0).Range("A1").Value = Sheets("invoice").Range("L6") .Offset(Line, 1).Value = Sheets("invoice").Range("L12") .Offset(Line, 2).Value = Sheets("invoice").Range("E12") .Offset(Line, 3).Value = Sheets("invoice").Range("E13") .Offset(Line, 4).Value = Sheets("invoice").Range("E14") End With Range("A1").Select Sheets("Invoice").Select ActiveWorkbook.Save Sheets("Data").Visible = False Application.ScreenUpdating = True Application.Run "New_Invoice" End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "alainB " wrote in message ... Hi, I save all the data of each invoice in one row of my DATA sheet. Below is my sub doing that. In fact, it is much bigger, I truncated it a bit in this example. I offset one column for each individual data of the invoice. I suspect that: ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L12") is not the ultimate way of doing it. But it is working fine. I just want to improve my coding as much as I can. Any suggestions? Thanks! Alain Sub Save_And_New_Invoice() Dim Line Application.ScreenUpdating = False Sheets("Data").Visible = True Sheets("Data").Select Line = LastLine(Sheets("Data")) Range("A1").Select ActiveCell.Offset(Line, 0).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L6") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("L12") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E12") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E13") ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = Sheets("invoice").Range("E14") Range("A1").Select Sheets("Invoice").Select ActiveWorkbook.Save Sheets("Data").Visible = False Application.ScreenUpdating = True Application.Run "New_Invoice" End Sub --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to improve this code?
Hi,
Thanks everybody! I also see that it is not even necessary to select the sheet and mak it visible to work on it. Thanks again! Alain (not Alian -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
How can I set the tab to improve navigation in Excel? | Setting up and Configuration of Excel | |||
Help improve mySchedule Please | Excel Discussion (Misc queries) | |||
How can improve this formula? | Excel Worksheet Functions | |||
Need to improve a formula | Excel Worksheet Functions |