Replies

Hi Amy,
I’m glad it worked! The 65,536 you asked about is the last row in Excel… at least it is the limit in the older versions of Excel (I use Excel 2002). The code basically starts with the last cell in that row (cell B65536) and then counts how many rows up from that cell to the bottom of the where the data is located (cell B29). This “count” is then used as the “x” to determine the bottom of the database for the “autofill” portion of the code.
Regards,
Lana

This worked… thanks Hans!
Lana

Hi Amy,
I’ve attached your worksheet with a macro that should do what you’d like it to do (press the pink button… I’ve attached the macro to it). I’ve inserted comments into the code so that you can see what I did. Also, please note that I changed the vlookup formula (in column C), likewise I changed the subtotal formula in A7… hopefully I assumed correctly in making these changes.
Regards,
Lana

I tried the change and I still couldn’t get it to work… I’ve attached a small version.
Thanks!!
Lana

Okay… I learn something new everyday… I was told years ago it had to be on the same worksheet and I always thought that it was odd! Thanks for setting me straight… this helps out tremendously… I just got your second response… you read my mind, as I was still interested in learning the form thingy in Visual Basic. I’ve just found it 10 minutes ago and I was playing around with it… how does the form get from the Visual Basic into Excel for the people to use??
Thanks Hans!!
Lana

I got it to work…
I had to define the vaRecipient as follows:
vaRecipient = Worksheets(“lana”).Range(“A3”)
Also, I replaced the SendTo: part of the code with .SendTo = vaRecipient
Yahoo…
Thanks for the help Hans!
Lana

Hmmm… I must be doing something wrong… it accepts the code, however now it says that there is no name to send to and highlights the “.Send 0, vaRecipient” portion of the code. Any ideas? I’ve attached the worksheet with the macro in it.
Thanks!!!
Lana

Yep, I wondered if Jan’s code was opening a 2nd session… I’m showing how green I really am, as I didn’t understand it. You can probably tell by my code that I’m more familiar with code that deals with moving stuff around in Excel, as opposed to actual programming code. So I understand what Jan wrote, could someone please explain what each line means (especially the part on where in the the code it says to go out and open the 2nd session). I’ll take care to heed Hans warning, as I know I’m quite the rookie… I may need to just live with the fact that my macro may run slow for this project… macros have spoiled me, as I love the completion of tasks in the speed of light for everything and everyone!
Thanks!
Lana

I’ll give this a try… thanks so much for your help Jan!
Lana

Attached is my file & macros (without the real data)… I’ve added Jan’s code as suggested, and tried it out, however I’m sure I don’t have it right (as it slowed down after 31 workbooks), and I’m not sure how it would know to stop or shut down after 30 workbooks saved, and how it would know where to start back up again on the list of customers??? Any ideas would be awesome!! The main macro is named CreateStmts in module 3.
Thanks so much!
Lana

Thanks to all for responding! I’m unsure as to how to incorporate the code Jan wrote into my code. Do I put the function in a module, or attached to a particular worksheet, and how do I get it to work?
Thanks!
Lana

I tried the two different options you mentioned Hans… and with each option (turned off virus, saved files to local drive), it still slowed dramatically down at the 32nd workbook. Any other ideas?
Thanks!
Lana

Thank you Hans & Jerry… I appreciate the help!
Lana

Thanks Jerry & Hans… these examples help me understand it! I appreciate the tutorial!!
Lana

Awesome… thanks Hans!
Lana