The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
Question 1: how i can automatically add a cell into a formula by just clicking the cell to be added? in other words, say i typed an "=" sign in a cell and want to add a number of cells just by clicking the mouse without having to type the "+" sign after selecting each cell. the formula will just appear as > =A1+B1+C1 by default with each click of the mouse. Is there a default setting that can be set so the plus sign will appear with each click?
Question 2: how can i set a comma to appear for all thousands, (i.e. 1,000) without having to format each cell individually to show commas?? in other words that any spreadsheet i open will always insert comas for numbers larger by a thousand?
I've inherited a workbook that has about two dozen or so pivots spread over a few worksheets that source data from an analysis services cube.
There are multiple slicers attached to the various pivot tables and charts
Some of the reports (worksheets) have a business rule that certain filters need to be selected in specific combinations for the data that's returned to make sense.
eg the fact table holding the measures has a billmonth and processmonth that's linked to role playing date dimensions. For the report to reconcile the data correctly both the billmonth and processmonth need to be set to the same value. So if I select 2014-03 on the billmonth slicer, I need to set the processmonth slicer to 2014-03 as well. And there's another pivot on the same worksheet that's linked to a different fact table that's at the year grain, and for that bit to make sense it should be set to 2014.
My task is to simplify this by propagating the billmonth value to the processmonth and the billyear, but I haven't played with vba in about 7 or so years, so I'm very out of practise.
How do I use vba to monitor a slicer for changes? and if it does change how to set another slicer to a dynamic .Value? It can be assumed that the value will always exist in the downstream slicers, if it doesn't the user has bigger problems than an excel error.
I've recorded the macro of me selecting the same date on both slicers but it doesn't give me much to go on
I have a macro that will email a link of the worksheet.
the problem is I may have different email addresses to send it to. I want to be able to have an input box come up, and the person to enter in an email address, click ok and have the macro continue to email.
Sub Make_Outlook_Mail_With_File_Link() 'Working in Office 2000-2010 Dim OutApp As Object Dim OutMail As Object Dim StrBody As String If ActiveWorkbook.Path "" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0)
I wish to use a formula to grab data out of cells A1 & B1 and use that data to complete a hyperlink URL in cell A3. The base URL never changes but the last two variables do. For instance:
Currently using Excel 2010 and looking for a macro that will export a selection (will be part of a column that I select) and export as an MS-DOS CSV file (no quotes around values, each row on different line) with 12 commas after each value in the selection.
Attached is an example of how I would like the output to appear, with the selected values only have four numbers in them. If possible, I would like a dialog box to appear for saving the file to a directory and name that I chose.
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
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
I have a question on how I could populate data using a combo box selection in Excel 2010.
For example, I have a table with values in Sheet 1, & below that table there is a combo box whereby another table of values can be populated based on the selection of the combo box.
Maybe to make it clearer...
Table 1 Name | Address | Phone number Andy | Avenue 2 | 999 John | Road 5 | 998
Combo box (selection of names): John
Data derived from combo box - Table 2 Name | Address | Phone number John | Road 5 | 998
how I could solve this Also, do let me know if this can be done without the use of VBA.
As part of a program I'm writing in VBA (for Excel 2010), I have a textbox in a user form used as an interface to write a formula in cells in Excel.The resulting value of a formula from a cell is loaded up into the textbox. It would be shown in the textbox like "See 1.2 and 1.3" where the formula in the cell is
[Code].....
This is just used as an example but the principle is there. It is worth noting that I’m writing this for very inexperienced Excel users but I need them to be able to edit the string part of the formula without breaking the formula.
However where I’m struggling is to pick up a selection change event inside a text box already selected. I need to be able to check if the textbox.SelStart is within an address value or within the string in the textbox.
The event Enter won’t work if the user is already editing the textbox (i.e. typing stuff) and then clicking or using the keyboard arrow to move the cursor somewhere else inside the textbox. I don’t think the event Change is the solution either as it would mean that the user would have already typed something and as a result the formula may already be broken.
I have had a good look around and I didn’t find an event for a selection change inside a textbox. Does it exists and/or is there a way that would have the same result?
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.
I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.
Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)
What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.
Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).
The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...
Can I make it so that it will insert Pre-Specified information into both sheets at the same time?
I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...
So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...
Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...
I'm using Excel 2010 on windows 7.
Code: Sub UnHide_NewRoutings() If (Engineering.ComboBox2.value) = "0" Then Engineering.Label4.Visible = False Engineering.TextBox5.Visible = False Engineering.Label9.Visible = False Engineering.TextBox9.Visible = False
I'm creating a basic form or templateusing Excel 2010 where the user will fill out information (See attached screenshot) I'd like to incorporate two a check boxes, that when ticked, change the display of the second table, either adding, removing or splitting of of the current columns in to two?
Is this, or any variation of, possible using Excel 2010.
I am working in excel 2010. I have a tracking document that lists free tickets and their expiry dates. In the adjacent columns we track redemption details of these free tickets. What I want to do is return the oldest expiration date from A only if the ticket has not been used (i.e. B is empty). This will allow me to see the date the upcoming tickets about to expire so we can make sure they are used.
A________________B Expiry Date________Redeemed by 15/08/2014 15/02/2014 15/08/2014________John 15/02/2010________Marc 15/02/2011________Bob
Here is an example of what I am trying to do in E2013.
The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.
Also - is there an easier way to insert sheets other than a screen grab and photos?
I have a column of dates in Col H with associated values in Col I. I need to specify a date range in Cell I1 and I2 such that the row number for the first encounter of the first date is placed in Cell I3 and the last encounter of the end date is placed in Cell I4. For example, with this data when I specificy 4/2/13 and 4/3/13, I would like to get a 19 in Cell I3 and a 53 in Cell I4.
Matty supplied the formula in Col K for each of these cells. They worked well in the application that I supplied earlier (different locations for these variables), but my real application is as shown here, and these two formula give the incorrect results shown. Both of these formula are arrays.
Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.
Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell
I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.
For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.
Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.
I am trying to insert a Pivot table with dynamic Name Ranges. It needs to start from Cell B1 as Column A has hidden formulas in them. I created an Dynamic Name range and tried to insert a pivot table. Excel then throws out a error stating "Data source reference is not valid". I tried re-saving the document but still no luck.
I am relatively new to VBA and am trying to convert a range of data to a table in the same sheet. I receive the following message when I try to run the code as shown below:
"The worksheet for the table data must be the same sheet as the table being created." The code stops on the third line of the code.
Sub UseChangePassword() Dim wksOne As Worksheet Set wksOne = Application.ActiveSheet ' Protect the worksheet. wksOne.Protect ' Establish a range that can allow edits ' on the protected worksheet. wksOne.Protection.AllowEditRanges.Add _ Title:="Classified", _ Range:=Range("A1:A4"), _ Password:="secret" MsgBox "Cells A1 to A4 can be edited on the protected worksheet." ' Change the password. wksOne.Protection.AllowEditRanges(1).ChangePassword _ Password:="moresecret" MsgBox "The password for these cells has been changed." End Sub
The red color code is giving error message: Application defined or Object defined error.
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
I have to report Actual Headcount every month. Since I cannot add the Headcount numbers in the FY Column. What is the best possible forumula I can use to pick the last month's reported numbers into the FY Column.
For example.
I have 13 columns (A - M). Jan - Dec and the 13th column is the FY number.
This month, I am reporting March numbers in Column C
Column D through N (Apr through Dec) are emplty
I need Column N (FY number) to read Column C and enter the value from that cell.
similarly when I report April numbers in Column D next month, I want Column N to read April numbers and ignore the prior months.
I tried using the min/max function but since h/c fluctuate every month I could not use that. I could use an "if" statement and go back every column but that seems to be inefficient way to solving for this. Any thoughts?
I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
I've gotten the desired result in Column B, but this will not work going forward as we add to the table in columns E:G .
I'm looking to search between columns E:F, Identify the date-range where my dates in column A belong, and pull the corresponding rate from column G into column B.
Using Office 2010, I am trying to do a averageifs formula for a specified date range. I can get it working by specifying the date range in the formula itself, however when "pointing" the formula to a specific cell with a date in it, the formula gives me a div/0 error.
Formula that works is: =AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"08/29/2012")
What I need is a version of: (currently not working) =AVERAGEIFS('UHP Weld Data'!M:M,'UHP Weld Data'!B:B,"H1")
The date range will constantly change as I want it to show me the past 30 days only.
