Pasting Values #2

Hi,

I have developed a quoting tool which creates an output sheet detailing
all the info, What I would like to be able to do is take a copy of this
and paste the values (and Format to a new workbook). I'm currently
doing this manually and I unsure how to automate it!

I have never used VB which I'm guessing is the only way of doing it, so
please be gentle!!

Thanks,


Sam.


-- 
sammy2x
------------------------------------------------------------------------
sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674
View this thread: http://www.excelforum.com/showthread.php?threadid=496906

0
12/30/2005 12:02:56 PM
excel 39879 articles. 2 followers. Follow

1 Replies
524 Views

Similar Articles

[PageSpeed] 49

Try recording a macro when you select that output sheet.
Then edit|move or copy sheet (select copy and copy to a new workbook)
Then select all the cells on that new sheet.
edit|copy
Edit|paste special|Values

I got something that looked like:

Option Explicit
Sub Macro1()
    Sheets("Sheet2").Select
    Sheets("Sheet2").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub



sammy2x wrote:
> 
> Hi,
> 
> I have developed a quoting tool which creates an output sheet detailing
> all the info, What I would like to be able to do is take a copy of this
> and paste the values (and Format to a new workbook). I'm currently
> doing this manually and I unsure how to automate it!
> 
> I have never used VB which I'm guessing is the only way of doing it, so
> please be gentle!!
> 
> Thanks,
> 
> Sam.
> 
> --
> sammy2x
> ------------------------------------------------------------------------
> sammy2x's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29674
> View this thread: http://www.excelforum.com/showthread.php?threadid=496906

-- 

Dave Peterson
0
petersod (12004)
12/30/2005 2:44:41 PM
Reply:

Similar Artilces:

Oops! #2
anonymous@discussions.microsoft.com wrote: And? ...

How do I set up a chart to only chart values greater than 0
I have a chart in Excell that I would like to only have chart those items with a value of greater than 0 (zero). Not sure if there is a better, easier way than having manually manipulate the data. Let's say the values are in B1:B20 In C1 enter =IF(B1>0,B1,NA()) Now use column C for the chart; the displayed #N/A values will be ignored by the chart engine best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "debra49424" <debra49424@discussions.microsoft.com> wrote in message news:A513E053-4AD9-44FE-952E-E9A60F49...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Text values to numeric values
Is there a formula to convert a text value in a cell to a numeri value? Thanks, Jayveeja -- jayveeja ----------------------------------------------------------------------- jayveejay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=504 View this thread: http://www.excelforum.com/showthread.php?threadid=39463 Good afternoon Jatveejay Yes there is. Say your numeric value is in A1, in B1 the formula: =VALUE(A1) will show the number as a value. If you wanted to convert a number in its cell from a text number to a value number, ie., not use a formula in a di...

Can't activate trial version #2
I ordered a trial version and I've tried 3 times to activate it and I keep getting the error message that I used the wrong product key to install it. I double checked it 3 times. HELP! ...

Need null values for several fields in db
I have an equipment inventory db. I need to run a query that would give me the records that contain null values in several of the fields. I have run a successful query to return records that have a null value in one field but don't know how to combine so that query returns records that also have null values in other fields or any other fields. Please advise -- dmp You join the seperate queries with a UNION. SELECT * From YourTable WHERE YourField Is NULL UNION SELECT * From YourTable WHERE YourField2 Is NULL UNION SELECT * From YourTable WHERE YourField3 Is NUL...

Match values in more than one column
Hi I have three columns of data and wish to highlight the matches or change the format if there are any duplicates. i.e A B C Apple Banana Cheese Banana Orange Milk Chocolate Milk Water Biscuits Pear Bread Wafers Plum Banana So Banana would be highlighted in all, Milk in B and C. I assume I'd use conditional formatting, but the MATCH functio...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Conditional format
Hi Conditional format Is there a way to get a cell's format determined by the format of a different cell?! e.g. Could one get an entire row market up in say bold red if one cell in that row was say less than 100 With thanks Ship Shiperton Henethe Hi Shiperton yep ) select the rows that you want to apply the conditional formatting to, ensuring that the first row of this group is the first row at the top of the screen (it gets confused sometimes if it isn't) the following example assums you've selected from row 2 onwards and you want the row to turn red if ...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

Linest #2
Hi, I have 4 variables A, B, C and D. I know the relationship between each of the variables with Result i. each variable as an independent variable. How do I use this information to find an equation with all the four variables that gives me the result. I looked at LINEST but I don't kno how each variable affects another- that is why I want to fit an equatio that involves those variables. Any help is appreciated -- mnarasi ----------------------------------------------------------------------- mnarasim's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14...

