Macro to generate next number from last cell of another workbook

**larzep** · 02-21-2012, 12:30 AM

Hi,

Just wondering if anyone can help me with a coding to generate the next number from the last cell of another workbook.

It is used for a quoting spreadsheet where all quote numbers are stored on the 'Master' workbook in cell A2 and onwards.

When a button is clicked on the quote template spreadsheet, it clears the quote template then generates the next number after checking the master spreadsheet.

So far for my coding I have

Please Login or Register  to view this content.

If anyone could help I would be ever so grateful.

Thanks

**gjlindn** · 02-21-2012, 12:35 AM

Are you allowed to open the Master workbook to look at the last line (through code)?

**larzep** · 02-21-2012, 01:14 AM

I would prefer the Master workbook to remain closed and not have too many sheets opening up at the same time.

If this is not possible to have a macro search a closed workbook and generating the next number from the last cell in a column, then anything will help.

**gjlindn** · 02-21-2012, 02:44 AM

If you have a cell in your template you can assign this formula to it which should make it pretty simple

Please Login or Register  to view this content.

This makes the assumption that your data is on Sheet1 in your Master workbook and that you want to find the bottom row of Column A and the data in Column A is alphanumeric. Then you can reference that cell in your code. Otherwise, you can also use ADO (or other database connection methods) to connect to the closed workbook and get the record count of the table. If ADO is the route you want to go do a search in this forum for ADO from Excel Database...I'm sure there's a lot out there already. Thanks!

**larzep** · 02-22-2012, 06:24 PM

Anyone can assist? It has got me totally stumped

**larzep** · 02-24-2012, 01:08 AM

can anyone assist?

**gjlindn** · 02-24-2012, 03:08 AM

Thought of another way without using ADO. Try this

Please Login or Register  to view this content.

**gjlindn** · 02-24-2012, 03:23 AM

Question: Does the Master workbook need to increment each time this happens? Example A529 is the current last row in the Master workbook and then you run this process...should the Master workbook be updated to have something in A530? If that's the case, you'd be best opening the Master, adding your data and closing it in code. Writing to a closed workbook would mean using that workbook as a database and connecting with ADO to update it...not near as simple as opening/updating/closing. If you don't want the workbook viewed while updating it, you can hide it.

**larzep** · 02-26-2012, 08:42 PM

Hey gjlindn,

Thank you for your response. My quote template has 3 buttons - New, Update and Save + Print, . When ever you open up the template, you will need to click 'New'. This is where i have the coding to clear all the cells listed in my macro above and then pull the next value from the last row in the master workbook i.e. ABD0529. Once the quote is completed, you will then need to click the update button where certain cell values, including the quote number, will be copied and saved into the master in the last row. Last but not least you will then be required to click save and print. This will automatically save a copy of the quote template in a designated folder, creates a PDF and then prints.

In my update button it has the macro to open up the master, add in the values and then closing in.

I will trial out your code, but with the line

[Code] .Names("NextRow").Delete [code]

could you please just elaborate what this will do?

Sorry to be such a pain and thanks for all your efforts

**gjlindn** · 02-26-2012, 08:55 PM

Hi larzep.

Please Login or Register  to view this content.

just deletes the named range "NextRow" created in this line earlier in the code

Please Login or Register  to view this content.

**larzep** · 02-26-2012, 08:56 PM

Hey,

I've just uploaded the spreadsheets in questions.

Hope that helps.

I tried to enter in your coding suggested but it is returning a value of '7' instead of the next sequential number.

Please Login or Register  to view this content.

**gjlindn** · 02-26-2012, 09:08 PM

Sorry...I thought you were looking for the next row number. I'll update the code.

**gjlindn** · 02-26-2012, 09:26 PM

Change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

**larzep** · 02-26-2012, 09:32 PM

Hi Gjlindn,

The code works like a charm! You have been amazing and super helpful.

Can not thank you enough. Much appreciated. With this I can finally streamline my quoting process and remove the chances of doubling up on quote numbers.

Thanks again for your time.

Cheers.

**gjlindn** · 02-26-2012, 09:50 PM

You're welcome. If you could click the star icon in the post with the solution, I'd be very appreciative. Thanks!

**larzep** · 02-29-2012, 06:15 PM

Hey Gjlindn,

Sorry to bring back the dead, but got another question if you could help me out. I have tried that coding on a new set of sequential numbers and it is not returning the next value.

Example: Q9474, Q9475, Q9476, etc, etc.

With the coding

Please Login or Register  to view this content.

It only gives me a value " Q77".

Do you know how to fix this?

**gjlindn** · 02-29-2012, 10:45 PM

Sorry, the previous code I posted assumed the first 3 characters would always be text and the number would begin on the 4th character. Try this instead:

Please Login or Register  to view this content.

**larzep** · 02-29-2012, 11:21 PM

I just tried the coding but it is returning a run-time error '1004' and when i go to debug the issue it highlighted.

Please Login or Register  to view this content.

**larzep** · 02-29-2012, 11:37 PM

Also while i have your amazing assistance, i have a macro that creates a copy of the active worksheet into a new workbook. It then renames both the new workbook and worksheet based on the values of two cells then saves it to a certain location.

Please Login or Register  to view this content.

The thing is when the cell contains more then 31 characters, it gives me a run-time error 1004. Is there anyway to fix this? Alot of my data will have names that are longer then 31 letters.

**gjlindn** · 03-01-2012, 01:43 AM

Hi larzep. Did you remember to change the path and workbook name and sheet name to match your workbook? The code you pasted above still has my temp path/names in it.

Your other issue really should be a new thread since it's not related to the subject here (that way if other users run into the same issue they can find the solution created for you). I can tell you that this line will be an issue if you have more than 31 characters for a worksheet name

Please Login or Register  to view this content.

If you post this as a new thread I will be more than happy to help you with the solution. Thanks!

**larzep** · 03-01-2012, 02:21 AM

Hi Gjlindn,

Yes i have changed the parth and workbook name and sheet name to match. The coding i posted up is just a dummy one to exclude some of the details from my files. But if it helps to have the original one then ill post it below.

Please Login or Register  to view this content.

**larzep** · 03-01-2012, 02:21 AM

Hi Gjlindn,

Yes i have changed the parth and workbook name and sheet name to match. The coding i posted up is just a dummy one to exclude some of the details from my files. But if it helps to have the original one then ill post it below.

Please Login or Register  to view this content.

**gjlindn** · 03-01-2012, 02:37 AM

This line appears to be incorrect

Please Login or Register  to view this content.

Should be

Please Login or Register  to view this content.

**larzep** · 03-05-2012, 08:02 PM

Hey Gjlindn,

Finally got a moment to continue this project. I have used the following coding but i still get errors.

Please Login or Register  to view this content.

The errors i get is:

Run-time error '1004': The formula typed contains an error. Try one of the following - Make sure you've included all parentheses and required arguments.

When I click debug it always highlight the string

Please Login or Register  to view this content.

**gjlindn** · 03-05-2012, 11:21 PM

Looks like I added an extra parenthesis. Change it to this

Please Login or Register  to view this content.

Thanks!

**larzep** · 03-06-2012, 02:12 AM

You are a legend! thank you. Works perfectly

Macro to generate next number from last cell of another workbook

LinkBack

Thread Tools

Rate This Thread

Display

Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Re: Macro to generate next number from last cell of another workbook

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions