Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
V = ActiveCell.Value

[Code] ........

View 1 Replies


Excel 2010 :: Pivot Table Change Event

Mar 22, 2012

I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three Pivot Tables

Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3

When I use In Sheet 2 the following lines of code

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";

How to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.

View 3 Replies View Related

Excel 2010 :: Change Pivot Table Source With VBA?

Nov 25, 2013

I am using Excel 2010 and have a workbook with two sheets - "Risks & Issues" which contains the source data and "Risks - Summary" which contains a Pivot Table called "Dashboard"

I would like to create a command button called 'Refresh Data' which will be on the "Risks - Summary" sheet. When I add a new line to the "Risks & Issues" sheet, I would click the button in "Risks - Summary" and it will update the Pivot Table range.

So far I've tried using some examples found on this site, although with no experience in VBA macros, I'm not really sure what they do; all I know is that they cause an error.


Sub Refresh_Click()
Sheets("Risks - Summary").PivotTables("Dashboard").SourceData = Sheets("Risks & Issues").Range("A2").CurrentRegion.Address(True, True, xlR1C1, True)
End Sub


View 1 Replies View Related

Excel 2010 :: Change Spacing Between Dates In Pivot Table?

Mar 27, 2013

I have a pivot table with dates along the x-axis. The data is spread out over 5 years and the spacing between sampling events is not even, therefore, the spacing of my x-axis dates is not even. I want the x-axis to look similar to a normal graph where there is equal space between each month, even if I don't have data for each month. I do not want to group the data to accomplish this. If I click the "show items with no data" it only shows one extra day, not all days or even all months. I have excel 2010.

View 2 Replies View Related

Excel 2010 :: Adding Percentage Change Column To Pivot Table?

Mar 17, 2012

I am trying to add columns to my Excel 2010 Pivot Table to calculate % change of sales from year to year. The field name is "Year", while the item columns in the field are 2007, 2008, 2009, 2010, and 2011. How do I add a column between the years that will calculate the % change os sales up or down from the previous year?

View 2 Replies View Related

Excel 2010 :: Change Style For Certain Number Of Lines In Word Doc From VBA

Apr 29, 2014

i have done in Word VBA successfully (basically, change the Style for a certain number of lines):

Dim myRange As Range
Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)


View 2 Replies View Related

Excel 2010 :: Automatically Change Text Entries To Set Number ID / VBA?

Jul 24, 2014

Basically I first want to be able define around 100 variables such as:

Dog = 1
Cat = 2
Mouse = 3

And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.

So typing in


would automatically result in


In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?

View 1 Replies View Related

Excel 2010 :: Enter Formula Into A Cell That Is Pre-formatted To Accounting?

Jul 3, 2014

I am having a terrible time with Excel today. The version I'm using is Excel 2010.

I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.

The simple division is =13588/12

The output in accounting format should be 1,132.33.

Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.

View 7 Replies View Related

Excel 2010 :: Removing Comma From A Cell Formatted As General

Mar 10, 2013

I have a set of data that has a company name using commas to separate INC and LLC from the company name. e.g. Acme Explosives, LLC

I'm using Excel 2010, and when I try to use the find/replace functionality to find commas, I get an error message saying "We couldn't find what you were looking for. Click Options for more ways to search.

Short of editing all the fields manually (only about 300, so not too bad), I'm hoping there is a simple way to replace these commas with nothing.

View 9 Replies View Related

Count Number Of Loops Passed Through And Display On Excel Status Bar

Jun 8, 2010

Is there a way to count the times a loop has occurred and be able to present a running total in the status bar of MS Excel?

Sub Master_Phone()
' Select cell A2, *first line of data*.


View 4 Replies View Related

Excel 2010 :: Columns That Are Dependent On Each Other Without Circular Reference?

Aug 7, 2014

I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.

View 2 Replies View Related

Excel 2010 :: Getting Error When Try To Add Dependent Dropdown List

Oct 29, 2013

Creating a spreadsheet in Excel 2010 and am creating the dropdowns from a separate sheet in the workbook. There are no spaces and I don't know why I keep getting the "The Source currently evaluates to an error."

I am entering the Data Validation to reference the first cell I need "=INDIRECT(H3)"

I have put the spreadsheet on dropbox. When you select the "Sequencing Platform" drop down, it need to give just the dropdown for the platform selected.

[Code] ..........

View 2 Replies View Related

