Posts by NickW

    Re: Correct VB code to close a workbook

    So, just in case it helps anyone else out, I ended up with this.

    Re: Correct VB code to close a workbook

    Hi Rory, thanks for the reply. Is there an easier whay to do it then without opening a 'temp' workbook. I only did this as the once the workbook is shared the marco stoppped working.

    And thanks for the above code, it does indeed solve the problem :)


    Re: Correct VB code to close a workbook

    I managed to fix it, I realised where I was going wrong too.

    FileToOpen = ActiveCell

    That was bringing back the whole file name e.g. c:dir/dir/filename.xls

    Where as what I needed was just filename.xls

    so I just added this within the code when its in the workbook I need, I know its probably a bit sloppy still but it works for me;

    temp = ActiveWorkbook.Name

    Firstly I hope I've placed this in the correct forum.

    Secondly, I apologise for the very messy code below, its a result of 'gathering' various other code and recording macros to get this to do what I require.

    I've got a workbook, with a couple of worksheets in, 1 of which is a dashboard, so the macro below grabs the staff listed in the other worksheet copies them to a new one then filters the list and returns them, once its done its sorts into alaphbetical order and then copies a formula down each adjacent cell.

    As I'm sure many will tell, I'm not great with VB scripting, but this does work, it worked much better before I remembered I had to share the workbook, hence the copy out to a new sheet. The bit I'm stuck on is

    Workbooks(Temp).Close SaveChanges:=False

    For some reason it just errors, I've tried various combinations but I just can't get it to work.

    Any help greatly appreciated, the whole script is below.



    I have a spreadsheet that is imported from another software programme. One of the columns has a value which contains updates from users. I am only inerested in viewing the last comment in this cell and wondered if it would be possible to delete all the text from the Cell except for the last few (3 or 4) rows as this would then only leave the latest update.



    Hi I am using the following macro to insert rows into a protected spreadsheet. It all works fine unless the user hits cancel on the popup of how many rows they want to insert, I have tried various combinations but can't get it to protect the spread sheet after they cancel or if it does it then doesn't protect it when they do insert rows....

    Hi, I am trying to use the formula below to get the filename of the spreadsheet, it seems to work fine but when you open a second sheet the first sheet will pick up the name of the second.


    These spreadsheets will then be uploaded to another application and having 2 with the same name could cause issues.

    Obviously the answer is to only open one at a time, but this is always subject to human error!

    Any help would, as always, be appreciated.


    Re: Increment Numeric Part Of Alphanumeric Text

    Thanks for the reply.

    I realise I should have replied to the other posts and I am very sorry for not doing so, this is a great place for help and I will make sure not to abuse it.

    Once again thanks for solving this one for me.

    A very humble Nick W

    Re: Adding Filename To Merging Cells Vba Sript

    This one seems to work really well.

    It just gets run before we export some data, but I know need the file name to be exported to so that we have an audit trail.


    Hi I have the following VBA script that merges 2 cells together, but at the end of the merge I need it to add the filename and path of the active workbook.....any takers:?


    I am having an issue using vlookup with named ranges.

    I have a work book with 3 sheets on, the 3rd sheet has lists defined as named ranges and is named as lists.

    The first and second sheet has references to the lists on this sheet, using the validation e.g
    =(named range)

    I was using the following if statement =IF($E$7="web policy",web_policy,IF($E$8="NUD",BU,IF($E$8="RAC",BU,IF($E$8="IB",IB,IF($E$8="BAU",BAU,not_known)))))

    But I now need to add some more to this and have decieded to use Vlookup instead.

    I have the vlookup in the 3rd sheet (lists sheet) and when the vlookup result is returned it returns the list name that I need to select, the problem I am facing is using this result to produce the list on sheet 1 or sheet 2.

    The vlookup I am using is =VLOOKUP('Test Conditions'!E8,AD1:AE7,2,FALSE), but I can't use this on the first sheet as validation can't look at other sheets.

    Any ideas of a way round this.


    Nick[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I have now changed formula slightly to;=VLOOKUP(ad32,AD1:AE7,2,FALSE), AD32 contains the formula that references the first sheet, but when I paste this into the validation I get the following error.
    The list source must be a delimited list, or a reference to a single row or column

    I realise this is a bit of a crude way of doing it, but if it works I will be happy....