Is there a way in Excel VBA to check a sequence of numbers in one workbook, find the last one, and continue it in another workbook?

Question

I am trying to automate going through the LastLineNo (a linear sequence of numbers increasing by 1 each time) of one workbook, finding the last value at the bottom of the data, and starting the LastLineNo of a different Workbook with the final value found in the first workbook +1.

EDIT: After some trial and error, I believe I have gotten closer to figuring it out. Now, I've gotten my code to look like this:

Dim lineNo As Long
Dim filePath As String
Dim nextValue As Long
Dim trial As Workbook

filePath = "C:\Users\Michael Christ\Documents\Excel Work INP Automation Project\Trialbook.xlsm"
Set trial = Workbooks.Open(filePath)

lineNo = Range("B2").End(xlDown).Select

Workbooks(1).Activate
Range("B2").Select
lineNo.DataSeries rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, step:=1, stop:=Range("B2").End(xlDown), Trend:=False

It is a public sub procedure. The issue I am encountering at this stage is that I get an error when I run saying "Compile error: invalid qualifier" and it highlights the title of the procedure in yellow, and highlights the variable name lineNo attached to the DataSeries object. Anyone know why it's doing this?

Thank you for the suggestions when I first asked this question, they were very helpful. Cheers.

Welcome to SO. I would suggest trying out code first then when you run into a problem or get an unexpected result then edit this question with the code or post a new question. SO is not a free code writing service. With that being said, I would first look up how to determine the last row with data, which will be a great start. — Zack E, May 01 '19 at 18:37
[This](https://stackoverflow.com/q/11169445/1188513) should help getting started. — Mathieu Guindon, May 01 '19 at 18:45
`lineNo` is a Long/Integer, a simple data type. There is no such thing as a `DataSeries` method. What exactly are you trying to do with that statement? — David Zemens, May 07 '19 at 16:45
The `DataSeries` method belongs to the `Range` type object, not an integer/long: https://learn.microsoft.com/en-us/office/vba/api/excel.range.dataseries — David Zemens, May 07 '19 at 16:47
@DavidZemens When I change my data type for LineNo to range, it pops up a different error message, stating "Run-time error '91': Object variable or With block variable not set" — mjc5592, May 07 '19 at 16:53
well, you need to *set* object variables, and you need to assign them properly. so, something like `lineNo = Range("B2").End(xlDown).Select` is explicitly wrong for several reasons. You're missing the required `Set` keyword in a range assignment statement, and also the range `Select` method returns a boolean, not an object reference. — David Zemens, May 07 '19 at 17:15
so let's back up a few steps: what are you trying to do with `lineNo`? What is it supposed to represent in this code, and how do you intend to use it? — David Zemens, May 07 '19 at 17:16
@DavidZemens lineNo represents the line number on an expense report that I am importing from a biweekly report into a master file of all expenses in a year. The code needs to find the last line number of the master file, take it's value, and go back to the biweekly report and fill in column B with a linear increase starting with the last line number of the master file + 1. — mjc5592, May 07 '19 at 17:26
See the suggested link for help finding the last row. the `Select` method returns a boolean so it will only give you a value of 1/0. Instead of `Select`, use `.Row` property. But the link above should help in a more general sense. — David Zemens, May 07 '19 at 18:17

score 0 · Answer 1 · answered May 02 '19 at 11:20

Since the numbers are sequential you just need the largest number in that column of the old spreadsheet. So, let's assume your numbers are in column A of the first worksheet, and that sheet is called "Sheet1" and that your workbooks are called OLD.xls and New.Xls. The simplest way is to start with both workbooks open. Then in New.xls in the cell where you want to start the new sequence you can just type

    =MAX([Old.xls]Sheet1!A:A)+1

Now, when you close OLD.xls, Excel will edit the formula to store the full path to old.xls automatically. You can then save new.xls.

Is there a way in Excel VBA to check a sequence of numbers in one workbook, find the last one, and continue it in another workbook?

1 Answers1