Excel 2010 :: How To Make Concatenate To Generate A Text String Using Custom Formatted Value Of A Cell

May 2, 2014

I have an Excel 2010 spreadsheet that I am using to save several numbers all in the same column. These numbers can range from the several thousands up to billions. The formatting I am using for these numbers is Number (using 1000s separator).


I am also using the spreadsheet to generate a text string for each of the numbers. I don't want the text string to show the number as it is, I want to shorten the number by only showing the first few digits followed by a "B" for billion, "M" for million, or "K" for thousand. For example, in the text string I want to show 1,600,000 as 1.6B.

In order to shorten the number I use the cell in the column to the right of each number. This cell uses the following custom formatting (which I found by doing a Google search): [>999999999.999]#.0,,,"B";[>999999.999]#,,"M";#,"K";


As you can see the formatting is quite complex (at least for me it is) but it does what I want it to do.

Here is a screenshot of what the original and custom formatted cells look like:


As you can see the custom formatting works and does exactly what I wan

The next column (after the custom formatted number) is where I put the generated text string for each of the numbers. As I stated above, I want the generated text to use the shortened version of the number (e.g. 1.6B).

To generate the text I use the CONCATENATE function with a reference to the cell containing the shortened number as one of the arguments. For example:


Where B1 is the custom formatted cell.

The problem I am running into is, the text that gets generated doesn't show the shortened format of the number, it shows the full number. Here is a screenshot demonstrating what is happening:


As you can see the generated text is "SOME STRING 1600000000". This is not what I want. I want the generated text to be "SOME STRING 1.6B".

I think I understand what's going on. When the CONCATENATE function references a cell it takes the actual value of the cell and ignores any formatting. (I suppose formatting is just the way you see the data, not how underlying functions receive the data.)

My question is, how can I re-write the CONCATENATE function (or use another function, etc. available to me) to use the formatted version of the cell?

*UPDATE* I have attached my spreadsheet as an attachment to this post (tackyjan_excelforums.xlsx). Please note that it was created and saved using Excel 2010.

View 8 Replies View Related

Excel 2010 :: Change Default Pie Chart Colors In Recorded Macro?

Mar 20, 2014

I am working on a project where I am virtually almost finished except for a minor change with the pie chart. I am analyzing some data and recorded a macro to do this and also the pie chart for visualization. However, I do not like the color of the default pie chart colors and would like to customize it. How could I change this within the macro I have recorded?

[Code] .....

View 4 Replies View Related

Excel 2010 :: Macro Set To Refresh Just One Pivot Table But Is Refreshing All?

May 19, 2014

I have some code that runs and loops through each pivot table to refresh it; however, I remmed out the code so that it only loops once - telling it to refresh the first pivot; the thing I'm confused about is that after the code runs, it appears to have refreshed all my pivots. I think it's also important to note the each pivot table is linked to the same data source. (an external ds)

Maybe when this is the case, refresh will always refresh all?

View 5 Replies View Related

Excel 2010 :: Macro To Return A Table Of Active Filters

May 29, 2013

I've got an old Excel sheet with Stephen Bullen's function for returning the active filter criteria (Rob on Programming: Excel: Displaying Autofilter Criteria). My status sheet may be filtered in multiple ways, and when the user is happy with the filter selections, she can create a powerpoint file with a graph and a summary of the filtered table. As we are using Excel 2010, users are very likely to select more than two filters.

Example: Range A1:E100 has the following headers: Field, Installation, Project,Type, Phase.

The controller wants to filter on:
Field equals north or south or west Phase equals completed
The manager for Field South wants to filter on:
Field equals southType equals maintenance or repair or modification Installation begins with Zeus.

As the filters are not shown when I copy the table to powerpoint, I would like to create a summary of the user's active filters that is pasted into a sheet (for subsequent copying to powerpoint). For the users in the example above, that table would look something like this:

Active filters
Field: north, south, west
Phase: completed
Manager, Field South:
Active filters
Field: south
Type: maintenance, repair, modification
Installation: Zeus*

I've looked at various functions intended to take Stephen Bullen's code into Excel 2010's multiple criteria world (e.g. this: User Defined Function to Display AutoFilter Criteria for More Than Two Criteria in Excel 2007 / Excel 2010), but I have not been able to convert it to a functioning macro.

Any code that could be used for this sort of task, or any tips for relevant code?

View 2 Replies View Related

Excel 2010 :: Comparing Cells And Displaying Text Dependent On The Results?