Look up multi values
I’m struggling figuring out how you would do the following? Work sheet data. A1 =25 B1 = 40 C D 1 25 30 2 25 40 3 27 41 4 28 42 What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4 Matching the value 25 in Column C & 40 in Column D & return the location in the table to Cell A2? If there is no matching value, were decimals come into play, i.e. 25.8, then return the next largest from column C with its parent value from D, Returned value = 2 or 3 if 25.5. Sounds simple but I get lost very quick! Any Help greatly appreciated. one way. This is an array...

all cells in 2 workbooks have automaticlly changed to date format
Why would excel 2007 automaticly change formats? Upon opening a workbook this morning all the blank cells in the 16 sheets had changed from general format to date format, and 99.9% of cells with data or formulas were also changed to the date format. I opened another workbook and it was find, I closed it and reopened it and the same thing occurred, most cells had been changed to the date format. The first wb containts payroll data, with mostly sumifs functions and was working fine yesturday. The second wb contains data using vlookup and if functions. Thank you Deb Found the...

Checking a Yes/No field for its value
I'm trying to set a value in a custom number field based on alternatives in two other fields, and this is what I have tried so far: IIf([Text18]="Business Activity",[Number10],[Number1]), where summary tasks may or may not meet the test condition. This works for tasks and milestones, apart from summary tasks where the relevant value is not copied in - it's set to 0. So how do I add in an additional test for summary tasks to set the value explicitly? Here's what I have tried so far ... IIf( [Summary], truepart, falsepart ) IIf( [Summary]=True, truepart, fa...

The following recipient(s) could not be reached: #2
Hi A end user send a email to a external client and receive the following message back. The following recipient(s) could not be reached: User@domian.com on 10/11/2006 16:09 You do not have permission to send to this recipient. For assistance, contact your system administrator. MSEXCH:MSExchangeIS:/DC=local/DC=domain:MAILSERVER Can any one help me with this problem Thanks in advance Hi there, Is this your Domain/Exchange Org? ' MSEXCH:MSExchangeIS:/DC=local/DC=domain:MAILSERVER' Oliver "antony" <antony@discussions.microsof...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Numeric values in data area of the pivot tables
How can I bring a numeric data into the data area of a pivot table? I need to sort the numeric values in the numerical order. Any idea? ...

Fill Handle in .NET #2
I am trying to develop a code which handles copy/cut/paste functions in Excel within a macro. I am running into problems identifying when the fill handle is being used. Is there a cutcopy mode or other easy way to identify this? EggHeadCafe - Software Developer Portal of Choice WCF Workflow Services Using External Data Exchange http://www.eggheadcafe.com/tutorials/aspnet/3d49fa0d-a120-4977-842a-6dafb17b6d74/wcf-workflow-services-usi.aspx If you're trying to replicate dragging on the fill handle, you can look at ..filldown, .fillup, .fillright, .fillleft methods in VBA's help. If ...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Parameters And Null Values
I have a query that I wish to return ALL values between 2 dates AND any records that the date happens to be null I have used the following: WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And [Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null)) And the result is ONLY the Null values. WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And [Forms]![ViewReports]![DateTo] And (Schedule.DateSubmitted) Is Null)) And the result is Nothing...an empty set. What do I need to do so that it will show me all records betwe...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Searching for values
I would like to create a report for the following: 1 - Read a worksheet with data like the following: C1 C2 R 1 - John Smith A R 2 - John Smith A R 3 - John Smith A R4 - Bob Jones 2 R5 - Bob Jones 1 R6 - Bob Jones 12 2 - Produce a report for every occurrence of John Smith: C1 C2 R 1 - John Smith A R 2 - John Smith A R 3 - John Smi...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

Functions #2
Sir or Madam, Please send me list of formula definations llike sum = sum of two or more date example :- if i wanna to add two or more row or coloums then i can use it press shift+f3 and choose sum funcation and select range then ok. then we will get total value of selected coloum and rows. In this type i need other functions definations. Sandeep Gupta -- SANDEEP GUPTA ------------------------------------------------------------------------ SANDEEP GUPTA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29964 View this thread: http://www.excelforum.com/showth...

how to add a horizontal benchmark value line in a column chart?
I need to create a vertical column chart with a horizontal benchmark line to show where the expected value is. Is this possible in Excel? Hi, You will find examples of adding datum lines to charts here, including links to other site examples. http://www.andypope.info/charts/averageline.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Mike Chro" <Mike Chro@discussions.microsoft.com> wrote in message news:C17CFDA2-A8D5-46D9-81E9-2CC2C4D54C4E@microsoft.com... >I need to create a vertical column chart with a horizontal benchmark line >to...