Macro: Get Information From Two Sheets
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
ADVERTISEMENT
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
Feb 20, 2010
I have created a form in Excel for my wife to be able to enter data. Once the data has been entered, a print is required, but, the form is large, it contains lots of formatting, a lot of "space" and a lot of color. I world like to (on another sheet) collect all the relevant text, without any formatting, space or color to produce a concise print.
View 2 Replies
View Related
Dec 24, 2006
I have a workbook that has close to a hundred sheets in it (see attached). I need to know how to get all of the information onto one sheet in separate columns like this: EMPLOYEE NO| HIRE DATE| NAME| VAC AMOUNT|WEEKS|VAC DATES| # OF HOURS|AMOUNT|PPE
If you review the attachment you will see how the sheet is read from left to right, so I would need the have the information in that order.
View 5 Replies
View Related
Jul 16, 2014
search sheets by the name of cell , like name would be 17012-23-12-45 i need search by the first 5 letters 17012 , because sheet name 17012 , it would be for a lot of sheets so i need to do it automaticaly , and then that specific sheet has been found i need to copy table from there and some pictures in there
View 4 Replies
View Related
Apr 1, 2014
I am trying to tie two worksheets together. If text found in one cell in sheet1, make the same cell on sheet2 different color.*
There is a catch... no formula can occupy the cell in sheet2.*
My question is, is there a way of have a formula in completely different cell that will eventually fill the cell on sheet2 with proper information?
To explain a little better, I am trying to tie the sheets together, same cells and everything so when information gets put inside the cell on sheet1 the same cell on sheet2 will change color or display different information, and vice versa. That is the reason no formulas can occupy those cells.
View 5 Replies
View Related
Nov 30, 2008
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT
View 2 Replies
View Related
Jan 25, 2013
I have an excel with several sheets with task for different departments. All of them are assigned to a person responsible for that task. I applied auto filter which gives me the overview for a person Per department, now i would like to gather all departments into one so we have an overview for each person what his tasks are.
View 1 Replies
View Related
Feb 19, 2007
I have a wb containing several sheets that the field supervisors use to fill out for ordering material. Each sheet contains different types of material. The problem is that, if they only need one particular item from each type (sheet), they have to print 7 or 8 sheets just for those 7 or 8 items. I want to create a sheet that will list everything they are ordering in one compact, neat area, so they only have to print one sheet.
The set up of the sheets goes like this. There's a column containing the name of all the items. Next to each item is a place they can specify how many of that item they want. They leave it blank if they don't want any. That's it. Really quite simple.
So now, I need this new sheet to find all the cells that a quantity was entered on the other sheets, and list that and the description of the item wanted, along with what type of material it is. I have figured out one way of doing it, but it would take a crap load of hidden IF formulas, and I know there has to be a simpler way. I'm open to all suggestions - whether it's vb codes or formulas, it doesn't matter to me.
View 9 Replies
View Related
Oct 29, 2009
Can someone help me i have a few columns of information i need to move over to a another sheet, but the problem is the information i need to move is the end result of a formula and when i move it the answers are gone how do i fix this,,,,, and both sheets are on the same work book as well
is there a vlookup formula to use,,,im not experienced in Vlookup up formulas
View 9 Replies
View Related
Apr 1, 2014
I will be adding a weekly report to each sheet, which will have varying rows within it, but always the same column headers.
I want a master sheet that will automatically pull all the rows of data through from each sheet, create a new row if needed but merge rows with the same data name and combine the figures.
View 2 Replies
View Related
Oct 2, 2013
I have excel from windows 8 and I am trying to copy the information on one cell on a sheet to multiple sheets. I tried the click on one tab hold shift and click on the last tab and type the information or press F2 or paste the information. Well, nothing works.
View 5 Replies
View Related
Nov 18, 2013
Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?
View 3 Replies
View Related
May 9, 2012
I am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
View 6 Replies
View Related
Aug 21, 2009
I have a blank sheet and need to total values in cells to the right of certain information from a range in 90 sheets in other workbooks contained within the same folder.
for example the range is O13:AX500
the first text to search would be "P1", that could be found several times within the range in each workbook with the sheet named "plant"
The value to total would be found in the 2nd cell to its right, ie "P1" in cell O13 then value "£290" in Q13 ::: "P1" in cell X365 then value "£100" in Z365 and so on throughout all 90 workbooks.
The total of the referenced values would then be placed in say B1....
I have around 500 reference codes to total throughout the 90 workbooks so maybe having a list of the text reference down column A may help the process.
Is it possible and can anyone please help with the code to look at a piece of text in new sheet with cell A1 to A500 containing "P1 to P500" and then total all values in the cell 2 to the right in every workbook and place the total in new sheet cell B1 to B500
View 10 Replies
View Related
Mar 26, 2014
I would like to generate a dynamic list of employees on sheets based on information on Sheet A. Here is my workbook structure:
-Sheet A contains all employees information (Name, division, salary hire date, etc)
-Sheet B is named Div A
-Sheet C is named Div B
-Etc...
Here is what I want to happen:
-Sheets B should pull the employees name and salary from sheet A only for employees of division A
-Sheet C should pull the employees name and salary from sheet A only for employees of division B
-Etc..
Sheet B, C , etc. should update the info and resort every time sheet A is updated.
View 5 Replies
View Related
May 16, 2014
So, I'm setting up an accounting book. There's a master sheet that needs to pull from the other sheets that are made. The problem is, with each new sheet that is made, I have to update the formulas on the master. I would like that if I made a new sheet, the master would automatically pull from it.
Example:
Master pulls renter fee info from May 3rd, May 7th
I create sheet (May 15th)
Master pulls renter info from May 3rd, May 7th, May 15th
View 1 Replies
View Related
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
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
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
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
