Teach Yourself Excel Lesson Copy And Paste Special Values

Hi,

I am completely new to excel programming. I have a SQL DTS which
creates an excel file and exports data into it. The created excel file
could have variable number of work sheets.

Text data in the excel file had a leading single quote character and
spaces before the data. I can get rid of these by copying entire sheet
and paste special--values into a new sheet.

Would some one tell me how to automate to create a new excel file and
copy and paste special (with values option) all the work sheets into
the new file? I appreciate any help.

Hi all,

This had me scratching my head: at work, we're using a large-ish .xlsx file, with about 20K rows, with a lot of look-ups and so on. A messy job, but it's being kept in use due to, well, user reluctance to switch to an Access solution. You know the drill.

There is a manual operation where cells from one tab are copied and then Paste/Special/Values into another tab of the same workbook. There are three columns and about 20 thousand rows involved.

Until recently, we were using Excel 2007 and, on a stock PC, it took about 10-15 seconds for the paste operation to complete. Automatic recalculation is turned off, as there is a LOT of VLOOKUPs -- about 25 columns times 20 thousand rows.

Anyway, since our company upgraded to Excel 2010, the same paste operation now takes anywhere between 40 and 60 MINUTES. Automatic calculation is still turned off. I have no clue why is this happening. The same file, on a slower machine (2-year old HP Mini) under Excel 2007, performs the same operation in about 10 seconds.

Any clues? I am truly lost for ideas.

Cheers!

Hello

This is probably relatively easy but I can't find the relevant instructions on the net, can't even get past assigning macros to buttons as there is no 'assign macro' option when I right click on the command button (editing enabled).

Anyway... I am looking to make a button that when pressed will select the data in a range of cells, copy it, and then paste special > values that data into the same range.

If I can, I'd like help making one button which, when pressed, selects the data to the right of the highlighted cell. If this is too difficult, a button for each row would suffice.

If anyone can point me in the right direction it would be much appreciated. The most I've worked out is a macro that, when run, copies and pastes the data in a specific range... but this isn't based on what cell is selected.

The purpose of this is to make data entry easier, I'm creating a data entry sheet for traffic surveys and having to copy a range of cells, then paste special values it back into the same range might be beyond some of those who will use the data entry sheet, and so a button to do this would make life easier.

Attached are the 2 data entry sheets we currently have finished.

Thanks in advance for any pointers, I'll keep googling in the mean time and trying to learn the basics of VBA.

Hi

Is it possible to replace formulae with a paste special values while the cells are in a filtered list? I've tried using visible cells only first, but get the error message 'That command cannot be used on multiple selections'.

Thanks for any help in advance.

RG19

Does anyone know of a way to replace 'Copy-Paste' with 'Copy- Paste Special -Paste Values'?

Ideally, 'Copy-Paste' shoud be disabled and 'Copy- Paste Special -Paste Values' would be the default option.

Is this even do-able?

Any ideas?

hi i run a macro and when i have this macro run i have to copy and paste special values the 3 sheets of my .xls file. Is there any way to include the latter copy and paste special values routine in the macro itself.

I have formulas in 4 columns

D,E,F & G

For column 'F' I want to have a macro that willl copy and paste speacial values on click of a button. I tried recording a macro but it copy and paste special on all the columns with a formula and not just column 'F'

Does anybody know the vba code to just copy paste special values on column F:F

Thanks in advance for you help

Hello

I'm using the following code to copy blocks of data, equal column size across yet different row sizes down into a central table.

Yet when I copy the TableWest I would like PasteSpecial Values to pasted, not copied. I tried replicating the code from above, whilst using the "find last cell used" yet can't figure it out.

Any suggestions? Also, any suggestion on a more efficient way of copying would be appreciated.

Cheers Code:

'Copy TableEast data
    Sheets("TableEast").Select
    Range("T23:AD23").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("TableTotal").Select
    Range("A22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         
'Copy TableWestl data
    Sheets("TableWest").Select
    Range("AL23:AV23").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Worksheets("TableTotal").Cells(Worksheets("TableTotal") _
    .Rows.Count, "A").End(xlUp)

Using Excel 2010

In A1:A5 sits =Randbetween(40,100).
I then copy A1:A5 to the clipboard.
I then paste A1:A5 to c1 using Paste Special/Values Only.
In C1:C5, the random selections from A1:A5 have changed (likely due to the auto recalc).
However, I seem to recall that in previous times the random selections from A1:A5 were "frozen" and not subject to change in C1:C5 given the Paste Special/Values Only.

Feedback Appreciated.

Does anyone know code to hard code values with formulas in a range