Aug 15, 2012

I have attached a work book example of what i am trying to do.

Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.

I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.

Is there anything i can do to change this?

View 3 Replies View Related

Excel 2010 :: Calculating Value And Posting Total To 1 Of 4 Fields Dependent On Content Of Another

Jan 9, 2014

Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.

proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).

F2 = 150
G2 = Q2
H2 = 10%

I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.

View 4 Replies View Related

Excel 2010 :: Macro To Select Latest Date In Dropdown Box Of Pivot Table?

May 9, 2013

I am trying to create a macro using the recorder that will select the latest date in a drop down list. The dates upate each day in the pivot table after the table is earlier refreshed. Using the recorder simply picks the same date each time. I want it to select the latest date. Am using Excel 2010 and have some very limited experience in vba for editing macros.

View 1 Replies View Related

Excel 2010 :: Macro To Select Rolling / Latest 6 Weeks In Pivot Table

Nov 13, 2013

I have a raw data in one file and then another file contains pivot table linked to the raw data. In the dashboard i need to show last 6 weeks trend which is linked to the pivot table. Currently i am manually unselecting previous week and selecting the latest week.

Ex: This week i select weeks 5, 6, 7, 8, 9, 10 from the pivot for the dashboard. When new data is updated next week, i want to select 6, 7, 8, 9, 10, 11. I cannot delete as i need the historical data.

I am using Microsoft Excel 2010.

View 1 Replies View Related

Excel 2010 :: Macro Design For Serial Number Input

Dec 8, 2011

Excel 2010 O/S XP Macro for serial number input all serial numbers have JTC0. Any VBA code to place in macro to have a cursor appear after the four characters in the macro below?

Example: JTC0| < cursor ready for input

Sub Macro2()
' Macro2 Macro
' Keyboard Shortcut: Ctrl+s
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "JTC0"
End Sub

View 4 Replies View Related

Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table

Dec 2, 2013

I am using Windows 7 and Excel 2010.

Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?

I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)

Example: Project ID
TCP Level
[Code] ......

Here is the table:

I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green

Earned Value Limits





View 2 Replies View Related

Excel 2010 :: How To Save Excel File As Cell Reference Using Macro

Mar 5, 2012

I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)

Sub aaa()
' aaa Macro


View 1 Replies View Related

Excel 2010 :: Macro For Replacing Text In HTM Document - Text To Change Different Every Time

Mar 18, 2014

I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.

An example:
My htm document is located at C:/ and named XX.htm

The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx

And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:


View 8 Replies View Related

Adjust Macro From Excel 2003 To Excel 2010?

Apr 1, 2014

I am trying to adjust the below macro so that it will work in Excel 2010.

Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer


View 3 Replies View Related

Excel 2010 :: How To Auto Update PowerPoint Table When New Data Entered Into Table

Jun 12, 2013

I have created a table in Excel 2010 (pls see attached table named post.xlsx).

Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).


I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.

Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".

Specific Question:

The newly-typed column in Excel table is not get updated in PowerPoint table.

View 2 Replies View Related

Excel 2010 :: Create Sheet With Table From Pivot Table?

Apr 26, 2012

Recently, the boss showed me a Pivot table & chart, which consists of a list of about 30 user names in the first column. The row headings were the different items they purchased from a vending machine. & when he clicked on any name in the first column, this created a new sheet, renamed with users name, with a small table of results showing what that person purchased.

Problem is, none of us can figure out how to do this. I have created a new Pivot table & chart exactly like the original, but I cannot get the smaller sheet to generate. (Excel 2010)

View 3 Replies View Related

Macro To Change Table Properties In Outlook 2010

Jun 16, 2013

The macro im using copys a table from excel into a email. Unfortunatly the email is not readable for blackberry users due to the table properties.

I need to change a few properies. This is what i do manually.....

In outlook i then hover over the table right click then select "Distribute rows evenly" then i right click again but this time i select "Table properties" i then go to the "Row" Tab

i then untick the "allow row to break across pages"

then tick "specify height" and change the value to 0.4 cm and finally change "row height is" to "exactly"

Below is the whole code im using so far....

Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object


View 2 Replies View Related

Excel 2010 :: Convert Text To Number And Format Number Without 2 Decimal Places?

Oct 23, 2011

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.


View 4 Replies View Related

Excel 2010 :: Getting Outlook Warning (2010) While Sending Email Through Macro

Mar 25, 2014

I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved