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

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

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

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/

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