Posts by michibahn

    Re: Auto copy column formula to the new row


    Hi EggCel, I was putting new entries in the table and it seems that you are right. It'll just refresh everything even though the last SUBTOTAL row is still there. I hope this is ok for the users who will use the table...


    I don't know how to thank you again for this. THANK YOU THANK YOU! :)

    Re: Auto copy column formula to the new row


    Hi EggCel, yes I used the latest code posted but it did not delete the SUBTOTAL in row 60 after I refreshed the table using my MS Access query update.


    By the way how did you test it using my sample worksheet? Did you import a table from an external data using an MS Access query? Thanks!

    Re: Auto copy column formula to the new row


    Hi EggCel, I tested the code but for some reason the SUBTOTAL in row 60 (which I think is always the SUBTOTAL before the TOTAL) is still not removed after a table refresh. The way the query overwrites the table, is just deleting the SUBTOTAL rows but this SUBTOTAL row above the TOTAL is not removed.


    I guess it conflicts with the TOTAL row?

    Re: Auto copy column formula to the new row


    Hi EggCel, just want to let you know that the code is working in 2003 :) All calculations are correct :) at the first run.


    Meanwhile, there is a slight issue if an update from the MS Access query or a new unique part number is inserted in the table, the table will be refreshed since it imports a new table from the MS access query. The SUBTOTAL rows disappears except for the last SUBTOTAL row above the TOTAL.


    Please see the attached output after an MS access query import. 2 new rows are added in rows 36-37 as you can see the SUBTOTAL in row 60 didn't disappear so if i run the SUBTOTAL query again, the SUBTOTAL in row 60 doubles up.


    TIA!

    Re: Auto copy column formula to the new row


    Hi EggCel, thank you so much for spending time to create the code. I tried it here in my 2010 and it works falwlessly! :)


    I'll try it out tomorrow in my 2003 Excel workstation. Will let you know the updates.


    Thanks again! :)

    Re: Auto copy column formula to the new row


    Hi Eggcel, hold on.. I think the SUBTOTAL past 58 issue is still there. I tried it with a new unique entry in row 58 before running the code and the output is not correct. Please see the attached file.


    The one highlighted in YELLOW shoul have its own SUBTOTAL and the SUBTOTAL of the new row 63 is not correct.


    Apprecaite if you can take a look at it.


    TIA!

    Re: Auto copy column formula to the new row


    Hi EggCel, the lastest code without formatting works in 2003. But still it does have problems in row 58 onwards and needs to correct the formula in the TOTAL row as per my last post.


    In row 58 onwards it just gives a BLANK output on Column H and if a new row is inserted anywhere after row 58 there are no SUBTOTAL row. Also, can be the TOTAL value be just the SUM of the SUBTOTALs? because right now it just gets the SUM of the entire column including the SUBTOTALs which gives the wrong value.


    Thanks for your help!

    Re: Auto copy column formula to the new row


    Hi Eggcel.. I tried running the code using Excel 2007 and it executed without any errors. However, in row 58 onwards it just gives a BLANK output on Column H and if a new row is inserted anywhere after row 58 there are no SUBTOTAL row. Also, can be the TOTAL value be just the SUM of the SUBTOTALs? because right now it just gets the SUM of the entire column including the SUBTOTALs which gives the wrong value.


    My problem now is all users here are running Excel 2003 and it seems the code doesn't work in this version :(

    Re: Auto copy column formula to the new row


    Yes I'm using 2003. When I tried to put a ' in the line, there is still an error and the next 2 lines are now highlighted

    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.349986266670736
    .PatternTintAndShade = 0


    so when I finally commented these 3 lines, the code is not working at all. When I ran it my excel hangs and all I get is the hourglass :(

    Re: Auto copy column formula to the new row


    Hi EggCel, there are no SUB-TOTALs yet. I just did that Book1.xls sample to show what is the expected final report.


    Attached is the original output from our previous code (the before sheet) that does auto copy of the formula from above or below row.


    Here are the BEFORE (REPORT.xls) and AFTER.xls sheets just to be clear.


    TIA

    Re: Auto copy column formula to the new row


    Hi EggCel, there will be new unique part numbers added as the table goes on and also more items are added in the same exisiting part number rows. These new entries/itmes will be coming from an MS Access query which is triggered in Excel using the Import External Data option.


    I mean the auto-copy formula code you made is working fine using the same way the entries are made. Its just that they want to groupe them now and create a SUB-TOTAL for each similar entries :( I thought I was done yesterday :(


    Thanks so much EggCel!

    Re: Create an Excel macro to run an MS Access macro


    hi Rory,


    I'm using the code posted by Smallman.


    Code
    Sub Call_Access()    Dim ADB As Object
        Application.DisplayAlerts = True
        Set ADB = CreateObject("Access.Application")
        ADB.Visible = True
        ADB.OpenCurrentDatabase ("C:\db1.mdb")
        ADB.DoCmd.RunMacro "Macro"
    End Sub


    The macro works in MS Access without any errors. The macro is also working without any errors as a scheduled task.