I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
| ID Y | +------+------+------+---> | | 1999 | 2000 | 2001 | +------+------+------+------+---> | Jan | $100 | $250 | $300 | +------+------+------+------+---> | Feb | $200 | $300 | $200 | +------+------+------+------+---> | Mar | $300 | $250 | $100 | +------+------+------+------+---> | Sum | $600 | $800 | $600 | | +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria: >= First Day of the Month <= Last Day of the Month = ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I'm working on an assignment that pulls information for different countries and I'd like to know how to go about placing the charts on a specific area. Any help would be greatly appreciated!
With each click of a country, the country's information will be pulled from a 'data dump' that I created in the same file. The map and flag are also shown. Some of that information is just using lookups, or a combination of lookups and code, and the hardest part are the GRAPHS - which I'm having a really hard time placing and adjusting accordingly.
As of now the charts are automatically filled as the user clicks a new country, but all of the charts are placed in weird locations, but I'd like for them to be placed in the specified ranges above.
I need to create a "purchasing programme sheet from extracted data in a different workbook.
In short I need to open the Allocation, Add stock qauntities to the the boxes with a quantity in it and if its a zero i need (Once the button has been pressed) the information to be extracted to the corresponding boxes in the Purchasing Programme Document: Description, Part Number and quantity.
now I thought about recording the macro, but it's an offset and if you click the chart, then it'll mvoe somewhere else even though the charts are always placed in the middle when generated. So I think I need to place it in a specific range. I want to use XY coordinates or range labels or something to that effect but would like something to this affect:
With Charts.Add .ChartWizard source:=Worksheets("sheet1").Range("a1:a20"), _ gallery:=xlLine, title:="February Data" End With
I got that in VBA help, but I'm not using Chart wizard, my code just creates the chart from the data on its own with specific chart type. So anyway, how would I make it put each of my five charts for the 25 people in the same place for every person. Performance Errors etc etc etc all will go in the right place everytime, regardless if you start with the page scrolled. I should do range right, but how do I change this macro to do it without chart wizard...just a chart that's already created and named. QUESTION: How do I place charts that are created from a data source onto a specific area of one worksheet, and those charts are always in the same place regardless of the person'a name.
find a way to place a value of 1 (or a text "email sent: mm/dd/yy") in a specific cell on each row when an email is sent out through some VBA code I currently have. Then when the workbook is opened on another day, the code will look to see if that cell (target) is populated so that it doesn't trigger a second, third (repetitive) email every time the workbook is opened.
The spreadsheet contains rows of many clients, and growing every week. The code needs to be designed to search through each row, along a specific column (lets suppose column R beginning at row 7). When it finds the target rows blank, the code will turn each target cell red, as well as trigger a message box alerting that follow-up action is required.
I'm trying to copy a worksheet and place it at the end. I will rename it afterwards. I'm using the following code but it doesn't work.
Private Sub Copy_sheet() Dim wbk As Workbook Dim wsh As Worksheet Set wbk = ActiveWorkbook wbk.Worksheets("Template").copy After:=wbk.Worksheets(wbk.Worksheets.Count) Set wsh = wbk.Worksheets(wbk.Worksheets.Count) wsh.Name = "New Sheet" End Sub
I need to copy the sheet being worked on, and place it behind the original sheet.
This is going to be in a Macro enabled template that other users will be rename when they save it.
It will be activated by a button on the original worksheet other users may need to rename the worksheet before copying so I am using ActiveSheet.Copy I don't want to put the sheet after a counted sheet, because other sheets may be inserted before the one being worked on. I am not proficient at VB, I basically search for a macro that does what I need and copy it.
This is what I am working on.
Sub CopySSR() ' ' CopySSR Macro ' ' ActiveSheet.Copy After:=Sheets(7) End Sub
I have is 3 seperate jobs listed, what i want to do is have each one of these rows copy over to a different sheet, and place the next available row. What I would like to drive this is the number in column "n", for example, "n3" is 1, so I would like that row to copy to the sheet named "1", and entered in the next available row under any previous lines that have been copied over.
I have come up the code below. It imports contact information from Outlook contacts in a contact folder called Private Contacts. The below code has been copied and modified from this forum.
As it stands the macro creates a new workbook and places the data on that. What I need it to do is create a new worksheet with the name of private compare on the active workbook and then place the data on that worksheet.
What I basically have is 3 columns of data, A,B,C. Every two rows are a matched pair (e.g row 2&3, 4&5 etc)
I am trying to organise this data into columns E,F,G.
For all rows, if data in Col C is "A", I would like to copy the data in Col B to Col F For all rows, if data in Col C is "B", I would like to copy the data in Col B to Col G Then copy data in Col A to Col E
Also, the data in Col A represents different cases, so 1 is the same case, 2 is the next case etc. As you can see different cases have different numbers of entries.
I am trying to create a basic order history in excel from orders generated on our limited ERP system. I have exported the data required and imported to excel, but due to the export limitations, I can only export certain fields at a time. This means that I have to create three sheets for all the data I require.
The first sheet has the bulk of the data and is now nicely collumned. However, I cannot get both the customer code and full customer name on the same sheet due to the aforementioned limitations. Sheet 1 has customer code and other data, sheet 2 has customer code plus full customer name.
I have created a collumn for the customer name on sheet 1 (A3) next to the customer code (A2)
Basically, what I am trying to get it to do is as follows: In "A3" I need to create a formula that states that if A2 contains a certain customer code, then look in sheet 2, match the code and place the full customer name for that code. Bearing in mind that we have over 395 customer codes in use, so asking for a specific code to lookup would be as time consuming as entering the codes manually.
I have previously used an Excel file - created by someone else - which had pre-entered data. It was set up in such a way that if I typed a person's ID number into a cell, their name popped into the following cell. I know that the names and IDs were pre-entered somewhere else - in another sheet? In a file linked some other way?
What is this called? How do I do it? I want to end up with a file where I can type a department code into one column and have the department NAME pop up in the following column. I am mostly doing this as a time- saving measure when completing long lists of supplies being distributed to different departments (long story.) If anyone can even tell me what this is called, so I can look it up correctly, that would be... sublime.
I'm trying to create a table with drop down options for each cell (a different set of options for each column). Normally I'd use data validation, but I need to use Excel Services with this document, and that doesn't support data validation. I've seen mentions of using pivot tables to accomplish the same thing, but I can't find an example that works for my situation. I have eight or so column headers, and need a fairly large number of rows, all with the dropdown option. Is there a way to do this?
Found several items close to this, but not exactly (at least that I could find). It's difficult to type what I need to do, but here it goes.
I have two sheets. Sheet1 has data that I want to look at in range A1:B7; Sheet2 has column titles waiting on data from Sheet1. If one of the column titles on Sheet2 is "Mike," I need to look for all "Mikes" from A1:B7 and pull the data automatically onto the other sheet. I apologize if confusing, but I tried as best I could to describe it. I have attached a small file for guidance. Using the attached file, take "Mike" for example. Under his name on Sheet 2, cell A2 would display 300, then cell A3 would display 1,000.
I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
I have a list of names that I'm looking up, and I want to list the data relevant to each name in a seperate place using a VLOOKUP ,(maybe theres a better way to do it) but some of the names appear more than once, resulting in an error.
I have on the first sheet, Name, Customer, Amount and Variance.
On the seperate sheet (in the same workbook) I have then name with 4 VLOOKUPS underneath, trying to extract the data Customer, Amount and Variance. But, I get errors in all of them.
Is there anyway I can List all the data relevant to each name using a Lookup function or is there another formula I should be using?
I am working on a workbook in which I extract data from a SQL database into a sheet with more or less the following format:
ID_Company ID_Level ID_XX
The aim is to extract a list of any employee that has been tagged as "NEW" in the last column, with his/her detail as per the column headings - and place the resulting list in another sheet. The extract is done dynamically and varies from month to month. So in other words the column headings 2013.MAY and 2013.JUN will change next month to 2013.JUN and 2013.JUL respectively - and so would the detail of the data below them as well. The "NEW" tag is simply assigned through an IF statement.
I had a look at most of the Excel Magic trick video clips on YouTube and managed to get a data extract from my main data sheet - BUT was only able to do the extract on the SAME sheet and NOT onto another sheet in the same workbook.
I am trying to export a range of data from excel to xml. I have a module that puts the range of data into one cell:
Function Concat(myRng As Range) Dim myStr As String Dim c As Range myStr = "" For Each c In myRng If c.Value "" Then myStr = myStr & ", " & Chr(34) & c.Value & Chr(34) Next Concat = Mid(myStr, 2, 9999) End Function
How to all data to come in with one decimal place (eg. "4.5","5.0",7.0")
Firstly, I have attached a file called test2 which has two sheets within it, one called list and one called 12345. This is for demonstration purposes only as in real life these are both separate files.
What I am trying to do is create a history in the file called list that extracts certain data from the file called 12345 and places it in a more user friendly format. No problems with the formulas etc, just a case of a few ifs and buts.
My real problem lies in the fact that file 12345 is only one of hundreds of files and in order to create the history, I have to repeat the process on all the other files, so that the list grows as I copy the data. Again for demo purposes, the file "list" sits in the C root ie C:list and the job files sit in C:files*.*.
Is there a way to automate the process so that formulas or VBA residing in the file "list" searches through the C:files*.* and populates the list as it runs through them.