Posts by Walthobum

    Re: Copy filtered range into template workbook

    The other technique involved some VBA to produce a unique list and a loop through the data advanced filtering and copying to a range. Crucially this was done within the same workbook though. Then the appropriate sheets were copied to a new book and saved before the loop continued with the next value.

    I need to 'interupt' that process and copy the data to a template, (with all the calculations and charts set up), save that template and then go back to the original workbook and loop through.

    Just putting some sample books together. I'll upload soon as I'm in the office now.

    I'm using quite a large dataset which I need to analyse in some detail at various summary levels. I've been using a technique I picked up on here, (thank you), but I'm now finding the workbook is becoming too large and unwieldy.

    This is almost certainly because I'm using lots of charts to provide some insight into the data. I need to keep the charts.

    I'm now considering filtering the data by a chosen summary level and copying it into a template where my calculations and charts already exist. I've had some trouble with this in the past, I get confused with thisworkbook command. Can anyone point to a website that explains how to use this properly or give me some pointers.

    Re: Attaching specific attachments to MS Outlook emails

    The code I got from Ron de Bruin has the body of the email as a variable which it then assigns later on. The body of the email I have been asked to send is quite long and needs a lot of formatting. I found the syntax very difficutl to get my head around. I thought it would be fairly straightforward and used the same format for every line on the mail but for some reason I couldn't get it to work. I tried using HTML tags and HTMLBody but got no further.

    I'm wondering now wqhether I exceeded the maximum length for a string variable. Could that be it? I have only just thought of this by the way. It didn't occur to me yesterday when I was looking at it. Is there a maximum length for a string variable? If so could I get around it by simply using more than one variable until I have the email body complete?

    Re: Attaching specific attachments to MS Outlook emails

    Hi cytop,

    thanks for this. I actually managed to realise this was the problem line myself, (I know, progress - of sorts). The 'fix' I came up with was this:

    Set rng = sh.Range(Cells(cell.Row, 8), Cells(cell.Row, 10))

    I then got deep into the whole mess of syntax around the body of the email, HTML, vbNewLine, all of that. I think it finally sent me over the edge.

    Apologies for not getting back sooner. Is there a material difference between the fix you suggets and the one that I have posted above?

    Edit: The reason why I have:

    Cells(cell.Row, 10

    is because I got fed up of trying to work out the syntax for the body of the email and just attached a standard letter to the email attachments and referred to that in the email body. Not great but it gets me over the line on this project I think.

    I'm trying to create an emails to various recipients and attach files relevant to those recipients. I have checked out the marvellous Ron de Bruin site and I have used some of the code there. I have created a dataset in an MS Excel workbook and I'm hoping to loop through it with VBA, creating an email, populating the address box and adding attachments relevant only to the recipient, (there will only be two attachments for this project).

    The issue I am having is that on the first loop, (the first recipient), the code attaches every file in the range, (in the test book uploaded that's 6 files). On the second loop the email address is for the next recipient but has 4 files. The final loop, (in the test book there are only 3 recipients), has only 2 files. I hope that makes sense.

    How can I attach the relevant files to each email created for each recipient? Code below and test book uploaded. The test workbook has the placeholder email address "[email protected]"[/email] which I obviously replace when testing. I also replace the blanks in the Attachment paths with actual filepaths. To be clear I can get the emails to send to the correct addresses with attachments, (i.e. - it's not because some of the fields in the dataset aren't populated or have invalid email addreses). It's something to do with the loop set up I think.

    Any help greatly appreciated.


    Re: Trouble assigning macros and passing variables using userforms

    I have been playing around with a version of it to try to get a betetr understanding of what is going on. I think I'm nearly there, (yes, that means I;ve broken it several times).

    I would indeed like a run down of the code and what it does, and why.

    In particular I'm thinking I mgiht need to understand how to change the way the months are presented as I deal in financial periods rather than calendar months.

    I have also hit a problem when changing the names of buttons. Don't ask me why I have done that, I was just trying to deconstruct and rebuild the userform and code, in an attempt to understand it better. I get a 'subscript out of range' error but I think I know why. I think it's something to do with the variable being passed from the form to the procedure as I might have used the same variable name in two places. Either way, I've changed it back.

    Re: Trouble assigning macros and passing variables using userforms

    Thanks for this. On the 'With' construct issue. Is it a question of adding a '.' to the lines where one currently does not exist? If I add a '.' to those lines will the code still run, or is it possible/likely that it will crash? I only ask as the code currently works - i.e. - it does what I need it to. I am only carrying out this change as I'm leaving my current employer shortly and trying to get rid of anything in my code that will cause the end users who pick it to crash it. I know my workarounds, (well, I know what I have to enter in the input boxes), and believe me the people picking this up, won't. Therefore if it's going to cause the code to crash then I'll leave it as it is.

    In terms of the FlxMth and FrzMth they are variables that are basically equivalent to Current and Prior. FlxMth is Current and FrzMth is Prior. They are jargon effectively. It is also, as you say, one or the other.


    thanks for your help. I'm trying to understand the way VBA passes data using userforms but it's defeated me a few times now. Despite it not being too complex.

    Re: Trouble assigning macros and passing variables using userforms

    Ha. Don't worry, I doubt I'll ever feel bad for not understanding some code, no matter how straightforward it is. Here is the procedure though if you can help:

    Any help appreciated greatly.

    Re: Trouble assigning macros and passing variables using userforms

    Thanks for your help but I'm getting nowhere fast. I'm considering trying a combo/drop down box. I just can't make this work. I can't get the information entered to pass to a variable. This is the same kind of issue I've had with userforms before so it's a user issue and nothing to do with your code.