auto saving transfered data

[seags]

I have 2 workbooks, one called "invoice" the other called "invoice log"


I can transfer the data from the "invoice" workbook to the "invoice log
workbook OK.

My problem is how do I save the transfered data in the "invoice log"
workbook and then do an auto shift down a row for when the next lot of
data is transfered from the "invoice " workbook.

 

[Ken Johnson]

Hi seags,
the only way I know is to paste the following code into the
ThisWorkbook code module of the invoice log workbook...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheets(1).Rows(1).Insert
End Sub

It's an event procedure that is triggered when you save the workbook.
All it does is insert a row at the top of the first sheet in the
workbook. When you reach the bottom of the worksheet it will result in
a run-time error. You will then have to start using the next worksheet
after changing the code to Worksheets(2).Rows(1).Insert

If you're not sure how to get the code into the right place then follow
these steps..

Copy the code. Press Alt + F11 to get into the Visual Basic Editor. Go
View>Project Explorer to ensure that the Project Explorer is visible.
Double the ThisWorkbook icon in the Project Explorer (has the green
Excel X). Paste the code into place in the white space just to the
right of the Project Explorer.

Ken Johnson

 

[Ken Johnson]

Hi seags,
If you decide you are going to try to do it this way, then you should
notice that after pasting the code the first two lines will appear red.
This is caused by the structure of the text in the forum. The forum has
added a line break to the opening line of the code. You can either edit
the opening line so that the end of it reads "Cancel As Boolean)"
(without the speech marks) or paste the following code in place which
has a VBA style linebreak to solve the problem...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Worksheets(1).Rows(1).Insert
End Sub

Ken Johnson