Macro To Extract Data From Specific Column To New Sheets Specific Column?
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
I Need to loop through col W Used range except the last row which is totals or the first 4 rows which is Heading stuff, and if value > 0 post it to a column on the right based on the day code. ( 31 values 1 for each day)
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata() Sheets("ConsolidatedYTDReport").Select Range("E2:H4202").Select Selection.Copy XXXXXXXXXXXX ActiveSheet.Paste End Sub
I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.
Like this, Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...
Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.
So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.
Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.
Like this: Rows (1,2,3...,) 1---- 2---- 3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on.. 4 A --- B --- X --- Y --- X --- B --- Y 5 A --- B --- X --- Y --- X --- X --- X 6 A --- B --- X --- Y - --X --- Y --- C
After the macro it should be:
1---- 2---- 3 Mo - Tu - We - Th - Fr - Sa - Su 4 A --- B --- X --- Y --- X --- B --- 5 A --- B --- X --- Y --- X --- --- 6 A --- B --- X --- Y - --X --- --- C
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select For Each cl In Range("J:J") If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then Rows(cl.Row).Delete End If Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
I need to get a button to print specified columns of data. There are columns in between the data that I don't need. So what I need is something that will allow me to print everything in column D starting at row 4 down to the last cell with data in it as well as G and H starting at row 4 down to the last cell with data in it. I have somewhat figured it out but the data ends up being put on different pages when printing. If possible I need it to print side by side on the same page.
There's one sheet that has a list of customers (this is updated very frequently). These customers are either ‘New’ or ‘Existing (as listed in a corresponding column) - would it be possible to have a macro that, when run, will place a copy of existing customers into a second worksheet (“view list – new”), and a copy of new customers into a third worksheet (“view list – existing”), with the main worksheet still listing both existing and new customers.
As the main sheet that lists all customers is updated very frequently (with customers being added and removed throughout the day), would it be possible to clear all contents of the other two sheets that each list of 'existing' and 'new' customers will be copied into, before the two lists are copied into each of the two sheets (just to ensure there isn’t any data in there from the previous time each list was copied in).
I’ve given this a go via the ‘record macro’ function - some users of this WB use excel 2000, and others use excel 2007, but it only seems to work on excel 2007 (which is what it was recorded on)…
In this example, i need to get rid of all the rows except for the mfg part #. The heading "mfg part #" can be deleted as well.
The number of rows will vary each time i capture data. So for this example, i need 50 rows of data, but the next time i capture it, i might need to only keep 10 rows of data. The format will remain the same, the only thing that changes are the number of rows and the actual part #'s.
ive been writing a database for my business using excell and VBA
I have worksheets for clients surpliers labour and parts i have written userforms that add parts clients and surpliers to their respective sheets
I have started to white a userform that ties all this information together to create invoices it creates a new invoice number allows me to select which client the invoice is for and add labour to the invoice.
I have a txtbox called "txtsearchparts" and a button called "cmdSearchparts" at this stage i can type a serialnumber into the "txtsearchparts" box and click the "cmdSearchparts" button. This saves the text that was entered into "txtsearchparts" as a variable called "partsearch".
I am having trouble with the last bit. once the serial number has been entered , the button has been clicked and the serial number has been saved in "partsearch" variable, i want it to search the first column of the "parts" worksheet the the serial number contained in the variable and return the row address that the part stored on to a variable called partaddress.
I want to create a macro that when selected will take me to a specific row and column in another worksheet, this will be the outcome of two cells, so far i have it to take me to the row but no reference to the column can anyone help.
code i have so far.
Sub Find_Todays_Date() Dim FindString As Date Dim Rng As Range FindString = Sheets("Dynamic Whereabouts").Range("B1:B1") With Sheets("whereabouts 08").Range("A:A") Set Rng = .Find(What:=FindString, _ After:=.Cells(.Cells.Count), _ LookIn:=xlFormulas, _
What im trying to do is have a macro that will open a file called Blue 1.xls in location "I:SchedulesBlue" and then copy data from cells N13:034. Then paste it into another excel file (Press.xls)on worksheet "Press Break" cell G14.
The data that is copied not always fills up the cells N13:O34 so I need it to go to the next blank cell in column N and paste the information from file Blue 2, and so on for Yellow 1, Yellow 2, YellNR, and Green.
So to summarise I need a macro to open 6 files copy data from the same location on each of the files(N13:O34) then close and paste it into a master document(Press.xls) worksheet "Press Break".
Currently i hav list of info that is long like this.
A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10
How to move row to column example below. Because i want the data to be printed on an a4 paper which can fit around 3 row to save space . If i manually copy paste page by page it is too much.
A 1 D 4 G 7 B 2 E 5 H 8 C 3 F 6 I 9
===
Currently i have macro code which roughly does this.
A 1 B2 C3 D4 E5 F6 G7 H8 I9 J10
Sub test() Dim rng As Range, m As Integer, c As Range Columns("c:E").Delete m = 2 Set rng = Range(Range("a1"), Range("a1").End(xlDown)) Set c = Range("a1") Do While c <> "" 'MsgBox c.Address
Range(c, c.Offset(m - 1, 0)).Copy Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True Set c = c.Offset(m, 0) Loop End Sub
I have a set of data (time in minutes) below (fig 1) that I need to use if the total time between two entered times matches a coloms total. If it matches I want Excel to use the data in that colom to affect the data in another (fig 2) colom adding the times to each cell.
Looking at both figs. below I want Row B, Colom A in fig 2 to add Row A, Col A to Row A, Col A in fig 1 since both are the total of 19.
fig 1 Coloms A B C D Row A 5 5 6 6 Row B 5 6 6 8 Row C 4 5 6 7 Row D 5 4 4 5 Total 19 20 23 26
fig 2 Coloms A B C D Row A 1:00 2:00 3:11 4:01 Row B Row C Row E Row F 1:19 2:20 3:33 4:27 Row F-A= 19 20 23 26
I have a workbook named as "DCR_Summary". In the sheet "FX", I want to get the sum of a specific column from two different workbooks named as "WNCR REPORT" and "DCCR-REPORT". The name of the column is "FXCOLL". The summation should start in row 7 all the way down where data is available. The good thing is that, my sum range will always starts at row 7 in both files but the bad thing is that the position of column "FXCOLL" is changing every day. The expected result is shown in the attached "DCR_Summary". It should pick at the same time the Sheet names where the summation came from. Sheet names is also changing and sometimes the FXCOLL is nil, hence, 0 value can be returned.
I get an excel sheet with W/C Employer: (whatever) entered below the patient it belongs to in column E. This information needs to move into its own column (H) in line with the above patient information so I can use it in a mail merge to word.
The same thing needs to happen with WC injury date listed in column G. It moves to column I. Then delete the row it was moved from so I don't have an empty row.
Sheet 1 is what I get. Sheet 2 is what I need to end up with.
I want to insert a formula that is tied to a specific column. I know how to do formulas and have a slight understanding of macros. Can I insert the formula as part of a larger macro?
This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score.
I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players.
I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players.
I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?
I want it to count and fill in a range in column A until it sees a blank or notices the change in value in column B. In the example below i hope it shows what i need to do. i left the last group without numbers to show that is where it needs to start counting over again. i am basically wanting to count down 1st place 2nd place etc.
Any way to search for a value down column A, another value across roW 1, and tells what value lies in the cell at their meeting point. 4 example, I'm looking For "47" somewhere in column a, and also the number 53 somewhere in row 1, and I need to know what value is in the cell where those to meet.
I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
