I need to have multiple (up to 6) pivot tables on the spreadsheet with shared external data source. As a data source i have a csv file. Also, i need to be able to load data file on demand. I have created a button on the sheet that would load File dialog and let user select whatever csv data file they need. Then the idea was to update pivot cache connection and command properties using VBA with new file name. Here is the code :
Dim pc As PivotCache
Set pc = ActiveWorkbook.PivotCaches(1)
Dim ODBC_CONNECT_STRING As String
' FullFilename is a new absolute path to selected csv file
ODBC_CONNECT_STRING = "ODBC;DBQ=" & FullFilename & ";" & _
"DefaultDir=" & FullFilename & ";" & _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;" & _
"SafeTransactions=0;Threads=3;UserCommitSync=Yes;".................
I have a Workbook with multiple PivotTables which are feed from an external data cube. I need to update all of this Pivotables to the same date through VBA. The date will be a known variable and it corresponds to the "Periodo" field. I had thought of doing this by having the date stored in a cell of maybe with a calendar pop-up
I'm trying to update a pivot table filter with a list on another worksheet. When I do a record and select two WBS for filter criteria I get the following:
If I entered values 700UY0S1MGL1 & 700UY0S1MGL2 in cells A1 and A2 of Sheet2 in the same workbook, how can I get this code to read that? If I extend the list to include additional items, how can I get the code to read it?
My next question is, in Excel I could enter a partial search (like 700UY0S1MG) in the filter drop down and I could filter on everything with this string. Using the macro it seems as though the full 12 char string needs to be qualified. How would I write a macro to handle this?
I have a spreadsheet pulling data through from Access (in a Pivot Table format), but Excel inserts the string "(blank)" for empty fields. How can I stop this, and have Excel leave them truly blank?
I have created a post on TeachExcel.com, but haven't received an answer yet: http://www.teachexcel.com/forum/view...7d85cfb7d#p203. I've attached an example of the spreadsheet.
I need to write code to automatically update a user's add-in if a newer version is available. Here are the specifics. The add-in is installed on multiple users c:drives. In the add-in code, there is a constant, Version_Nbr. On the network drive, I have a text file containing the most up-to- date version number. When the user opens the add-in file, the code compares the Version_Nbr to the text file on the network drive. If the version on the network is higher, I prompt the user with "A new version is available. Would you like to download it now?"
I have a seperate download program which will automatically download the new add-in to the user's local drive. Here's where I run into problems. I cannot replace the add-in file with a newer version when the add-in is open. The add-in is always open because it's the add-in that determines if a newer version is available. So how do I do this? It seems to me I need to launch the download program from the add-in and then have the add-in close itself before the download program executes.
On a weekly basis I record payment terms updates and do this process manually. I compare 2008 terms against 2009 terms for any status update. I would like to provide a button to do the work for me. Can anyone provide code please?
I currently use this "IF Statement" =IF(C2=D2,"No Change",IF(C2D2,"Reduced")))
I have a time sheet which is used by around 15 people. Part of the timesheet is a userform which adds a new sheet and names it with the seleted month and year. I have made a few changes to my timesheet which I wish to update on other peoples sheets. I am going to send out a speadsheet with a macro that people can run and it will automatically make the changes to their timesheet. I have done all the work for updating the various formats and formuals but I have hit a bit of a brick wall when trying to change the code on a user form via a macro. It would be great if I could either overwrite all of it or add a some lines of code from a specific line number.
I'm trying to write a VBA code to automatically update using a vlookup but I seem to be running into trouble, partically with the lookup value part of my vlookup. I basically have a range of dates in column B and want the values to appear in column C. Yes, I know I just type the forumla in column C and drag it down, but I really need to do it in VBA, as I want this to update when additional dates are added.
This is what I have thus far. The code keeps looping at i = i +1 and doesn't stop unless interuppted.
Sub update() Dim i As Integer, L() As Range, s As Integer, V As Double Sheets(1).Activate s = Range("D:D").Select On Error Resume Next While s "" i = i + 1 ReDim Preserve L(i To 1) L(i) = s Wend If i = 0 Then MsgBox "No dates found" End If Exit Sub
For i = 1 To UBound(L) V = WorksheetFunction.VLookup(s, Range("Inputs"), 2, 0) Next i End Sub
I have created a pivotchart and placed it on (say) sheet " GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".
Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.
However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day?) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.
The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.
However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the target. name, its blank!
I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:- dim pt as pivottable : set pt = ......... : pt.refreshtable etc select the sheet before hand etc, etc but no joy.
Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.
I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
msgbox Target.name <---------ERROR (Blank!)
If Target.PivotFields("BusinessArea").CurrentPage = "XXXXXX" And _ (Target.PivotFields("BM").CurrentPage = "AAA" Or _ Target.PivotFields("BM").CurrentPage = "BBB" Or _ Target.PivotFields("BM").CurrentPage = "CCC" Or _ Target.PivotFields("BM").CurrentPage = "DDD") Then MsgBox "Error - " & Target.PivotFields("BM").CurrentPage & " is not a valid Business Manager of " & UCase(Target.PivotFields("BusinessArea").CurrentPage) & vbCr & vbCr & "Please try again!!", vbCritical, "Error" Exit Sub End If
..graph reformat code (irelevant because even If i delete this it still bombs out!" End Sub
One of the several ws is a form; when the user saves it, all other ws are deleted and the wb is saved as a new file name. (Need to retain other macros for future functionality, seems easiest to just delete the unneeded sheets).
Is their a way to modify the sheet reference from "menu" to "sheet-name" (will be a constant). Because it is likely that additional sheets will be added in the future to this new wb, I'd like for it to open to a specific ws.
As my VBA skills are "in development" (basement level, just now), please provide a complete answer (assuming this can be done).
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
I have a 15 excel files which are part of the forecast system. There is another file MASTER which is the host file for the macro. Every months multiple users are asked to update their forecast in the 15 excel files .As these files also display actuals, so they are linked to bunch of other files. The links to the files should not be updated unless I run my macro from MASTER to do the series of tasks.
Everytime these users open the files , they are asked to update links.
Is there a way for me to stop this warning and still able to update the links only when i run my macro from the Master.
I can easily use VBA to update a column's next cell with a value from a static cell. As easy as this may be for most, I seem to be struggling!
For example, I would like cell B3 (then B4, B5, B6 etc) to update, according to a value (time frame in seconds) in cell C3, with a value from the cell D3.
The below is a data sheet which is going to be designed to keep the records of "sending and receiving" details of wash-garments from a garment factory.
Descriptions;
01. Starting from B12, the dates of sending and receiving are entered in the sequence they occur. Both sending and receiving can occur on the same date.
02. Starting from C12, there are reference codes related to the activity, if Sending the letter is entered as "S", and if receiving it is entered as "R". I included this for the calculation or sorting purpose.
03. Columns D to J includes the break down of sizes of the quantities sent or received.
04. column K simply calculates the totals of the columns from D to J.
Requirement : I need to have the totals of each size wise quantities sent and received in the "summary table" as follows;
>> Size wise totals of "sent qtys" to be shown in D5 to J5(referring to the code "S") >> Size wise totals of "received qtys" to be shown in D6 to J6(referring to the code "R") >> The dates to be updated and displayed"automatically" in the rows of the column B, when the code letters("S" or "R") is entered in the column "C"
Special Remarks : The last date of sending or receiving cannot be predefined, the rows(dates)will be kept adding according to the way sending and receiving may occur.
I have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets. In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row. I guess something could be done in macros.
follwing is the existing Sub TestIt() Dim sWS As Worksheet Dim Sellers As Range, Seller As Range Dim lRow As Long, fRow As Integer Dim CopyRng As Range, ws As Worksheet
I have a function that is called upon saving a UserForm. One of the Text Labels contains the ID field that I need to use to do a lookup into the first column of a Named Range and then I need to update the cell in the 4th column of that row.
From my research, I'm assuming it will go something like this:
I am trying to link what is in the header and footer with information contained in certain Cells. (I eventually want to have a user form to help input the information, but one thing at a time.)
How do I go about getting the following information into code so that I can update the enclosed code?
Left Header: Client Name contained in Sheet: "Table of Contents" B:1
I have a sheet with names and subdividers (Ent, Ver) on the columns, and dates on the rows. I am trying to make a form update the sheet when the name, ent or ver, and date match. I have attached a copy of my sheet to help explain that. I don't know much vba, but I can make a userform just fine. Trouble with most places is that the userforms only update based on empty cells, not based on more complex criteria.
I used VBA to write a number of formulas into various cells in a workbook. After the VBA is done running, I change the values in the cells that are referenced by the cells I wrote formulas in. The formulas do not update after I enter this new information. My calculation is set to automatic, and F9 doesn't help.
The only change I made was to change the "Sheet1" to "Journal" to match the worksheet name in my workbook.
As you close and reopen the workbook, the timer should start with =NOW() in A1 (formatted as "HH:MM:SS") and count up with the current time until you close the workbook.
I use Excel 2010, could that be the problem, that I copied a VBA code for an older version of Excel???
Question: What exactly is a regular module, do I use Module 1 for the first portion of the code or place it in ThisWorkbook?
I've got a big file with a large dataset. One of the sheets works as a screening for the data, so everytime I populate an ID number in say cell C3, the rest of the sheet updates as per the formulas I have in place. However, I need to write a code that takes ID numbers one by one from a sheet, populates them into my screening sheet, updates the sheet and saves it.
I have some existing code which I did not write, nor do I have the expertise to update. The macro code takes multi rows of data for one person and "flips" the output to be one record per person with columns of data.
The source data looks like this sorted by teacher, Term and Period (columns H and I are the new ones added; the code currently looks through column G only)A
Faculty Name B Periodc C
[Code]......
I just want to add column H and I data to the period course listings by term. So instead of "Term: TM#", just have the display indicate Termcode: Tally/SectSize.... such as (example): "TM1: 20/30"
My Code is as follows:
Option Explicit Dim wsSource As Worksheet Dim wsTarget As Worksheet
I have a price list from my supplier with the new prices.
I then have my Accounting software where I need to update the cost and retail prices. The problem I have is the Accounting software has allocated it`s own Unique ID for each item. So in order for me to bulk import this I need to keep this unique ID with the Actual Product ID together otherwise it will duplicate the product.
Here is a example
Sheet to be updated (Cost and Retail only) from PRICE LIST SHEET Uneque ID SKU
I needed a code that would input the current date and time in the cells in column N whenever changes were made to any cells in the row from columns A to M. For example, if I change a name in cell 6D, then cell 6N would automatically change to the current date and time.
I found a useful code on a forum (maybe here, don't know for certain) and modified it to suit my needs (see below). I am however now getting a debugging error suggesting that the second line that reads "Private Sub Worksheet_Calculate()" is causing an error.
c)Also there are many formulas in the workbook that needs to be updated as well.
if i do it manualy i takes more time.......for opening and refreshing and i could see status bar running for excel calculation for updating all forumulas.
