My very large document uses the CHOOSE function in many places to paste content based on the value of a COMBOBOX at the beginning of the document.
My problem is that I now need to paste an entire table for a certain CHOOSE function value. Documentation for the CHOOSE function allows reference to a range of cells such as A1:B12, however, I cannot get it to paste the table in the document.
I have a ListObject table called Table1, and the column name is Table1[Company]. The records have been sorted A-Z for the Company.
The records look something like this
Date Employee Company
-------------------------------------------
1/1/11 Jane A Company
1/1/11 Jane B Company
3/1/11 Bob B Company
2/1/11 Bob B Company
4/1/11 Bob B Company
5/1/11 Bob C Company
What I need is a sub() to look in the column of Table1[Company] and determine:
The # of records that match the criteria given the Company name, and where they start and end. Ultimately, I'd like to copy and paste this portion of the table to another spreadsheet.
So for instance, if the Company = "B Company", then I could find out there are 4 records and there locations to copy and paste them.
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
If you look at "B9" I have created a list of names and when I choose a name their ot hours appear in "K9". Now when I try to do the same in "H9" to bring back there qualification it gives me an error. I also tried to do the same in "M9" for the phone numbers and it didn't work.
View 7 Replies View Relatedconditional Macro or filter? Easy to record a Macro which filters, but choosing what (column) to filter based on values of a cell I'm not sure how to go about that. It means implementing IFs into a Macro, or..?
Data on "Staff List" sheet I have a list with all staff details including availability. I need to filter their availabiltiy and copy values over to another sheet. The availabiltiy shows Monday to Sunday and states for which of our offices they are are available. Here the columns BS to BY
Available Location Monday
Available Location Tuesday
Available Location Wednesday
[Code]....
Now I need the Macro to choose which column to filter based on the office set in cell C3 on the "Availability" sheet. And then filter that column for YES.
I am attempting to choose a cell containing a text string based on information placed in a raw data area.
My output from this formula will be a line of text picked from yet another reference cell, based on each of the four choices.
Removed non pertinent information and edited for clarity.
Basically...here's what I'm trying to do...
I have 7 numbers. The numbers are going to be grouped in threes.
(Ex. 123,124,125,126,127,134,....)
I want to assign column/range for each number...
1=A2:A100, 2=B2:B100, 3=C2:C100...
So for 123 the formula would be...
sumproduct(--(A2:A100>criteria),--(B2:B100>criteria),--(C2:C100>criteria))
It would be awesome to have the formula automatically calculated based on the numbers I choose.
I am trying to basically search for the last non zero entry in a column and then return a value in the same row as that last non zero entry.
For example:
My first column of the table is time, second column is submission/marking of coursework (denoted by +1 & -1) and the third column is the number of courseworks awaiting to be marked. My aim is to search the third column for the last non zero value then return the time (from first column) that last non zero value appears.
Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.
Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).
The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.
This list, unlike pricelist, must be visiablly broken down by job phase.
I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.
I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.
When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)
Sheet2
ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................
I have an 'existing results table' as per my attached sample. From this table I need to create a list of 'sold' for each annual date range - i.e. I need a separate list for items sold within that date range. see my example in red. *Note. I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
View 4 Replies View RelatedHow to copy paste data as a relationship table.
In attached workbook...when user will click on "Relate" button on "rawdata" sheet....then data should be visible in same form as showing in "relate" sheet...
Book2.xls
I'm trying to make a script that will copy and paste a row in a table underneath the last row in that table when a button is clicked. I've simply recorded a macro to get things rolling, however whenever the macro does an insert to paste the data it goes above the last new entry which doesn't work for my purposes,
View 14 Replies View RelatedI am creating a simple IF function. that says IF cell (cell name) = 2007 then copy and special paste values from these cells into this location...I am not entirely sure how this should be set up.
View 2 Replies View RelatedI have a very complicated table that has a bunch of links to other cells on the sheet. I would like to duplicate this table onto another sheet in my workbook. However, when I try to do that, the links also change when I paste it on the new sheet. Is there a way I can keep all of the links to the original page after I have pasted the table on the new sheet?
View 4 Replies View RelatedWhat I have is a table of employees names, which site they work at and a comment.
I have a table on another sheet with the addresses of all our sites.
What I need is a macro which will:
>look at the active selection
>copy their name(sheet 1, row B) & comment (sheet 1, row G)
>look up the site they work at (sheet 1, row D)
>copy the relevant address (sheet 3)
>paste the name and addresses into different sheet
>paste the comments into another sheet
This will be used to make printable labels for letters that we post out to employees. They will need to be split up so each name and address is printed on a different label.
The comments will be printed onto letters which will have a headers which is the same for everyone, but would be nice if they could be personalised with the comments of their managers and perhaps their names (something like "Hi John, you have been selected by your manager for your great work, please find enclosed your badge. Jeff's comments: "Comment 1""). But this part is a nice extra, the important part is the address labels.
I have a tab with several small tables (Column E-G). What I need to do is to copy them as pictures, move them to another tab, change the picture size, and align them in the new tab.
The data set is huge and it would involve lots of labor if copy and paste one by one. Is there any way to do it using a macro? I tried to record a macro, but it doesn't work. It will not change the size of the picture and I don't know how to tell Excel to align them the way I want.
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.
I am looking for better way to copy formula and paste it to dynamic table (I don’t know how many rows there is at the table). The procedure takes long time for the user. (the table may contain more than 30,000 rows !!).
View 3 Replies View RelatedI would like to know if it's possible to copy a pivot table from one sheet and paste it into in another sheet such that two pivot tables are not linked i.e. if I create a calculated item in pivot table it doesn't show as an item in another. If yes, how could I do that?
View 1 Replies View RelatedI have a spreadsheet with macros and conditional formatting assigned to it. What I want is to be able to copy only the displayed contents, without the macros and formatting instruction, to a separate workbook for reporting purposes.
View 2 Replies View RelatedI am trying to have Excel 2007 open a Word Table, Select All, then Copy and paste to the open workbook. Here is what I have so far:
Sub Comments()
Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "R:JohnstonOfficeProductionSO COMMENTS.doc"
Selection.WholeStory
Selection.Copy
Right now I and getting an "Type not defined" error and the Dim oData As New DataObject step. Also if delete the code to clear the clipboard, I get an error at the WholeStory step.
I have a problem in creating Pivot Table in the format that I want for the purpose of report, so I'm not even sure if I should use it at all.
In the file attached, I have a sample data source in sheet 'Source'. Basically the data are already in the list format.
For the purpose of reporting, I want to make this data in the format of sheet 'Final Format Wanted'. Note that the report only takes in data from columns D, M, N, O ( Headings highlighted in red).
(1) I understand that I can do something like this with a Pivot Table, and I already did something like it in the sheet 'Pivot'.
The Pivot table works well for Total Exposure (data from column M)The thing is, I want just the Grand Total Figures for column N and O instead of putting it in the data section itself. For better explanation, please take a look at sheet 'Final Format Wanted'. I just want the grand total for these 2 columns because they are less important and I want to be able to squeeze just the relevant figures for the report. Can I do this?
(2) Another way I have thought of is actually using a combination of Vlookup and concatenate function (look at sheet 'vlookup'). The thing is, some names that I used to concatenate might appear twice (look at row 39 and 40 in sheet 'Source' under column L), so if I am to use this, I'll need to find Sub-total first then copy and paste each item again into the format I wanted.
To me, this seems to be a really long way of doing it. Pivot Table is preferable in that it can count the total automatically for me, but I can't get the format I want.
Does anyone have any suggestion what I can do? If I use
(1)Pivot table - how can I get just the Grand Total of the other 2 columns?
(2)Vlookup - Is there a faster way in getting the subtotal and ultimately get the data in the format I want?
I have a sheet in which you enter in new information. I need a macro to copy that information onto another sheet, onto the first empty row, so it does not clear the information that has already been entered. The first sheet is an information entry sheet, and the second is the database, which contains all of the information. I hope this is clear, if you do not understand, I will be happy to rephrase my question!
I have included two screenshots of the excel system. They are named.
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.
I want the same affect as copy a table and pasting into the email. So i guess i need to copy a range and save as html in a string. It seems like it would be simple since its just copy and paste into the email, but i've been unable to find anyting on it.
View 3 Replies View RelatedWhen i click a button in excel, i need the table from a certain "Range" to copy/paste in the body of an e-mail that will send itself automatically. Now, i know how to build the macro to automatically send the e-mail but i can't find a way to insert the table in it. here's the code i'm using to send the e-mail:
Public Sub SendMail2()
'Tools --> references --> Microsoft Scripting runtime
'Variables
Dim EMailSendTo As Variant
[Code].....
But i don't know how to adapt the code for my problem.The fact is, i've just started to write macros and the code i'm using for the e-mail isn't from me, i have juste adapted it for my problem.
i need a code to open a specific word document, copy table from word into excel with a link so when the word document is changed so is the excel doc
my macro pulls download in on sheet1. On sheet2 it makes a pivottable of it with horizontally displayed the suppliers and vertically the codes of products. This is of course dynamic (one month it may contain 10 suppliers + 8 products, other month 15 suppliers + 20 products). On sheet3 is the (static) lay-out of all suppliers and all products. Now, what I want is that all fields <> empty (or zero) from the pivot table are to be copied and placed in sheet3, the 'report' I have to fill in. index and match won't do the job I think.
View 2 Replies View RelatedIn the sample worksheet, I need VB code to copy the formulas in column D, to paste them as values in the same cells (without removing autofilter by clicking on menu Data>Filter>Autofilter, then edit>copy>paste special>values).
sample2.xls
I have a chart and a data table (please see attached file named "delete_1.xls).
I need to present these 2 items in a single slide of PowerPoint.
I have done like this.
-First copied the chart from Excel.
-Then pasted it in PowerPoint using "Paste special".
-Then clicked "picture (windows metafile)" this is to reduce memory consumption
Then repeated the above steps for the excel table too.
The PowerPoint slide that I got cannot be attached as system doesn't allow me.
Question: I wanted to a give a paper copy of the PowerPoint slide to my Director who needs a PowerPoint slide and not an Excel chart. But in the slide, the numbers of the table look much juggled and as if the numbers are too closely typed.
I tried with various different fonts in Excel and then copy and pasted in PowerPoint but the problem persists.
What things I should do in Excel table so that cell values are clear in the Table presented in PowerPoint ?
Attached File : Delete_1.xls‎
