Posts by Xigol

    Re: Macro to use correct workbook


    Im probably being real slow here? So whats the way around it? Ive tried using activeworkbook, but it doesnt support range. The loop doesnt seem to actually move through workbooks.


    Is there no other way?

    Re: Macro to use correct workbook


    That would work, except that I have no way of telling what workbooks will be open, nor what the names of the sheets will be. Some may have Sheet 1, some may not. For example, I have one that I need open with worksheet named Area 1. So i get the good old subscript out of range.


    :)

    Re: Macro to use correct workbook


    I was really trying to avoid using distinguishing values or anything. But i try to use it and it didn't work. The code i used was


    Code
    1. For Each wkb In Application.Workbooks
    2. If Range("E9").Value = "a" Then
    3. strOrderWkbID = wkb.Name
    4. Exit For
    5. End If
    6. Next wkb
    7. Windows(strOrderWkbID).Activate


    However, when I do this it goes through the loop the number of times that I have workbooks. Eg 4 workbooks open, goes though the loop 4 times. But it keeps checking the same workbook. Not sure why. Any other way to do it.??

    Me again. The code for that other question I had worked great, silly me just realised i solved a problem i didnt have.


    My problem is. I have a macro ---> Mname


    I have work books. (All open simultaneously)
    Iwant.xls
    Iuse.xls
    NotUsed.xls
    Notused2.xls
    Personal.xls


    Now Mname is stored in Personal


    I need Mname to select Iwant without referring to it directly as Mname is used with many workbooks, and the name changes. I kind of asked this question before. But badly.


    I have tried going through all workbooks, but i dont know how to make Iwant be distinguishable from NotUsed.


    Thanks anyone who can help

    Hi, I was wondering what the proper syntax for testing a variable on if the first letter is a number or not. However, the variable is stored as a string.


    Eg. I want it to return.


    True if strNme = "612504"
    False if strNme = "Not a number"


    i thought the syntax was somewhere along the lines of.
    if Instr(strNme, Left, 1) = 1 or 2 etc then
    Blah
    end if


    Thanks guys, Its pretty simple, but i cant remember

    Re: Multiple Workbooks using one Macro


    The button is in the order form, and copies summarised information from itself to the summary workbook. Yeah, I got a lot of fiddling around to do. Thanks for your help, i should be able to get it to work from here, work as far as i want it too anyway. :D


    Thanks a lot

    Re: Multiple Workbooks using one Macro


    Its meant to be run from a button, but while im testing it I've been running it through the module editor itself. Uusally in debug mode to catch it at where it never picks up the name. Its actually meant for someone who doesnt use excel much at all and she just needs to press a button for it all to happen.

    Re: Multiple Workbooks using one Macro


    Ok, yeah, im not the best at explaining myself am u :).


    Unfortunately my macro currently is not very efficient, with some hardcoding and redundant coding. But this is generally meant to be a quick fix for a problem i didnt need it to be coded well. Though its hard now.

    Re: Multiple Workbooks using one Macro


    I just tried using ActiveWorkbook and it activates the wrong workbook, it activates the summary workbook instead of the order workbook, still can't seem to figure it out. If anyone knows how to make global variables between multiple projects and workbooks that would solve it. Thanks

    Re: Multiple Workbooks using one Macro


    I will have a look at it. However, the code doesn't need to be in a loop because I'm only running the code once for one work book. It depends on the work book.


    Scenario:
    I have suppliers who make orders. Each supplier has a seperate workbook. Each order for each supplier goes onto a summary work book. I want it to when they click on the button, that it runs a macro (that is universal) that copies the summarised data to the summary order workbook. So I need to run macros as I make orders.

    I have around 40-50 odd workbooks and I need them to use one macro. Now that isn't that much of a problem using the personal macro workbook (which i dont like that much).


    Currently when I need to update the macro i need to do it 40-50 times because they are all using seperate macros. I wish to stop this, my problem is that they refer to their own workbook to activate and I dont know how to do this dynamicly.


    ThisWorkBook.name property won't work because the macro is in the "Personal.xls" macro. I was trying to use a global variable but declaring a variable with 'Public' in front caused errors unlike the MS help said it would. I know there is probably a quick easy answer to this. I did search, but couldnt find exactly what i was looking for.