Macro To Consolodate Information
Jun 7, 2007
I have a workbook with several SHEETS e.g Red, Green, Blue, Orange etc
I need a macro which will consolodate data to a Sheet named ALL
The macro needs to look at the first SHEET e.g. Red sheet Starting at C3 down to last record in C , COPY the information to the ALL SHEET, starting at C3. Move onto the next SHEET e.g Blue Sheet Starting at C3 down to last record in C then apend these records to the last enty in the ALL sheet.
etc. etc. for about 15 different colour sheets. I need to end up with all the records on the ALL sheet.
View 9 Replies
ADVERTISEMENT
Dec 9, 2009
I am trying to put together a macro to pull data from one tab to another. The key here is that I want it to take the entire row.
Basically it will look something like this:
As someone goes down the rows of the checklist putting x's in the boxes that need one (there is a column set aside for that), that box will turn red.
That part is easy and done.
What I cant figure out how to do next is to automatically have a copy made of the row with the red cell and send them to another document.
View 13 Replies
View Related
Sep 21, 2006
I have a form in Word that I go through and fill out different fields in the table with text. I need to be able to copy each specific field to an excel spreadsheet so we can have a database of the info. So far I am just recording a macro and trying to modify it. I can get excel to open.
Sub Testing()
Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.MoveRight Unit:=wdCell
Selection.Copy
Set appEX = CreateObject("Excel.Application")
appEX.Visible = True
appEX.Workbooks.Open FileName:="D:/AE Service Session/macro.xls"
End Sub
It needs to take the value that it copied and paste it into the first blank cell in column A.
View 9 Replies
View Related
Oct 2, 2007
I am trying to get information from two sheets. I have added the lines myarray1 and mystring1. I also added myarray1 in the For Statement. I cannot get it to run correctly. If you take these two lines out, and the myarray1 out of the FOR statement it works for one sheet.
Sub test()
On Error GoTo notfound
myarray1 = Array("A", "D", "G", "H", "I", "End")
myarray = Array("A", "Q", "R", "S", "T", "U", "V", "X", "Y", "Z", "AB", "AC", "END")
For i = 0 To WorksheetFunction.Match("END", myarray1, myarray, 0) - 2
x = WorksheetFunction.Match(ActiveCell.Value, Sheets("phones", "computers").Range("a:a"), 0)
mystring1 = mystring & Sheets("phones").Cells(1, myarray1(i)).Value & ": " & Sheets("phones").Cells(x, myarray1(i)).Value & Chr(10)
mystring = mystring & Sheets("computers").Cells(1, myarray(i)).Value & ": " & Sheets("computers").Cells(x, myarray(i)).Value & Chr(10)
Next i..........................
View 9 Replies
View Related
Feb 11, 2010
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
View 5 Replies
View Related
Feb 6, 2010
Currently where I work we print out registers to keep track of student attendances. This is done by merging an excel document that contains all the details of the names, days, dates , times, student groups and rooms of each lesson (each row contains the details for one lesson) with a word document containing the appropriate list of students in each specific group along with blank mail merge fields that relate to the excel document.
There are often 100 lessons a week meaning it takes an age to merge all the documents to create the registers.
Instead of this method I'd like the tutor to print off their own register by opening the excel document and clicking a button at the end of the appropriate row that contains the details for the lesson they are teaching. This would then take the details from that row and generate the appropriate register on another worksheet. Importantly it would have to list the correct group of students for each lesson - the names of these groups would be listed on other sheets.
Im guessing macros would be the right way to do this, but I have no real knowledge of them so alternatives would be helpful. Can anyone point me in the right direction? I've included a sample of how it would look with just one lesson in the excel doc.
View 8 Replies
View Related
Jul 11, 2012
I need to link information between two sheets.
In Sheet2 I have a list of products with their description in adjacent columns such as prices and other information. Sheet1 is a MasterSheet of some of the products listed in Sheet2. So I would like to write a macro that links the information of the product from Sheet2 to Sheet1 if I type the products name in Sheet1 (MasterSheet). The list of products (Sheet2) is updated in a regular basis, so by linking the info I would just need to update Sheet2 and Sheet1 would update as well.
View 4 Replies
View Related
Dec 30, 2012
I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
View 2 Replies
View Related
Jan 16, 2009
I have been helping a friend keep track of his clients, by making a form for them to fill out when they become a client of his (he owns a care rental) and I made a spread sheet for him to keep track of which cars are out when...
With that said, I would really like to have a Macro that i could run, when the form with all the personal information about hte client had been filled out - take that information and pull it over into another spead sheet that keeps track of all clients.
Meaning that workbook 1 is set up as a booking form and workbook two is just a normal list. I can make a macro that pulls over the infomation - but the problem is that it will always pull the information into row 1. Meaning that it overwrites itself each time I run it. I would like the Macro to know that it has to fill the information into the next empty row.
View 9 Replies
View Related
Mar 14, 2007
I am creating a macro that will take some information from 3 cells that the user will input, place it into 3 different cells, clear the original 3 cells and then I would like the macro to, when next activated input the new information from the 3 cells below the information that was already in the table.
For Example,
Date (D3), Description (E3, F3, G3 Merged cell) and Reference (H3)
I would then like those 3 cells to be put into A9:E9 then clear the original cells. That is not the part that I have the problem well, the following however, is.
After I enter new information into the input cells, and press the macro button, I would like the information to be placed into A10:E10 and from then on, the information goes down by 1 row after each macro.
View 11 Replies
View Related
Jan 16, 2014
I am a complete amature at Marco's and formulas. I have been trying to create what i need but i am unable to get it to work. I have a worksheet named "Course dates incl. pursuit" which has a table which runs from B7 to J144 in the table it has date, number, name, location, job, area, notifified, on system and passed?. in that order. I want if the passed column which is in J if that has a Y in it to copy and paste into another worksheet on the next avaliable line, worksheet name "Master."I would like it in a slightly different order if that is possible they have the same titles on the table but would like it to go number, name, location, job, area and date. Running from B5 to G(end of spreadsheet). That is the main part i would also like to to copy and paste into another worksheet if the answer is "N" but the above part is the most important part. Due to the computer settings i am unable to attach it
View 6 Replies
View Related
Jun 21, 2014
I was wondering if it is possible to protect a macro to be used only if a *.txt (for example) file wit some certain text is available. like a key to run hidden in a text. The file is situated in a company network, and i want to protect it from taking the file out of the company. I can do it to check for presence of a file - if the file is not available - the macro exits. but if someone break the password that is protecting the code will see the blocking point immediately. this is why i want to use a key....
View 9 Replies
View Related
Aug 13, 2014
What I would like a macro to do is take information from cells A1 to P1 on sheet1 and add them to the table in sheet2 and then I can save the workbook. The information in A1 to P1 is taken from other cells around the workbook. Once the information is in sheet 2 I can delete or change the informtion in sheet 1 without it affecting the copied information in sheet 2.
The next time I open the workbook I can enter the information in cells A1 to P1 on sheet1 then run the macro again and will then take the information and add it to the bottom of the table in sheet2 underneath the previous information that had been added - obviously the source of this information will now be deleted.
I will need to do this again and again building up the table in sheet2. The table will also use columns A to P like in sheet1.
View 7 Replies
View Related
Oct 10, 2008
I have a Macro with a main goal to download info from another workbook and reconcile it... the information being copied is always from a workbook called book1 so the following code is what i am using:
View 3 Replies
View Related
Nov 25, 2011
In my workbook I have a form that loads some information to my database. When the information is entered and the form button is pressed I want a message box to appear to give the user some information. On that message box I want there to be a button that the user has to press before the message box disappears.
View 1 Replies
View Related
Feb 9, 2012
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
View 3 Replies
View Related
Oct 8, 2007
My workbook is set up such that column B has dates (starting in B11 and going to B1200). C11:Z1200 has data that correspondes to the date in Column B. There are a lot of dates that don't have data. For example, row 15, there is a date in B15 but no other information for the remainder of row 15.
I'm trying to write a macro that will delete entire rows for the dates in which there is no data. So, I'm presuming that it would search C15:Z15, if there is not information in these cells, then delete row 15.
Row 16 becomes 15 when you delete 15.. I don't know how to get around this.
View 9 Replies
View Related
Jul 26, 2006
I need to create a simple stopwatch program to store the times and write details concerning each time. I got the stopwatch to work, but everytime i try to enter a cell to write in information(while the stopwatch is counting up), the stopwatch stops counting.
Is there any way to enter data while the stopwatch/macro is running?
View 3 Replies
View Related
Apr 2, 2014
I am trying to come up with a way, and do not know if it is possible. In the attached spreadsheet, I am looking to find a way that will automatically filter a group of records from the f column (all CC, all 2ndR, all NA) then calculate the information in in Columns G and H and place it in the appropriate box in D-21 through D 37. Example. I want all the rows that ONLY have CC in Column F. Then take the information from those records in Column G and column H. I want the sum of Column G in D21/2 and Column H in D23/4. I
View 2 Replies
View Related
May 19, 2014
What I have is a list of selected employees, with the site they work at next to them.
What I need is a macro which looks at the site they work at, references it with an address table in a separate worksheet and then pastes the site address along with the employee's name onto a thrid worksheet which will be used to print post labels.
>Look at which site the employee works at (Sheet 1, D:D)
>Find the address of said site (Sheet 3)
>Paste the relevant address along with the employees name (Sheet 1, B:B) into a separate worksheet.
This will be done weekly, and I need the macro to either only work on the latest's weeks table, or to just work with the current active selection. Any better way to layout my table.
View 12 Replies
View Related
May 22, 2014
I need a macro to move specific information.
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.
View 4 Replies
View Related
Feb 21, 2012
How to loop a macro which downloads historical stock information into Excel. I had the macro working for a single sheet, but I want it to update all of the sheets in the work book except a few. Now I keep getting a Error # 1004 unable to open error on line .Refresh BackgroundQuery:=False
Code:
Sub GetData()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' Exclude certain sheets
If ws.Name "Porfolio" Or ws.Name "Benchmark" Then
[Code]....
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("A5"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
[Code]....
View 1 Replies
View Related
Dec 31, 2013
I have a spreadsheet using an Input Form (user cannot get to the spreadsheet to enter data - they must use the form). This is working well.
What I need to do now is allow the user to retrieve data that is already entered. Example: Spreadsheet has 100 unique records (rows). In each is ID, Name, State, Dues, and about 20 other columns of information.
I need to allow the user to key the ID # and then up pops all the other information for just the ID provided. I will also need the ability for them to then change that information if needed.
So if they key 13WIL they will get Wilson, TX, $50, etc. And then they can make changes to any field needing a change. Add a phone, change and address, etc.
View 3 Replies
View Related
Apr 7, 2014
I am new to Excel VBA and am trying to create a macro that will draw information from a spreadsheet. The code i have written so far is shown below:
Sub Event1()
'Dim Event Date As Double
Dim Message As String
Dim Ans As String
' Prompt for Data
[Code] ......
The section i am having issues with is shown in red above.
What i am trying to do is have a message box pop up with the information as shown based on a date being typed into the the original box that pops up (Please enter a date and click on OK). The following message box then displays the required information from the spreadsheet.
View 4 Replies
View Related
Apr 6, 2013
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
View 3 Replies
View Related
Jul 15, 2014
I've recorded a Marco copying and pasting information from one cell to other cells and some of the copying functions paste to two cells. I just need this to loop through all information I'm copying from the the new cells.
Range("I1").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("J1").Select
[Code]...
View 7 Replies
View Related
Apr 30, 2014
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J
Email in Column L
Memo in Column N
Balance in Column T
Due Date in Column P
Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L
Subject: "Company Payment Remittance Payment Date *Date from Column P*"
Body: Hello *Name from Column J*,
For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
Example Workbook for Email Macro.xlsx
View 1 Replies
View Related
Mar 25, 2013
I am trying to find a macro that look for last non empty cell in column A and them paste a formula/comment in all cells of column B.
View 3 Replies
View Related
Jan 27, 2014
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
View 1 Replies
View Related
Dec 26, 2006
I used Excel to take inventory of the items in my store.
I have two columns that I would like to export into my point of sale program.
They are, "product code" and "inventory count". When I try to copy them into my pos system it won't work because I would have needed to hit the enter or tab key on the keyboard inbetween the two fields. So if I were going to enter it all by hand I would just type the product code, hit enter or tab and then type the quantity.
Is there anyway to make Excel export an Enter key press or tab key press inbetween the two columns? If I can't figure it out I will have to do it all by hand. (over 2000 items)
View 9 Replies
View Related
