Posts by ryancgarrett

    I have attached an example spreadsheet of what I am trying to accomplish here. Basically all of the rows with a date prior to today will be actual values pulled in from an external database.


    In cells E2:M3 I have the different stages of our supply chain and how many days each stage takes. As units move through the supply chain they should stay at each stage for the number of days indicated in the table in E2:M3, with two exceptions:


    Stage 1 is the ordering stage. You can see my formula starting at cell F29. This formula checks if the day is divisible by the reorder frequency, and if so, checks to see if we go below the safety stock number and need to order more inventory. If the total stock falls below the safety stock number in the period being checked, and order will be placed for the reorder amount.


    Stage 8 is the other exception. This is our total stock available for sale. This number should check if any stock is available from completing stage 7, as well as subtract the unit sales from the previous day (Column O).


    The issue I am having is writing a formula that will repeat the unit number each day until the number of days in the supply chain length table has been reached, and then moving those units to the next stage. Any formula I write results in a circular reference in the formula for stage 1.


    Happy to provide any other details as needed.

    I have a workbook with around 100 sheets and would like to create a macro to copy certain sheets to a new workbook (all the sheets in the same workbook) and keep the sheet names and formatting but only leave the values in the sheets, not the formulas. I've searched a lot and found some helpful answers but have been unable to put it all together and have it work. Something along these lines:



    This code seems like it should work to copy all sheets to their own file (it actually errors out on the "ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value" line) but I can't figure out how to copy them all to one workbook. I'm sure its a simple fix that you experts will know in no time.


    Thanks,


    Ryan

    I've developed an Excel Add-In for my company that runs certain reports based on data in the spreadsheet. I'd like to have the reports created from a template rather than using VBA to format the sheets every time it is run, i.e. rather than adding headers, formulas etc, create new sheet from a custom template and import the data.


    As the template will just be part of the project, is it possible to store it within the add-in, or will I need to distribute the template to all users in order for the add-in to work properly?

    Re: $15 comparing two spreadsheets, 10% paid to Ozgrid


    Code works great, and very quickly. As you mentioned, the files are very large. I'm fairly new to programming and haven't dealt much with memory usage, but I think this may be cause by creating and deleting hundreds of sheets so often. Do these remain in the memory? What other measures can I take to reduce the size of the file?

    I need an answer pronto to this problem. I have two spreadsheets, last month's customer statements and this months customer statements. The current month doesn't have invoice descriptions, just dates and amounts. Each sheet houses a single customer statement and has the same name in both workbooks. I need to compare each sheet with the same name and if there is a line that has a matching date and amount, copy the invoice description to the current months line. I've written the following code, but with over 100 statements it takes hours to run on my machine, and doesn't work to boot!


    I have made a custom ribbon tab for an application I am developing. On the tab a have a toggle button that hides and unhides certain sheets in the workbook. It does this by checking if "pressed" is true or false and then calling either hideSheets or unhideSheets. I've run into a problem because there are other macros that call the unhideSheets procedure, but the toggle button remains pressed. How do I tell the toggle button to "unpress" whenever the unhideSheets procedure is called?

    Re: Rename copied sheet with ScreenUpdating turned off


    I figured it out. You were right to warn about only turning off screen updating once. I didn't realize it but when i called "hideRows" screen updating was being turned on again with each sheet. On another note, my hideRows sub causes the total time it takes the macro to run to go from a minute 30 seconds to 6 minutes... Is there a more efficient way to write this code?


    Re: Rename copied sheet with ScreenUpdating turned off


    Here is the full code:


    If I turn off screen updating in the beginning it doesn't work, I still watch as every sheet is added and then populated. Also, RoyUK, the error I got before changing the code to the above was an Excel error, not a VBA error. It would tell me that it could not create a sheet with the same name.

    Re: Rename copied sheet with ScreenUpdating turned off


    That worked perfectly, as far as naming the sheets correctly, now I'm having an issue with the screen updating. Even though it is set to false, I am running the code right now and watching each sheet be copied and populated. Any idea why that would happen?

    I have a large macro with the following code as a key component:


    Code
    Sheets("Blank Customer Statement").Copy after:=Sheets(prevCust)
    ActiveSheet.Name = formattedCustNum
    Sheets(formattedCustNum).Range("A76").Value = "'" & custNum.Value


    As it stands, the code works fine, however, if I set application.screenupdating = false then the code errors out, as the copied sheet doesn't become the active sheet. Is there a better way to handle this situation? The macro takes about 15 minutes to run, and I'd like to turn off screen updating to help speed things up a bit.

    I have an edit box on a custom ribbon tab where a user can input a sheet number and the following macro navigates to that sheet and hides all others



    The problem is if the user inputs a sheet name that doesn't exist, the error message appears just fine but the "Table of Contents" sheet gets hidden for some reason. Can anyone see why this happens?

    Re: Loop Until User Selects vbNo


    Works like a charm! Another conundrum on the same code however...


    Code
    While MsgBox("Any Volksbank receipts?", vbYesNo) = vbYes
            vksReceipts = vksReceipts & InputBox("Country?") & ", " & InputBox("Customer name?") _
            & " - " & FormatCurrency(InputBox("Amount? (Enter number only i.e. $100,000.00 as 100000"), 2) & vbCrLf
        Wend


    is the actual code I use. When I select yes it asks first for the amount, then for the country and customer name. It concatenates everything in the correct order, just asks the user in a weird way. Can anyone tell me why?

    I've written the following code to ask a user if there are any cash receipts to input:



    How do i cause this to keep repeating until the user selects no?

    Re: Macro to Format Raw Data from Accounting System


    Thanks for the reply and getting me started cytop. I've rewritten the code using your suggestions and have the following:



    The only part I still can't figure out is how to delete the filtered data. Also, if there are any improvements I am open to all suggestions. My VBA skills are improving, but still rudimentary at best.