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 ?
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
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
Re: VBA in Excel 2016 - Update graphs then copy sheets
Originally Posted by westconn1
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
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
Re: VBA in Excel 2016 - Update graphs then copy sheets
Originally Posted by westconn1
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
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
Re: VBA in Excel 2016 - Update graphs then copy sheets
Originally Posted by westconn1
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
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
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
Re: VBA in Excel 2016 - Update graphs then copy sheets
Originally Posted by westconn1
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
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