Excel 2007 :: VBA Code Determine Where To Paste Based Upon 2 Cells In Different Workbooks Being Equal
Feb 24, 2014
Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.
Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).
What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".
The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).
In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.
Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?
If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........
how to make VBA code work in all workbooks. I created a new module in my PERSONAL.xls file, and added the code, but the code does not work when I open a new workbook. Using Excel 2007.
I am using Excel 2007, and here is my scenario. I have a spreadsheet with a different sheet for each month of the year. I want certain cells in a month's sheet to mirror or be equal to the same cell in the previous sheet so that if I change the value of a cell in April, the same cell will automatically be changed in May.
An example is that I have a cell with the value of "Comcast". If I want to change that value in April to "Directv", I want it to automatically change in May, too.
Now I know that I can individually program a cell to be equal to a cell in another sheet and it will do exactly what I want. The problem is that I have about 60 cells in each sheet that I want to mirror the same cells in the previous sheet, and I don't want the entire sheet to mirror the previous one. So with 11 months/sheets with cells mirroring the previous sheet, individually programming 660 cells and switching between sheets to do that would take a very long time.
So my question is this. Is there any way to select multiple cells and have them all mirror the same cells on a different sheet without my having to program each cell individually?
Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.
The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.
Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.
Example data below, I've simplified it (the actual data array spans from columns A to Y)
A1IDS09C1_PEND_COB2COPY YES SHRLEVEL CHANGE SORTKEYS SORTDEVT SYSDA3BMCSTATS YES UPDATEDB2STATS YES UNLDDN RU0014CONDEXEC YES UNLOAD RELOAD REDEFINE NO DELETEFILES YES SORTNUM 125DDTYPE WORK ACTIVE YES DDTYPE SORTWORK ACTIVE YES6DDTYPE LOCPFCPY ACTIVE YES7DSNPAT
[Code] .......
In other words, I need to copy the text in blue so that it is above the text in black and the text in red so that it is below. Then I need the macro to repeat this procedure for each cell that it finds containing text in black. I have already created and run a macro to insert blank lines between each black text entry.
Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
(Excel 2007) The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked. I can't attach anything, so I'm including a link to a detailed screen shot with some notes on it and code that I'll host on my site. Not sure how else to explain the issue, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code
Here are the links to the screen shot and text file of my code Text file Screen Shot
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.
I'm creating a configuration tool for instrumentation that has several options. Many of these options are dependent on one another. To summarize, I need to:
*Conditionally lock cells (and populate with "N/A") OR allow selection from drop down list - the list exists, it's the locking and auto-population I'm struggling with
Example: If A1 = No, then B1 = "N/A" and is locked, else allow selection from drop down list in B1
Two support staff added data to the same base document. I now have two files that I need to combine into one. Is there a way to merge the two together so that I have one workbook that contains all the information entered by both users? We use Excel 2007.
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.
I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
Had to rebuild my PC's hard disk and reinstall software. Now, whenever I open another workbook via explorer double click, a new instance of Excel starts. If I open another workbook using Excel's menu open, then the workbook opens in the same instance. I've tried a 'Repair' of Office and resetting the default programs to Excel for all Excel related file extension types.
I and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
I have a client with approximately 4-5000 Excel workbook files that all contain the old Jet connection string and these files do not run under Office 2010 without changing to the newer ACE connection string. It is just too much to open each file and change the connection string.
Is there a way to do a mass change - like global search and replace - the connection strings in the files? I have a text editor that is able to do that but I fear it may mess up the file.
So, We are using Excel 2002 & Excel 2007 and for some reason all of the workbooks that we have open decide to randomly change the fill colours of nearly all the cells. The only reason this is an issue is that a lot of my work envolves me sending reports to clients and from a business perspective this does not look professional at all.
To add to this, once the cells have changed colour, the original colour is no longer available to select.
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example: workbook 06-01-2011 workbook 06-02-2011 all the way to workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do: ='C:File Path[WorkbookX.xls]Sheet1'!A1 where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1 where D1 is the full path name or ='C:File Path[Workbook"D1".xls]Sheet1'!A1 where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total?
I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors
I started a new job and my new company uses Excel 2010 (64-Bit). The code below was written for use in Excel 2007. I am trying to open an XML file and simply paste the results in a spreadsheet but I am getting this error, "Method 'OpenXML' of object 'Workbooks' failed" at the "set oWX" line in the code.
Sub GetNetTIEDAH() Dim idate As Integer Dim strThisBook As String
I am looking for macro where it can generate multiple workbooks with a single worksheet data source. I have this worksheet with data which is look like this:
[IMG][/IMG]
This data will be places in 1 workbook for each of line with given file name as page number above those workbooks generated will have same information for each line but at different cell position such as: take this example at line 3
and
Microsoft Excel 2007 Window 7Pro 64bit
This line could be up to 50 lines of data. I try google search but found most about consolidate multiple workbooks to single worksheet. I do have VBA reference that I refer to from [URL] ......
How to put the information from the worksheet into specific position in the workbooks.
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
I have 4 workbooks, all of them contain data that is managed by other teams at my company. I need to take all of this data, organize, and concatenate it into one sheet for myself, that I will then reference in other workbooks to various OTHER departments in our company that need the data for certain projects. I have the organization laid out in a lot of very complex formulas so that most of this is automated, and not manual.
I need a solution that can pull data from the 4 workbooks without locking them for use, to allow me to keep my document open all the time, and they can update theirs at their leisure. Everything works fine, until I hit the refresh button. Once this is hit, all of the linked documents are locked and cannot be opened, even in "Read Only" mode. Once I close the master document, the files are free to be opened. I know a workaround for now is that i can not "refresh" the document, but rather close and re open it for changes.
Some steps I have already tried :
1. Create an intermediary file that no one uses, and can be locked all day without a problem. This doesn't work for me since the intermediary file needs to be open to refresh, which locks the original source doc, leaving me in the same place as before.
2.Modify the connection string to display "Mode=Read;" instead of "Mode=Share Deny Read"
3.Create a new connection with "Read" only selected in the Advanced Tab
I need some different columns within a row to calculate based on a statement found in an earlier column in the row. something similar to below. there will only be two statements total which are noted on the table below.
A B C D E
1 0 0 20 5%
[Code].....
So basically If column A = disposition calculate D and E in column B but if Column A = Receipt calculate in column C
The formula for D and E is not exactly as shown just using that as a quick example. I'm using office 2010.
I'm trying to set an array of types equal to a set of cells in excel, first I've defined an ORoom with attributes such as ( ORID, ORName,...), then ORooms whish is a set of ORs, from OR1 to OR 30.
Now I want to put ORooms equal to the whole range for them containing all information for all ORs, I'm trying this code:
Public Type ORoom ORID As Integer ORName As String Cases As Integer
[Code] ........
But it gives me the error Invalid outside Procedure, referring to "A2:A31", I've checked but that the correct range for it.
