Store Value of Last Row even after the procedure ends and Excel Closes?

[YellowLabPro]

I am working w/ three sheets in total, one each in three different books.
Book1.Sheet1 and Book2.Sheet2 & Book3.Sheet1. Books 1 & 2 will send data to Book3.Sheet1.

Book1.Sheet1 's data will be new records each time, and all the new records will be sent to Book3.Sheet1.

Book2.Sheet1 's data will be cumulative and only the new records should be sent to Book3.Sheet1.

Book3.Sheet1 's data records are also cumulative. So all the data from Book1.Sheet1 and Book2.Sheet1 will be appended to the exisiting records in Book3.Sheet1.

Can I store the last row value of Book2.Sheet1 somehow so the next time I append records to Book2.Sheet1 there is a value stored to reference where to begin a new data copy to Book3.Sheet1?

[lucas]

Couple of thoughts ylp, Since your just using one sheet one each workbook why not just use 3 sheets in one workbook...if possible?

If you must use 3 different workbooks then have book 3 updated when you close book 2...

[YellowLabPro]

Thanks Steve-
I have three workbooks due to them being in different locations and w/ mulitple sheets. I only used the example for simplicity reasons.

One workbook has numerous sheets that support the creation of new records.
One workbook is the nucleus of the other two and actually has 9 sheets and will expand.
One workbook is sent off for data entry.
Unfortunately I cannot have them all update and run at the same time.

One thing I am exploring is finding the last row +1 in Book2 and writing that value to that 1st unused row.
I am thinking I can pick up that value as my variable the next time I run the code.....
Row 86 is the first unused row currently, so if I find the last row, add one and place that value in the first unused row then I have a starting point.
I have code up to writing the value. I am working on code to pick up and insert this value in my code.
Never tried this.....

But this is not a very clean way to do it.... I will have a orphan value in my sheet that will get written to Book3 now.... so I have to delete this.... but it might be the only way.

[rory]

Use a defined named and just assign the row number to it and save the book?

[YellowLabPro]

This seems to work:
[vba]
Dim x As Integer
x = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("K1").Value = x
With wstSNBD
i = sSNBDi + 1
ii = Range("K1").Value
tSNBDi = tSNBDi + 1
For i = i To ii
[/vba]

[YellowLabPro]

That is an even better idea Rory....

[unmarkedhelicopter]

This is a dangerous thing to do as :-
1. it is an easily tested for value and so only costs you milliseconds in getting it.
2. someone could delete your value
3. someone could 'not run' your code, add/remove a few lines and your code is out of step

Only ever do this sort of thing if the only way to change the value your value represents is via 'your code' or if the value is a throwaway instance.

But some people like fragility so feel free ...

[Paul_Hossler]

Could also use a Custom document Property

From Help --

[VBA]
With ActiveDocument.CustomDocumentProperties
.Add Name:="CustomNumber", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1000
.Add Name:="CustomString", _
LinkToContent:=False, _
Type:=msoPropertyTypeString, _
Value:="This is a custom property."
.Add Name:="CustomDate", _
LinkToContent:=False, _
Type:=msoPropertyTypeDate, _
Value:=Date
End With
[/VBA]

Paul

[rory]

You could define the name to point to the last used row. It will update if rows are deleted and if that row is deleted, the error will alert you that something has been messed up.

[YellowLabPro]

Thanks Paul, though I have no clue what to do w/ this.... <G>. Let me get all the kinks worked out and I will post my entire code.

This is a dangerous thing to do as :-

But some people like fragility so feel free ...

Heli- and this is advice from a guy who flys around inches above snowcovered peaks

[unmarkedhelicopter]

Why even bother ?
If your code performs using the last record and your code is robust enough to cater for the usual user values "I know it said enter a number from 1 to 9 but "Three" is a number from 1 to 9 !" then it either works or your input validation is not up to scratch. If you take the view that your users are "out to get you" then as you said in an other post "use something other than Excel" or give up now !

[YellowLabPro]

Heli,
Is this directed to my thread?

Why even bother ?
If your code performs using the last record and your code is robust enough to cater for the usual user values "I know it said enter a number from 1 to 9 but "Three" is a number from 1 to 9 !" then it either works or your input validation is not up to scratch. If you take the view that your users are "out to get you" then as you said in an other post "use something other than Excel" or give up now !

[unmarkedhelicopter]

Was it in your thread ? Yes
Is it directed at your thread ? Well, to Rory's comment on my comment on what you were doing so ... Yes

[Norie]

Why not just create references to the workbooks/worksheets?

Also why not combine the procedures?

[rory]

I didn't realise that was directed at me!
Since we are not talking about finding the last used row this time, but the last row last time so that we can determine new records, it seems to me that there are two options:
1. Do a match up exercise on all records, update existing ones and add new. This would obviously be most robust.
2. Store information about the last row each time and only add from there in the next code run. Depending on the controls in place on the workbook, this may or may not represent an acceptable risk.

Obviously, I was being lazy and answering the question asked, rather than necessarily providing the optimal solution...