Slicer Connections Lost When Copying To New Workbook
Jun 12, 2014
The main objective of the code is to create different workbooks that are going to be send to different people(with only their data).
I am using the filter to delete the data that I don't need and then create a new workbook, save it and close it. This has been working fine.
The issue is that I have two slicers in the 'DistMenu' sheet. When I open again the newest workbook, they slicers are disconnected to the pivot tables. This can be fix manually by selecting the slicers --> PivotTables Connections etc
I use the macro recording to get a code and work from there, but is giving me error '1004' "Unable to get the PivotTables property of the Worksheet class".
View 1 Replies
ADVERTISEMENT
Mar 28, 2014
I' have two slicers that each control two pivot tables. The slicers both are for the same field ("Department") but I guess the pivot table structure differences don't allow me to simply have one control all four tables.
Since both slicers contain the exact same options from the same field on the same origin table, I would like to problematically ensure that when department "A" is selected on slicer1, it is also selected on slicer2. When departments "A, B, & C" are selected on slicer1, the same are selected on slicer2. When filters are cleared... you get the picture.
View 5 Replies
View Related
Jun 2, 2014
My issue is we have a remote office, using an access DB which links to an Excel file. We sometimes send the files to other people to make changes, but the link is then broken (it was a network location).
You can just change it manually, or accept the many prompts that come up, but it needs doing at each end, each time.
I thought I'd do a routine to change the connection paths, this is what I have...
Code:
Sub alan()
Dim i As Long
Dim cnt As Long
Dim modtext As String, modfile As String
Dim modrange As String, OldPath As String, NewPath As String
OldPath = "\192.168.1.100MultimediaCommissioning Folder"
NewPath = "C:matts"
[Code] ...
I added the sourcedatafile part to the routine afterwards, because the connection string part worked fine. But I now get Run-time error '1004' Application-defined or object-defined error...
View 3 Replies
View Related
Mar 15, 2013
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
View 2 Replies
View Related
Oct 20, 2008
The following code deletes a specific connection in a workbook:-
ActiveWorkbook.Connections("text123").Delete
How can I code this so that I can delete ALL connections in a workbook without having to specify the names (as these will always be different).
View 3 Replies
View Related
May 23, 2013
I'm trying to use vba to create an offline backup file for a sheet that is linked to an access database. the code I have is simple and worked for a file that is not linked to any database.
Code:
Sheets("Data").Select
Sheets("Data").Copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myfilename
Application.DisplayAlerts = True
Now that I insert it into the actual file, which is linked to the database I get an error. I also tried to move the display alert up, before the select instructions, but also this does not work. this is not what I wanted since I want this file not to possibly connect to any database.
View 9 Replies
View Related
Apr 3, 2014
I have an Excel Workbook with Showcase Queries embedded into it.
Upon a manual open, the user is prompted to either refresh data or keep data since last save, the user then has to enter a username and password to allow the query to access the database.
Using the code below to open the workbook, no such prompts are given. Is the code overriding the prompts and automatically refreshing the data without password? [Given nature of data, visual validation of refresh is not possible]
If not, how can we code a data refresh through showcase query with user/pw req's?
[Code] ......
View 1 Replies
View Related
Jan 19, 2010
I have an excel program that uses a modeless userform. often times i will open other spreadsheets while the form is active or minimized. if i return to the form and run it without manually bringing focus to the appropriate workbook, i get an error because it references the wrong workbook. is there a way to keep that form tied to that workbook no matter what? also, it is important that this procedure works despite the workbook name changing.
View 5 Replies
View Related
Mar 31, 2008
Simple combobox query:
step 1. open excel, control toolbar, add new combobox ( named combobox1)
step 2. create new module, with the following
Sub test
With sheets(1).combobox1
.AddItem "123"
End With
End Sub
step 3. run macro "test", combobox1 should be populated, save and close file.
step 4. re-open file, drop down on combobox1, no values to select from.
View 9 Replies
View Related
Feb 11, 2013
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
View 13 Replies
View Related
May 30, 2014
Trying to create a new workbook from another open workbook, then copying all the sheets that aren't called "Summary" to that new open workbook and then saving it. I get a subscript error on this line:
[Code]....
View 3 Replies
View Related
Jun 16, 2014
All i did was i just created a form to open a workbook from the directories.
Code to copy the data from that recently opened workbook to my workbook where I have my forms. I need to copy that data as in the case that i don't know the workbook and the sheet name i'am going to open as i may open any of the files!!
View 2 Replies
View Related
May 16, 2014
I have created userform and it works fine. Following code assigned to 'SUBMIT' button in userform - works fine. I am trying to include code where certain data from userform is also copied to workbook2 ( of course without opening it)- as marked in red...below
[Code].....
View 4 Replies
View Related
May 27, 2014
Please see attached files.
Book1.xlsx
test.xlsm
I am trying to copy the emf image object from "Book1" into the "test" workbook whilst the test workbook is open and Book1 is closed. The code I have put together currently sort of works, I mean that it copies the text data over but does not copy the object which is what I require.
View 2 Replies
View Related
Mar 6, 2014
So I basically have a template workbook that the code is stored in. I need it to pull an entire row if Column C in workbook "rawdata" contains specific text, in this case "PRCH - Purchase".I have never had to do anything like this before, it has always been working in the same workbook. It's failing at the 'mp = ...' line every time. There might be other errors in the code too, I just cannot resolve the first one!
[Code] .....
View 5 Replies
View Related
Sep 18, 2006
i came up with this..
Sub Copy_To_New_Workbook()
Application. ScreenUpdating = False
Dim strFileName As String
strFileName = Application. GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="Please select the excel file to be copied")
View 9 Replies
View Related
Jun 7, 2006
I have created a macro that copys pivot table from one Workbook to new workbook. Problems I am encountering are print selecting from source workbook are not copied to new workbook.
Dim AmountofColumns As Long, AmountofRows As Long
Dim ColumnCount As Long, RowCount As Long
Dim ThisWorkbookName As String, ActiveWorkbookName As String, ActiveSheetName As String
Public Sub CreateGroups()
Application. ScreenUpdating = False
Sheets("Preferences").Select
ThisWorkbookName = ActiveWorkbook. Name
AmountofColumns = Range("A1").End(xlToRight).Column
For ColumnCount = 2 To AmountofColumns......................
View 6 Replies
View Related
Dec 3, 2013
Im using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
View 1 Replies
View Related
Nov 11, 2008
I am trying to set up a template for my MD to use to calculate budgets and have so far encountered a number of problems most of which i have overcome thanks to help from this forum.
What i am trying to do is have a 'Selection sheet' which contains a number of drop down boxes from which the user can choose several different options.
Based on what they have choosen on this sheet a second sheet 'initial budget' is populated.
What i am trying to do is a formula similar to the following. but i can't seem to get it to work.
View 15 Replies
View Related
May 14, 2009
I am trying to automate the process of filling out monthly reports and I have run into a problem I just cannot seem to work around.
These reports contain both weekly and monthly information, but because most months do not end on a friday or begin on a monday I have to have a couple of the week reports as partial weeks (e.g. April ended on a Thursday and so that week's report has both April 27-30 and May 1). When the first week of the next month's report runs I would like it to prompt the user to locate the last month's report, open the report, find week 5 of that report and copy the relevant data into the current month's report. (Hopefully that explanation is sufficient)
What I am running into is, after prompting for and opening the past month's report I am trying to select the Range A137:I232 on Sheet5 but instead the previous month's report just opens to wherever it was when it was last saved and won't select any ranges I tell it to, it just selects the range it was on last time it was saved. It will then copy that range and paste it into the Current Month's report.
below is my code ...
View 8 Replies
View Related
Aug 9, 2013
I have "wbThis" as the original workbook to be pasted to and "wbTarget" as the workbook that is being opened and copied from. (2 different workbooks). I have written a code for the user to chose the workbook (wbTarget) to open and copying the data from (the getopenfilename). I want to paste these values to the "wbThis" worksheet. The code is running, however it does not copy paste the values from one workbook to another, and it is not giving me an error either.
Code:
Private Sub CommandButton1_Click()
Dim wbTarget As Workbook
Dim wbThis As Workbook
[Code].....
View 2 Replies
View Related
Apr 10, 2014
I have two slicers, say slicer 'F' that slices on fields 'x', 'y' and 'z' and slicer 'E' that slices on fields 'a', 'b', 'c'.
I select these values by using a very simple macro. For instance to select z in slicer "F", I have the following code:
With ActiveWorkbook.SlicerCaches("F")
.SlicerItems("x").Selected = False
.SlicerItems("y").Selected = False
.SlicerItems("z").Selected = True
End With
However, I want the selected fields to have more flexibility than which is possibly by coding them in VBA. Ideally, I would want to write the selected fields in a cell in Excel (also if there are more of them!), then let the macro retrieve these fields and use them to apply to the slicer. So I write in a cell slice on X and Z, the macro picks this up and performs this operation.
Is this possible?
View 1 Replies
View Related
Jan 31, 2014
I have a list box contains of customer name and I used that to set up formula based on your selection in the list box. However, I wonder if there is a way that can select multiple customers in the list box and it will add up all that customers from your multiple selection like in the pivot slicer. I don't know if we have any formula that can do that complicated thing.
View 2 Replies
View Related
Jun 12, 2014
In my pivot table I have "Due Date" with is a column label.
I grouped the dates into weeks by using the "days" grouping and using 7 days.
I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.
Example:
6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014
I want the slicer to sort by actual date.
View 1 Replies
View Related
Feb 9, 2010
I've got a little program which uses webqueries to import data from anywhere between10 and 200 web pages. It's working pretty well, but I need a way to clear out the residual connections via the code.
The part of the macro which imports the data was simply recorded and very slightly modified. I've included it below... but what I'd like to do once the data has been imported is delete the connection.
View 3 Replies
View Related
Dec 31, 2013
I am currently working with a pivot table someone else created. The data set has dates only, in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.
Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.
How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
View 3 Replies
View Related
Jan 8, 2014
I need to transfer selection(s) from slicer Country to Country1 (they cannot be connected due to workbook functionality).
User can make a single or a multiple selection in the country slicer.
So basically using CUBERANKEDMEMBER I got the output from slicer Country (in cell A1 for now) and used it as input in VBA for slicer Country1
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]")
I have to use VisibleSlicerItemsList as it is an external data source (so cannot use ActiveWorkbook.SlicerCaches(Slicer Name).SlicerItems(Slicer Valuel).Selected = True/false)
Now, when user chooses 2,3 or more countries, they will be in cells A2, A3, A4... etc.
So, if the user selected 2 countries I would need to run a following code:
ActiveWorkbook.SlicerCaches("Slicer_Country1").VisibleSlicerItemsList = _
Array("[01_Feed].[Dosage].&[" & Range("A1"]", "[01_Feed].[Dosage].&[" & Range("A2"]")
Now, is there any way to loop this within the array, how many cells it should take?
For i = 1 to ..
code from above
Next i
won't work because then it only takes the last value, so I kind of have to loop it within the array.
View 2 Replies
View Related
Mar 7, 2007
I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.
first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?
second question: if so, is there a setting or something that can make excel more "passive"?
View 9 Replies
View Related
Dec 16, 2011
I have a workbook which uses data connections to our sage accounts product. Everytime i open the sheet i get the "Data Connections have been disabled" security message, and need to go in and enable them. Is there any way of enabling the data connections by default? I only want to do it for this one workbook - i still want to be prompted when i open other workbooks.
View 2 Replies
View Related
Jul 22, 2006
From within VBA I need to open a database connection and issue a select and store the results in my spreadsheet. The issue is that I only JUST learned that you can do this and I dont know where I can look for instructions. Incase it matters, I am trying to connect to a Sybase DB. I have used perl, c# and a little bit of Java to do this but never from with in Basic (let alone basic within excel!)
View 9 Replies
View Related
