Tags: created, excel, files, function, macro, macros, microsoft, msdn, pivot, prepare, record, run, software, table

Run Macro from different files

On Microsoft » Microsoft Excel

4,127 words with 2 Comments; publish: Fri, 23 May 2008 00:22:00 GMT; (30646.88, « »)

Hi,

I have created few macros to prepare Pivot Table through Record Macro

function.

I want that these macros I should able to run every month in different

files. Name of the files will be different.

What can I do to make these macros perform every month for different files.

Cheers,

Mandeep

All Comments

Leave a comment...

  • 2 Comments
    • Hi ManDeep

      Save your routines in a personnal macros workbook (PERSONNAL.XLS) and place

      this workbook in the xlStart folder on your computer.

      Or: just start recording a new dummy macro and from the record dialog box

      select to record it into a personnal macros workbook, validate and then stop

      recording.

      You will then be able to copy your macros from the original workbook into

      the newly created workbook which will only be visible from the VB Editor.

      HTH

      Cordially

      Pascal

      "Mandeep Dhami" <MandeepDhami.excel.todaysummary.com.discussions.microsoft.com> a écrit dans le

      message de news: 951F2A2A-418E-45AD-B7E5-4772D756A33B.excel.todaysummary.com.microsoft.com...

      > Hi,

      > I have created few macros to prepare Pivot Table through Record Macro

      > function.

      > I want that these macros I should able to run every month in different

      > files. Name of the files will be different.

      > What can I do to make these macros perform every month for different

      > files.

      > Cheers,

      > Mandeep

      #1; Fri, 23 May 2008 00:24:00 GMT
    • Hi Papou,

      Thanks for your reply. Sorry to say but I am not able to get your solution.

      Following is the script of my recorded macro would be able to make necessary

      amendment in the script so that this macro can be used to get pivot table

      from other files as well:

      Sub Pivot_Table_1()

      '

      ' Pivot_Table_1 Macro

      ' Macro recorded 1/9/2008 by Mandeep

      '

      ' Keyboard Shortcut: Ctrl+q

      '

      Range("A3").Select

      Range(Selection, Selection.End(xlToRight)).Select

      Range(Selection, Selection.End(xlDown)).Select

      Range(Selection, Selection.End(xlDown)).Select

      ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

      "'Register'!R3C1:R65536C52").CreatePivotTable _

      TableDestination:="", TableName:="PivotTable1", DefaultVersion:= _

      xlPivotTableVersion10

      ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)

      ActiveSheet.Cells(3, 1).Select

      ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Dist Line

      Type" _

      , ColumnFields:="Invoice Source"

      ActiveSheet.PivotTables("PivotTable1").PivotFields("Dist Line Type"). _

      Orientation = xlDataField

      ActiveWorkbook.ShowPivotTableFieldList = True

      ActiveWorkbook.ShowPivotTableFieldList = False

      Application.CommandBars("PivotTable").Visible = False

      With ActiveSheet.PivotTables("PivotTable1").PivotFields("Dist Line Type")

      .PivotItems("(blank)").Visible = False

      End With

      End Sub

      "papou" wrote:

      > Hi ManDeep

      > Save your routines in a personnal macros workbook (PERSONNAL.XLS) and place

      > this workbook in the xlStart folder on your computer.

      > Or: just start recording a new dummy macro and from the record dialog box

      > select to record it into a personnal macros workbook, validate and then stop

      > recording.

      > You will then be able to copy your macros from the original workbook into

      > the newly created workbook which will only be visible from the VB Editor.

      > HTH

      > Cordially

      > Pascal

      >

      > "Mandeep Dhami" <MandeepDhami.excel.todaysummary.com.discussions.microsoft.com> a écrit dans le

      > message de news: 951F2A2A-418E-45AD-B7E5-4772D756A33B.excel.todaysummary.com.microsoft.com...

      > > Hi,

      > >

      > > I have created few macros to prepare Pivot Table through Record Macro

      > > function.

      > >

      > > I want that these macros I should able to run every month in different

      > > files. Name of the files will be different.

      > >

      > > What can I do to make these macros perform every month for different

      > > files.

      > >

      > > Cheers,

      > > Mandeep

      >

      >

      #2; Fri, 23 May 2008 00:25:00 GMT