Rewrite recorded Macro for Pivot Table update

    Hi Guys, i am new here as well as in setting up macros in excel vba .

    I have recorded the MACRO for my massive excel file pivot tables. i have recorded 3 macros for 3 work sheets ( please guide if i can set one macro for all 3 sheets, that excel work book got 6 sheet)

    I need help to record this in a better way as my source file changes every day in a perticular folder and i have to change the name in macro every day ...is there anyway i can automise this ?

    I have to activate ( open ) the source file to run the macro ..is there any way i can get result when file is closed and have results in new excel work book ?

    I am really sorry if i sound bit confuse here ...thanks in advance whoever can help...

    Gaurang Patel
    PLEASE SEE THE DETAILS BELOW for 3 MACROS which i've created

    Re: Rewrite recorded Macro for Pivot Table update


    Gaurangpatel,

    Firstly, Macro help is not an acceptable thread title at Ozgrid. Thread titles should use Search Friendly Key Words only and give a concise description of the problem. They should allow another member to find this thread when searching for a solution to a similar problem.

    Secondly, all VBA code must be wrapped with [noparse]

    Code
    Your code here

    [/noparse] tags. I have added them for you this time.

    Please take note of these comments as further infractions may affect your posting rights.

    Thank you.

    Re: Rewrite recorded Macro for Pivot Table update


    That code is too dense to try and figure out, so I can't comment on your request to combine them so that you only have to call one macro.

    I also don't know how these are used. Is it just you running them? If so, you could move all the code into your Personal.XLS so the macros are available any time. The other change you could make is to replace the line I quoted earlier with something that enables you to either pick an open worksheet, or a File Open dialogue to browse for a workbook. The code would then either select (first choice) or open that work book. As it will be the active workbook, the remaining code should run without having to make any other changes.

    But it is very difficult to give a definite answer based on what you have posted or said so far...

    Re: Rewrite recorded Macro for Pivot Table update


    this is just simple macro i am recording , it will have other set up as well.
    basically i am trying to save my time from this massive pivot table and get the standard formatted reports while running the differnet macros so strightaway i can start analysis on them.

    thanks for your help so far... :)

    Re: Rewrite recorded Macro for Pivot Table update


    That last post did not explain anything.

    I'd suggest you move the macros into a personal.xls. If you don't know what or where this is, have a read of this.

    Then change the macros to something like...



    That is not ideal code, and not even the best way of doing it, but it might give you a pointer as to how your can begin to rationalise your code...

    Re: Rewrite recorded Macro for Pivot Table update


    Thanks for you reply...
    I will try later and let you know what is the progress...really appriciated all your support.

    Re: Rewrite recorded Macro for Pivot Table update


    Hi there, I have write the code with one of my friend and your guidence

    code:

    Dim UpdateFile As String
    Dim aDate1 As String
    Dim aDate2 As String
    Sub pivOrders()
    '
    ' Macro1 Macro
    ' SOrts the Pivot table to show orders
    '
    Workbooks("Target Stock Pivot Control.xlsm").Activate
    Call UpdateVariables
    aDate1 = 0
    aDate2 = 0

    Dim f, aFile
    Set f = temp.GetFolder(mrTargetStockDirectory)
    For Each aFile In f.Files
    If Left(FileDateTime(aFile), 10) = Format(Now(), "DD/MM/YYYY") Then UpdateFile = aFile
    Next

    Sheets(1).Range("A1") = UpdateFile

    Workbooks.Open Filename:=UpdateFile
    On Error Resume Next
    Windows(UpdateFile).Activate


    Sheets("ORDERS CREATED PIVOT TABLE").Activate

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
    .Orientation = xlPageField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Style code")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Colour-Fit")
    .Orientation = xlRowField
    .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Total qty"), "Sum of Total qty", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Style code").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)....................

    now the issue is i cant run the macro while someone else opened the file already , can you please advise what changes should be made to get the macro running even the file is in read only.

    thanks
    Gaurang
    ( sorry I have to paste the code here as i cant find the code box below)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login