Posts by gmccreedy

    Re: Reading contents of cell as file name


    I have been very successful with the following. modify as needed.


    this example shows a variable file path and filename represented in cells A1 and A2 respectively.


    Re: Forms Button - Macro runs, and won't STOP!


    you need to "Dim" p...in other words, you are using "p" as a variable in your if statement.


    Enter this below the title


    Code
    Dim p As Integer


    and for the hell of it, delete the duplicate commands...


    i.e.

    Code
    Range(Selection, Selection.End(xlDown)).Select 
        Selection.Delete Shift:=xlUp


    Not sure if this will help you, but it certanly will make the function work better for you.


    And just so you know, after you hit "delete", if you look really close, you will see that the delete button doesn't delete...that may be causing your problem. why don't you move the delete button away from the rows that are being deleted and see if that helps you.


    Edit:
    I just found a problem. try deleting the 4 delete buttons that are on top of each other. I just fixed up your code like I said above, deleted the other three buttons, moved the remaining delete button away from the delete rows and it works fine. having trouble attaching do to file size restrictions. easy to fix though.

    Re: Forms Button - Macro runs, and won't STOP!


    Hi,


    not sure, craziest thing i ever seen. Seems to happen, then if you move or zoom the screen it goes away, then comes back again. Are all the sheets the same? you say you are just having trouble with "March", why don't you just recreate the sheet by copying from one of the other months that work.


    unless someone else has a good answer to this, I think that is your best bet.


    Good luck and sorry i was of no help.

    Re: Find, Copy and Paste


    You may need to adjust some of the input below to add lines or the such to fit your set-up. Without seeing the file and how the data is getting entered and calculated, i can't give you exactly what you need. Can't even guarentee this will work, but it could get you started at least. Hope this helps. any problems, shoot me a PM and i will try and help some more.


    Re: Find, Copy and Paste


    Ok, one more clarification,


    Do the employees actually open up and enter the data you are organizing in the same sheet you are generating the cost estimate in? sorry, i am just missing the connection between where the employees is entering the data and where it is "living" in comparison with your Cost estimate sheet.



    i can help you either way so let me know. i am also assuming that you have the userform set up already, right?

    Re: Find, Copy and Paste


    Hi winny,


    Are they entering this data in separate worksheets? (ie. does each employee have there own sheet of information?)


    i think a little more information may need to be provided here. Are you looking to have the "employees" add the data through a userform, or just simply adding the information to cells.


    So if i see it correctly, Cells 1 and 2 are user entered, cell three is a tabulation of 1 and 2. if each employee has there own sheet, then simply setting up forumla linking the two sheets together would appear suffucient for this small amount of data.

    Re: Userforms


    Hi abbe,
    I have to agree with will here, i fail to see the need to construct code when simple formula's seem to be the appropriate method.


    Perhaps if you explain a little more about what your trying to accomplish, then maybe a better understanding of your dilemna would be accomplished.

    Re: Copy and paste issue


    Hi jack,


    hey no problem, sorry for the inconvenience. i should have watched what i was doing. thanks for the fix.



    Charles....You are the Man!!! thanks a bunch...it worked PERFECTLY!!!


    This site rules!!! so much information useful to take in. i fear that i will be hanging around here quite a bit. :rock:

    Re: Copy and paste issue


    i don't think vlookups are the way to go. Just so you can get an idea of what this "database" is going to look like, its about 63 columns of information, and can be limitless with the amount of pumps added (one pump being one row of 63 columns.)


    So what is happening is if two people are using the design sheets at the same time, they can keep up to date with the latest information in the database by recalling the data when a userform or macro is enabled or called upon.


    Here is the code i was talking about where the "message box" keeps haunting me.
    [vba]
    Dim Lastrow As Integer
    Dim Directory As Variant
    Dim Filename As Variant

    Sheets("Introduction").Select
    Range("H22").Select
    Directory = Selection
    Range("I22").Select
    Filename = Selection
    ChDir Directory
    Workbooks.Open Filename:=Filename


    ' This opens directory from a predefined location path set in
    ' cell "H22" and Filename in cell "I22"


    Lastrow = Application.CountA(ActiveSheet.Range("A:A"))
    Sheets("database").Cells(Lastrow + 1, 1) = UserForm2.TextBox1
    Range(Cells(3, 1), Cells(Lastrow + 1, 63)).Select
    Selection.Copy
    Application.CutCopyMode = False
    ActiveWindow.Close savechanges:=False


    ' The haunting message box pops up!!!
    ' closing database reactivates the pump design sheet with a 'tab name "pump database"


    Sheets("pump database").Select
    Range("A1").Select
    ActiveSheet.Paste
    [/vba]


    honestly, this is not a really big problem, but it is just annoying the ***** out of me, and i feel so iliterate because i don't know VB enough to troubleshoot it. (i am basically a "record macro" or 'steal code from somewhere else and tweak" kind of guy...lol.

    hello all,


    I have a small dilemna, but figured i would search for some advise in my "limited" vba experience to date.


    I have setup a spreadsheet that assists my firm in designing pump stations for sanitary sewage. It does numerous functions, and i have it working wonderfully. Just recently, i was approached to add some additional features to the spreadsheet that would automate the process of selecting different types of pumps (and consequently their respective pump specifications) for analysis and design purposes. (Until this point this data had to be entered manually).


    So...that said...What I am attempting to do is establish one file 9call it the database) of all the pump manufacturers and there respective pump specifications. The design sheets could then recall, add or edit information from the database.


    here is my dilemna...to streamline the process of searching through the data (and not setting up links) i have written code that opens the database, copies all the information present, then pastes it into the active design sheet. I would like to run the process so that the information is copied, the database is closed, then the information is pasted into the design sheet. i can get this to work, but due to the size of the "copied information" it contiunally prompts me to add it to the clipboard. Is there a way to copy directly to the clipboard then close the file down without this annoying warning message?