+ Reply to Thread
Results 1 to 8 of 8

Recording a Macro to be used on multiple spreadshets within work book

  1. #1
    Registered User
    Join Date
    08-18-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Smile Recording a Macro to be used on multiple spreadshets within work book

    Hi new to this site so hello all!!

    Not used Macros before but I wanted to set one up so that it can perform the same function on different tabs in the same spreadsheet.It comes back with Error run time 5.

    Any help appreciated. Thanks guys.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi, sds33,

    do you mind sharing the macro code with us?

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    08-18-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi Holger

    Sorry for being thick , do you mean where it says

    Run time error 5
    Invalid procedure call or argument

    Other than that I am that new I do not know what a macro code is.

    Thanks

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi, sds33,

    what did you do prior to getting the error message (as it will hardly appear oin itīs own ). Without knowing what action you have taken itīs hard for me to get a clue on why the error did show up.

    Maybe explain in words what you have done - may we can figure out why the run-time error shows up.

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    08-18-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Recording a Macro to be used on multiple spreadshets within work book

    This may help : The below is the Macro when I press debug


    Sub Stevepivot()
    '
    ' Stevepivot Macro
    ' Produce pivot tables for payment run
    '

    '
    Range("W3").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "26th - 31st Aug!R1C1:R303C18", Version:=xlPivotTableVersion10). _
    CreatePivotTable TableDestination:="26th - 31st Aug!R3C23", TableName:= _
    "PivotTable3", DefaultVersion:=xlPivotTableVersion10
    Sheets("26th - 31st Aug").Select
    Cells(3, 23).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Supplier Code")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
    "PivotTable3").PivotFields("Outstanding"), "Sum of Outstanding", xlSum
    Range("AA4").Select
    ActiveWorkbook.Worksheets("26th - 31st Aug").PivotTables("PivotTable3"). _
    PivotCache.CreatePivotTable TableDestination:="26th - 31st Aug!R4C27", _
    TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion10
    Sheets("26th - 31st Aug").Select
    Cells(4, 27).Select
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Element")
    .Orientation = xlRowField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Supplier Code")
    .Orientation = xlRowField
    .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
    "PivotTable4").PivotFields("Outstanding"), "Sum of Outstanding", xlSum
    Range("AD5").Select
    End Sub

  6. #6
    Registered User
    Join Date
    08-18-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi Holger

    Just saw your reply after I posted that. What I am trying to do is :

    Produce a Pivot table that will give a summary for our suppliers by by Group and also by individual departs. So I downloaded the invoicce details for the group.

    Then set up several tabs representing when the invoice was due to be paid ( basically only including a weeks date range per tab ). I went to the first tab that representedd invoices that were due to be paid on the tab 26th to 31st Aug . I recorded the MAcro as I did it. I then went into the second tab 1st Sept to &th Sept and presssed run Macro. then that message popped up.

    Hope that helps.


    PS you may know an easier way of selecting the date range rather than putting it on different tabs - any guidance gratefully received.

    Thanks

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi, sds33,

    you would need to wrap the procedure with code-tags as requested by Forum Rule #3 before we may continue to work on the solution for your problem (Tablename does exist when the macro wants to create another one by the same name).

    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    08-18-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Recording a Macro to be used on multiple spreadshets within work book

    Hi Holger

    Sorry because of my inexperience I am not sure what you mean, I had a quick look at the Forum rule #3 which says but must admit I did not follow.

    My wife is about to comandeer the computer so will check in later and have another look. Thanks very much for your reply ....I will get to the bottom of this .

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Easy way to manage multiple Sheets on a work book
    By cfaristiz in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 01-31-2013, 12:12 AM
  2. Create PDF files from multiple work sheets in a single work book
    By erprasannaa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-29-2012, 03:42 AM
  3. Replies: 2
    Last Post: 06-20-2012, 05:10 AM
  4. how to get a macro to work with any work book name
    By Wade LEES in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-24-2009, 06:18 AM
  5. Macro doesn't work but recording it does.
    By Rob in forum Excel General
    Replies: 4
    Last Post: 05-24-2005, 06:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1