Posts by SaraO

    Re: Incrementing a value from another workbook

    I am trying to use this code for a number set that does not contain the "-" and I get a run time error highlighting this line in the code...

    l = w.Sheets(sheet).Range(ref).Value + 1

    Any idea on how to fix that?


    I am trying to extract data from a closed workbook meeting specific criteria.

    This is what I have...

    A Workbook called "InvoiceList.xlsx", that stores a list of invoice that have been created by multiple sales staff. This workbook needs to stay closed so when a new invoice is created it can store the info in there.

    Each row contains information as following...

    A = Invoice Number in the this format Inv-170152
    B = Date invoice was created
    C = Sales Staff that created the invoice
    D = Customer's name
    E = Before tax amount
    F = With tax amount
    G = Job number
    H = Quote number

    What I need...

    Each sales staff needs to have his/her own list of invoices they created. I have created new workbooks for each sales staff where I would like to extract their invoice list into.

    I need a code that would look for "John Bergen" in column C and extract all the data from that row A - H and paste it in "InvoicesJohn.xlsm" in the next empty row. A1:H1 are column headers so the pasting needs to start at A2.


    I have been asked to create a spreadsheet for a fundraiser softball tournament. I have most of it figured out but I'm having trouble with transferring numbers and team ranking.

    The way this spreadsheet works...

    Info Sheet: Here is where the team names, game dates and times are entered to feed the rest of the sheets.

    Game Schedule: I have created dropdown lists to select the teams, dates and times for each regular games being played.

    Game Results: Here is where the teams runs get entered and it automatically calculates the differentials.

    Team Wins: The wins and differentials for each game transfer to this page and adds up the wins and differentials.

    ** On this page I have not yet figured out how to transfer the wins and differentials from the Game Results page**

    Team Rankings: This is where I want to see what position each team is in after each games and what teams will make it to the semifinals and finals.

    **This is where I'm having trouble with the team ranking**

    I have been struggling with this for months already and can't seem to get it all working correctly. My deadline has come and long gone :(

    I know very little about softball tournaments so if anyone sees room for improving this, please let me know.

    Thank you!


    I have a code that saves a copy of a template in several files. Its work really well but it doesn't keep the margin setting on the saved document. When we have to go in and reprint that document we always have to reset the margins or it will print on multiple pages.

    How can this code be modified to save margins as they are set in the template?

    Re: Modify code to transfer data to template

    I got it figured out!!

    For some odd reason this is working...

    addme.Offset(0, 1) = Me.lstConstructionRetail.List(X, 1)
             addme.Offset(0, 2) = Me.lstConstructionRetail.List(X, 2)
             addme.Offset(0, 7) = Me.lstConstructionRetail.List(X, 3)

    Thank you so much for your help! I really appreciate the time you took for me.

    Re: Modify code to transfer data to template

    That is giving me a runtime error "Could not get list property. Invalid argument". I changed my list to 5 columns to see if that would work and the error doesn't come up but still doesn't put the price in column I.

    Re: Modify code to transfer data to template

    Oh ooppss that second 2 should not be there. I have been playing with the code for a couple days now and must have changed that at some point.

    I did go back into my template and corrected that and it still does not put the last item in the list box in column I.

    I'm trying to write to columns B, C (C:H Merged) and I. The I column is where I'm having trouble.

    (Column A is where the quantity is typed in)
    Column B = Rev Code (First item in the list box)
    Column C (C:H Merged) = Item Description (Second item in the list box)
    Column I = Item Price (Fourth item in the list box)

    The third item in the list box is the supplier and is there for sales staff info only and does not need to appear on the Invoice.

    Hope that makes it a bit clearer.

    Thank you!


    I have a code that I have been using for some time now. I have to make some changes which requires adding another column to the template but I am having trouble getting the data to transfer to the correct cells.

    The code I have is as following:

    I have populated the list box with a dynamic name range list which is 4 columns wide but only 3 of those are needed on this particular template. One column is for information purpose only and is not needed on the template.

    If I use this code on a test sheet without merged cells it works just like I need it to. But my template has merged cells where the second column of the name range is going. It places the first item from the list in the second column on the template, second item in the third (Merged) column and fails to place the last item on the list in the fourth column.

    I have attached a screenshot of the template, Hope that helps.

    Thank you in advance for your help.

    Re: User Form adding, searching, editing and deleting data in a different book

    I want to store the address in 2 lines.

    Now they are stored as:

    B - C - D

    First & Last Name - Box 1415 Morden MB R6M 1B3 - 204-822-8745

    This is how I need them to be stored:

    B - C - D - E

    First & Last Name - Box 1415 - Morden MB R6M 1B3 - 204-822-8745

    I need to add another text box in the userform to separate the address as shown above.

    Hope this makes sense :)

    Re: User Form adding, searching, editing and deleting data in a different book

    Thank you so much!!! You are my hero!! I will give it a test run on the network with multiple users and see how it holds up.

    When I first started with this, the idea was to have the work orders automated the best I could. But as time and progress went on the bosses kept asking to add this n that and this is where I am at now. After a couple years of searching for the "Perfect" software to run the company they just gave up. I have been learning everything as I go and as I'm learning more I realize there could be better ways to do this. But at this point I have spent a crazy amount of hours on this the bosses would not be impressed if I started all over.

    I really appreciate all the help I have gotten in this forum, without that help I would not have been able to accomplish this. Thanks to all those that have helped me I got a raise!!!