Results 1 to 11 of 11

Thread: VBA in Excel 2016 - Update graphs then copy sheets

  1. #1

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    VBA in Excel 2016 - Update graphs then copy sheets

    Just wondering if anyone can help

    need to automate an excel sheet which will run a macro to update then copy the sheet to end

    have manually done but don't even know where to start automating it

    highlighted the 6 new tabs I wish to make



    only think is i need to change the pivot table before i create each sheet?

    how can i do this ?
    Attached Files Attached Files
    Last edited by Robbo; Mar 10th, 2021 at 05:02 AM.
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    to create the 6 worksheets with correct names
    Code:
    t = " 24h"
    i = 0
    For j = 1 To 6
        If j = 4 Then t = " 4w": i = 3
        Set sh = Sheets.Add(, Sheets.Count)
        sh.Name = "Jumbo " & j - i & t
    Next
    but more information would be required as to the contents of sheets
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    Quote Originally Posted by westconn1 View Post
    to create the 6 worksheets with correct names
    Code:
    t = " 24h"
    i = 0
    For j = 1 To 6
        If j = 4 Then t = " 4w": i = 3
        Set sh = Sheets.Add(, Sheets.Count)
        sh.Name = "Jumbo " & j - i & t
    Next
    but more information would be required as to the contents of sheets
    Hiya basically I need to change the pivot table then save the sheet

    Any idea how I can do this ?
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    does the recorded macro process one particular sheet of the 6?

    how much of the process is common to all the 6 sheets and what parts are the difference?

    the code in the macro can be changed to be more concise, but it is difficult to follow what it is actually doing or required
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    Quote Originally Posted by westconn1 View Post
    does the recorded macro process one particular sheet of the 6?

    how much of the process is common to all the 6 sheets and what parts are the difference?

    the code in the macro can be changed to be more concise, but it is difficult to follow what it is actually doing or required
    Hiya there’s one macro which updates the original sheet but not the copy sheets

    Open the zip file and look at the update macro without copy and pasting this 6 times need it to generate the 6 sheets with correct data cheers
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    i was looking at the update macro when i posted the above questions

    i was sure there must be differences for each of the sheets
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    Quote Originally Posted by westconn1 View Post
    i was looking at the update macro when i posted the above questions

    i was sure there must be differences for each of the sheets
    Yeah the slicers are removed but there’s hidden columns someone before me did the sheet just didn’t want to have to completely re do
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  8. #8

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    Quote Originally Posted by westconn1 View Post
    i was looking at the update macro when i posted the above questions

    i was sure there must be differences for each of the sheets
    How to use for Daily reports:-

    1 Select `Name`.. Jumbo 1.
    2 Select `Date`.. today’s date example - 11/03/2021
    3 Press `Update Graphs`.
    4 Press `Print`.
    5 Select `Name`.. Jumbo 2.
    6 Repeat steps 3 & 4
    7 Select `Name`.. Jumbo 3.
    8 Repeat steps 3 & 4

    For 4 Weekly reports:-

    1 Select `Name`.. Jumbo 1.
    2 Select `Date/s`.. From today’s date example 11/03/2021 to 11/02/2021 (4 week)
    3 Press `Update Graphs`.
    4 Press `Print`.
    5 Select `Name`.. Jumbo 2.
    6 Repeat steps 3 & 4
    7 Select `Name`.. Jumbo 3.
    8 Repeat steps 3 & 4

    just wanted the print to make a new sheet, if possible
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    i am struggling a bit with this, the below code should select a specific name (Jumbo 02), but sometimes it works and sometimes not

    Code:
    Set s = ThisWorkbook.SlicerCaches("slicer_name")
    ju = "Jumbo 02"
    For k = 1 To s.SlicerItems.Count - 1
        s.SlicerItems(k).Selected = False
    Next
    s.SlicerItems(ju).Selected = True
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Frenzied Member Robbo's Avatar
    Join Date
    Jan 2001
    Location
    Bradford
    Posts
    1,143

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    Quote Originally Posted by westconn1 View Post
    i am struggling a bit with this, the below code should select a specific name (Jumbo 02), but sometimes it works and sometimes not

    Code:
    Set s = ThisWorkbook.SlicerCaches("slicer_name")
    ju = "Jumbo 02"
    For k = 1 To s.SlicerItems.Count - 1
        s.SlicerItems(k).Selected = False
    Next
    s.SlicerItems(ju).Selected = True
    you may be on to something

    so i can select a slicer and set in VBA i then would have to force the update graphs and then do a copy to sheet.

    need to do the for all 3 jumbos but also need to select dates on the other slicer, but that's defo helped
    -----------------------------------------------
    "The hall is rented,"
    "the orchestra is engaged,"
    "its now time to see if you can dance!"
    Q, Q-Who, Star Trek The Next Generation
    -----------------------------------------------
    General Work day

    -----------------------------------------------
    DOS, Win 95, Win 98 SE, Win ME, Win NT 4.0 SP6a, Windows 2000 SP3, Window XP SP1, Windows 7, Windows 8/8.1, Windows 10, Office 97 Pro, Office 2000 Pro, Office 2010, Office 2013, Office 2016, Office 2019, Visual Basic 6 (SP5), SQL, Oracle

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA in Excel 2016 - Update graphs then copy sheets

    that is the idea, but selecting an item in the name slicer seems to be unreliable, i am sure that would also apply to the date slicer, but i have not tested with it

    i tried with multiple variations of the above code, all seemed to be problematic
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width