Macro To Copy And Place Data To Specific Place
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.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Fill In The 3 Criteria Fields, Calculate The Result, Copy The Result, And Place It Into The Correct Place On The Table
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?
View Replies!
View Related
Place Chart On Specific Range Macro
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! The concept: 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. The problem: 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 would like charts in the following ranges: A20:B30 (Immigrant Group) D20:E30 (Ethinc Group) F20:G30 (Languages) F13:G19 (Religion) D10:E17 (Age structure) This is the major part of my project that I need to complete. Any help would be greatly appreciated, PLEASE. =) Right now I have ranges that aren't accurate, but regardless of how I try to show my range, the function does not work: Sub CreateCharts() Dim counter As Integer, chartname As String, xvals As String, offset As Integer, Range As String 'Delete all charts Dim oc As ChartObject For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects oc.Delete Next oc
View Replies!
View Related
Macro To Extract Data And Place Into Template ...
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.
View Replies!
View Related
Move Chart To Specific Place On Worksheet
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.
View Replies!
View Related
Rows Copy Over To A Different Sheet And Place The Next Available Row
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. ******** ******************** ************************************************************************>Microsoft Excel - BETA SCHEDULE.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJKLMNOP1Date EnteredTime Ent'dCUSTOMERJOB #LINE #MATERIALMATERIALMAT'L QTYPAPERPAPER QTYFinish QNTYSIZEMach.RUN DATEDue Date233/318:58 AMCUST. A2585271HSS5260 15 SH 15 / 1530X65 .02013/313/3143/318:58 AMCUST. B2585241PC 8256 7 FT 3 / 624X26 .01023/313/3153/318:58 AMCUST. C2585161TFS1010 3178 FT 615 / 61532X61.5 .00833/314/1MASTER [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Macro: Place Result Value Instead Of Formula
I am testing the code below... Sub applyCombination() Dim shtP As Worksheet Dim j, k As Integer 'j = row; k = col On Error GoTo errorhandler Set shtP = Sheets("Sys-Year")........ k = k + 1 Next k j = j + 2 Next j Exit Sub errorhandler: Exit Sub End Sub and, as it is a bit slow to paste a formula in each cell, I was wandering: Is there any way I can place the resulting value ONLY? (I don't want to do copy and paste special just values as it takes even more time)
View Replies!
View Related
Using If, Extract Data From One Worksheet And Place In Another
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.
View Replies!
View Related
Automatically Filling In Data From Another Place
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.
View Replies!
View Related
Lookup Data And Place Corresponding Information On Another Sheet
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.
View Replies!
View Related
AutoFilter Via Macro & Place Text In Visible Cells
I need a macro that can change the text in Field 46 from Criteria1 to something else, ie from "To be capped" to "Capitalised". This is my current Selection. AutoFilter Field:=39, Criteria1:="OPEN" 'STATUS Selection.AutoFilter Field:=46, Criteria1:="To be capped" 'TYPE Range("AT1").Select how to change the text only on the selection found by AutoFilter?
View Replies!
View Related
List The Data Relevant To Each Name In A Seperate Place Using A VLOOKUP
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?
View Replies!
View Related
Extract Data From Files Within The Same Directory Or Below And Place In New Workbook
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.
View Replies!
View Related
Formula To Lookup Data In A Vertical Format And Place In A Table
I have a RAW DATA work sheet that has data of electricity consumption for a given week but it is in a vrtical table. I have many other work work sheets in the workbook that I require to look at the RAW data and the return the correct information in the specified cells I need the store number that is in cell F1 of each sheet and the Date on each sheet that are on Row4 of each sheet to Look up and match the information in ROW1 for the store number and columnA for the dates. then in columnB of RAW DATA I have time intervals of 30mins which need to match up with the time intervals on the sheets and display the readings from the RAW data on the sheets. ******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Energy Analysis WE15-03-09.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2A3A4A5A6A7A8=ABCDEFGHIJKLMNO1Reading DateReading Time8912116617118519682296710191119125612571292209/03/200900:0012.5926.74929.69668.728.6487.526.5616.2312.6416.3818.08317.02719.569309/03/200900:3011.8467.211.49610.1245.8726.821.817.9811.3216.711.96214.65619.243409/03/200901:0010.7368.11211.19811.286.27.415.2330.3412.0416.269.5527.26429.02509/03/200901:3010.78767.612810.68510.40725.6966.814.888.936.8416.618.53448.72645.4432609/03/200902:0011.0727.235213.01310.3235.9288.814.757.875.9218.059.38247.09445.3136709/03/200902:3011.2996.819210.26210.1765.70410.414.758.135.0916.489.0566.88325.1984809/03/200903:0011.8116.18248.952411.3695.88.314.697.774.9916.87.20964.71046.2496RAW DATA [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Move Numerous Data To Any Favorite Place In Other Sheet Quickly ?
Let`s assume that I have numerous "Serial Number" in "Sheet1" : http://i40.tinypic.com/2iqbspt.jpg Now I want to create some cards in "Sheet2" that each card have a serial number that related serial number there is in "Sheet1" : http://i43.tinypic.com/34fz690.jpg. Need easy solution for doing it while when I drag one card to copying it then related serial number in sheet1 would be appear in required field. I mean first card has first serial number of sheet1 and second card has second serial number of sheet 1 ; but I want to doing it quickly with dragging.
View Replies!
View Related
Countif (check The Data For The Following Conditions, If It Meets The Crirteria Then Place A 1 In Columns)
the traditional count if statement doesnt return what I need. I have an array of values that need to be checked. Column: A B C D E Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result) What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E. Criteria 1 If any of the coulmn data contains a value less than .5 I need a 1 placed in column D Criteria 2 If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.
View Replies!
View Related
Where To Place The Macros
I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.
View Replies!
View Related
Allows Me To Place Checkboxes In Each Row
What I have is a long list of about 100 items. I would like to set up an excel sheet that allows me to place checkboxes in each row and then whatever checkboxes have been checked, to simply collate and print out ONLY the check boxes that have been checked. I have gotten as far as to create and rename the checkboxes but my skill ends there. I also need to know how to get each checkbox to correspond to a row. I appreciate ANY help that you can give me. EDIT: this doesnt NEED to be checkboxes, it can be optionbuttons if that is easier... simply something to select/deselect and print. 1.1 a blah 1.1 b blah 2.1 a blah 2.1b blah 3.1a blah 3.1b blah 3.3.a blah etc blah etc
View Replies!
View Related
Using DMIN In Place Of Arrays
I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows. Here is my problem: I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below. Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row. In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3). Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
View Replies!
View Related
Place The Name Before Equal Sign
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort? Private Sub ConditionalRowDelete() Dim NumRows, iLine As Integer ActiveSheet.UsedRange.Select NumRows = ActiveSheet.UsedRange.Rows.Count Dim CircuitType As Range Set CircuitType = Range("C" & iLine).Value For iLine = NumRows To 2 Step -1 If Range("A" & iLine).Value > "6999" Then Rows(iLine).EntireRow.Delete End If Next iLine For iLine = NumRows To 2 Step -1 If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then Rows(iLine).EntireRow.Delete End If Next iLine End Sub
View Replies!
View Related
Place File Name Into Private Sub
The code below writes the names of all active worksheets into the A column of the active worksheet. I have a situation where I have 2 workbooks open (3 if you count PERSONAL.XLS). One of the worksheets has a consistent name, the other has a name that changes every week (not in a consistent pattern). I'd like to be able to use the routine below to get a list of open workbooks, ignore the one I'm working in and PERSONAL.XLS, and instead put the remaining filename into other Private Subs in a User Form to run other routines.
View Replies!
View Related
How To See Where The 55th Place In A Cell Is
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap. I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell. I have thought about coloring the first 55 character/spaces ? or inserting a character at space 56 ? Example below XXXX XXX XXX XX XXXXX XXX XX XXXX XXXX XXXX XXX XXX XXX XX~XXX X XXXX XX
View Replies!
View Related
Formula To Place Name Next To Appropriate Date
Spreadsheet 1: So in column "A" I have all of the dates written out. Then i need column "B" to put the appropriate name next to the date that person chose in spreadsheet 1. Spreadsheet 2: I have a live form that feeds data into a different spreadsheet. I am using google spreadsheets.... The formulas are the same as in excel
View Replies!
View Related
To Place A First And Last Name Into An Email Convention
I have a spreadsheet where I have the first and last name in two separate columns. In the same spreadsheet, I have a column where there is an Email Convention. The email convention can change from row to row. Is there a way to take the first and last name columns and make it format according to the email convention column. For are some samples. First Name Last Name Email Convention Ada Smith @mail.uch.org Ann Blanc _@chs.net Bob Jones .@halifax.org
View Replies!
View Related
Check If AdvancedFilter In Place
I'm in the process of debugging some code at the moment and so have removed the On Error Resume Next's that I had in there. I've since gotten rid of this where it wasn't necessary, but I have a general question about using the ShowAllData method with Advanced filter. When I use: ActiveSheet.ShowAllData when there is no advanced filter applied, I get an error saying the method of Worksheet class failed. I have searched and cannot find a way to check in VBA if there are advanced filters applied, though I can find code that will check if AutoFilters are applied. This doesn't work with AdvancedFilters.
View Replies!
View Related
Save The Tamplate In Original Place
I have VBA program that collect the data from database and make a report.(I will mention I open the browser to look for database ) Here is a problem: My original file is " Daily Report.xlt(template) " .At the begin when I open this file ,the file name appear as a " Daily Report1 "...This is not big deal When I run macros I need to keep some info on the original file. After I done, I have following code to save as " Daily Report.xlt " . but the actual problem is ;the code save this file on the database path that I don't want it.Actually I want to overwrite on original file to keep that file up to date.Here is code ActiveWorkbook.SaveAs Filename:= _ "Daily Report.XLT", _ FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Note:I know I can add the path in front of the file name and save it in the that directory .Different customer different path I can not use fix directory.
View Replies!
View Related
Ranking Multiple Columns And Second Place
******** ******************** ************************************************************************>Microsoft Excel - Win_calc.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutQ21= QRSTUVWX21TickerLowestLowest*NameBOALEHBEARSecond*priceSecond*name22AT*US95/BEAR10210095**23ARW*US50/LEH605070**24BSX*US65/LEH666567**25CBS*US53/BEAR/MS555453**HVOL8* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR. This is my general template. I get prices from BOA, LEH AND BEAR on the four companies listed. Then I use the Min function to tell me the best price (I'm buying). Then I use some IF statements to tell me who's the "Lowest Name" (lowest price): =IF($G2=$C2,"/"&$G$1,"")&IF($H2=$C2,"/"&$H$1,"")&IF($I2=$C2,"/"&$I$1,"")&IF($J2=$C2,"/"&$J$1,"")&IF($K2=$C2,"/"&$K$1,"")&IF($L2=C2,"/"&L$1,"")&IF($M2=$C2,"/"&$M$1,"") It checks if a cell is = the lowest cell value, then returns the header. This formula refers to different columns than my displayed sheet because Im hiding so cells and I just copied this to another section of my sheet. There's probably a better way to do this but this is what I have do far. I use this for 50 company names and 5 or six dealer banks (BOA, LEH, etc) I'd like column that gives me the Second Place price and one that gives me the associated dealer (BOA, LEH).
View Replies!
View Related
VB Code To Place Formula In Cell
Upon user making selection from data validation list in Active Row Selection & Column A : i would like to do the following (is possible) ,,,,,,,,,,,,,,, Place formula in Active Cell Row & Column S =MT4|BID! (followed by cell A1 but replace the "/" with "") followed by "m" I have tried several combinations but with no success. Also - where would i place this code so that it automatically updates the formula in Column S as soon as the user changes selection in Cell A?
View Replies!
View Related
Place Date In Cell H6 Automatically
I have the following Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A100")) Is Nothing Then With Target(1, 2) .Value = Date .EntireColumn.AutoFit End With End If End Sub I just want the date to appear in cell H6 autmatically and the time to appear in H7.
View Replies!
View Related
Automatically Place The Grades From The Other Workbooks
I have created a gradebook and it has several types of workbooks for the student grades. 30% of the grades are calculated in different workbooks. The main gradebook is where the teacher places the mid term, final term grades. In the same workbook the teacher must place the grades for the following areas. (Participation Grade, Following Rules, Homework Grade, and Monthly Test). Now this is my problem!!! In the main grade workbook I need to automatically place the grades from the other workbooks. Example: ='Participation English P-6'! ( Cells - AN84 for July, AN160 for September, AN274 for January, and AN350 for MArch. I tried to use = COUNTIF(AN84:AN160,AN274,AN350) but it will not work. I can get one grade at a time by using ='Participation English P-6'!AN84 , which will give me the grade in the main workbook that I need. But I don't want to change everytime the grade period comes around. forumal that will give me grades from the other workbooks at the grade time from these cells?
View Replies!
View Related
Count If Formula And Place In A Graph
I have a spreadsheet of which i need to count instances on 2 COLS and place in a graph. Example D...................................... Col E JO BLOGGS....................... ERROR JO BLOGGS....................... ERROR JO BLOGGS....................... SPOIL JO BLOGGS....................... SPOIL JANE DOE........................ ERROR JANE DOE........................ SPOIL I need it to count the errors Jo bloggs had so i can populate the chart, the chart would be no problem once i can separate the data as described. I would then have to do the same with Jane Doe.
View Replies!
View Related
Place Average Of The Five Numbers In Column
Column B values are the average of the most recent five values in column A. The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7? Note: I just want to be able to pick up the most recent average value – the method whereby it is done is of no consequence. However I cannot think of an alternative. Example:.....
View Replies!
View Related
Place Currency And Number In Middle
I have a problem here with the currency style, when I put the number in the cell and clicked the currency style button, the currency is placed in the left side and the number is in right side. How can I make it both in the middle? http://i44.tinypic.com/bfnbqd.jpg
View Replies!
View Related
|
TRACKER
