Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Command Button To Copy Then Paste Special

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have created a daily log to input data. From a drop down menu I select the week, and that data is iput into a grouped spreadsheet that tracks each week for a year. The formula I used is linked to the cell from the drop down menu so whatever the selection, that week is input.
The only way I know of to circumvent this is to copy the data in the grouped spreadsheet and paste special into the same space so when the selected week changes, that information stays.

This leads to my question, how can I create a command button to copy D:3 to O:9 and then paste special the values back into the same area?

Thanks for your help guys.

View Answers     

Similar Excel Tutorials

Quickly Switch the Columns and Rows of a Data Set in Excel
How to switch a data set in Excel so that the columns become rows and the rows become columns. This will save you ...
Make Negative Numbers Positive in Excel and Vice Versa
I will show you a few ways to change negative numbers to positive numbers and back again in Excel. Don't forget to ...
Convert Numbers Stored as Text to Numbers in Excel
I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situation often happens when you ...
Copy one range and paste in another range
Below is a macro, just copy and paste it into a module in your workbook and go from there.This macro uses message b ...

Helpful Excel Macros

Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a

Similar Topics

I'm using a data verification that requires using the paste special command. When I first got this thing set up, I would select Paste Special then values (radio button on the right side of the paste special window), but NOW when I use paste special, it comes up with 3 options in a box: 1.) HTML, 2.) Unicode Text, and 3.) Text. How did the paste special box get changed and can/how do I change it back? Any help would be appreciated. Thanks

Ok, I have read and read and can't seem to come up with a work around for my problem. Here is the situation:

I have a workbook that contains 52 worksheets (one for each week of the year). Each of these worksheets have the exact same format (day of the week with data based on daily intervals).

What I am attempting to do is circumvent the 30 limit in a formula. I need to take the value in say cell B4 from each worksheet so as to build and everage for the year (this is a call center environment that I am trying to determine the average number of calls per dat per interval).

What I thought it could look like is this:

=AVERAGE('Week 1'!B4,'Week 2'!B4,'Week 3'!B4,'Week 4'!B4,'Week 5'!B4,'Week 6'!B4,'Week 7'!B4,'Week 8'!B4,'Week 9'!B4,'Week 10'!B4,'Week 11'!B4,'Week 12'!B4,'Week 13'!B4,'Week 14'!B4,'Week 15'!B4,'Week 16'!B4,'Week 17'!B4,'Week 18'!B4,'Week 19'!B4,'Week 20'!B4,'Week 21'!B4,'Week 22'!B4,'Week 23'!B4,'Week 24'!B4,'Week 25'!B4,'Week 26'!B4,'Week 27'!B4,'Week 28'!B4,'Week 29'!B4, 'Week 30'!B4,'Week 31'!B4,'Week 32'!B4,'Week 33'!B4,'Week 34'!B4,'Week 35'!B4,'Week 36'!B4,'Week 37'!B4,'Week 38'!B4,'Week 39'!B4,'Week 40'!B4,'Week 41'!B4,'Week 42'!B4,'Week 43'!B4,'Week 44'!B4,'Week 45'!B4,'Week 46'!B4,'Week 47'!B4,'Week 48'!B4,'Week 49'!B4,'Week 50'!B4,'Week 51'!B4,'Week 52'!B4)

But there are more then 30. Any ideas how I can get around this????????? Or if there is a better option available to me (I do however have to keep the data in weekly worksheets)

Thank you in advance.

I haven't found a solution to this in this forum but if there is one posted, my apologies.

I have a drop down menu (via validation/list) with app. 10 values. The corresponding list has the name "Plants".

I'd like to create a macro that uses the 10 values one after the other. For example, I'd like to run the workbook with value 1, then copy and paste special certain results, which are on a different worksheet. I'd then like to run the same workbook using the second value in the drop down menu, then copy and paste special the results etc. until I have the results for all 10 values of the drop down menu.

I tried to record this but it seems that the recorded macro simply uses whatever value the drop down menu is set to at the time I run the macro, and does not change the selection to the other values. In other words, I get ten results that are all identical because they all are calculated with whatever the drop down menu was showing at the time the macro is executed.

How can run the macro using all ten values of the drop down menu one after the other?


I have a file that is updated daily. Part of the update is to copy $A$2:$N$25 from sheet1 and paste special values to A10 in sheet2, A10 being the first day of the first week of the month. The next day same thing only, paste special values to A34 in Sheet2, A34 being day two of the first week of the month...A58...A82 etc.

How can I create a macro to perform this through the 'Tools' menu?

The catch is that at the end of the fiscal month, which is the last friday of every month, I have a macro that prints the current months charts and data to keep as a record then clears all that data to collect the next months data.

Can the first macro be modified to more or less start over at the end of the month after the data is cleared out?


Hi guys

this could be very simple or impossible basically i wish to fill this series for 104 weeks so you can see it is taking much time to just copy and paste anyone know a quicker way?? Thanks

To clarify the Week 11 is in Column A and T1 is in column B

Week 11 T1 Week 11 T2 Week 11 T3 Week 11 T4 Week 11 T5 Week 11 T6 Week 11 T7 Week 11 T8 Week 11 T9 Week 12 T1 Week 12 T2 Week 12 T3 Week 12 T4 Week 12 T5 Week 12 T6 Week 12 T7 Week 12 T8 Week 12 T9 Week 13 T1 Week 13 T2 Week 13 T3 Week 13 T4 Week 13 T5 Week 13 T6 Week 13 T7 Week 13 T8 Week 13 T9

Hi to anyone who may be able to help,

I am trying to produce a wages history page to calculate accruals and YTD's. I have allocated each pay week a line and the weekly figures are sent from payslip sheet. I plan to copy the previous week's line into the current week to pass on the formulas and formats, then copy and paste special the previous week back over itself to archive and protect the values and formats only.

I don't know how to make this copy and paste the NEXT line, or perhaps select a cell/cellgroup to have it relate to with each run.

If I make sense to anybody, could you please respond - my L-plates are dragging dangerously low to the ground...

Good Evening --

I have Office XP - I use Paste Special frequently.
Paste Special is in my Drop Down Edit Menu but is NOT in my Right Click Pop up Munu. Just "Paste" Appears there.

A Friend also has Office XP and His is in the right click Menu. It was on the computer when he bought it and had no idea how / why paste special is in the Right Click Menu - He has always just taken in for granted.

I have read the Paste Special Threads here - and Searched Excel Help and find no reference to my quest.

Does Anyone know how to put it there ?


I have several different spreadsheets, which are continually being updated.
One thing i do is to copy 4 columns from each row of data, then paste special values only to another sheet.

This works fine most times, but annoyingly sometimes instead of getting the usual paste special menu, i am given a different menu for paste special which includes paste as unicode,text etc etc. whichever option i choose it then posts the entire row of data which contains over 60 columns instead of just the 4 i copied??

Why does this happen, how can i avoid this please?
Many thanks

In Excel 2007, Service Pack 1, fully patched, Paste Special is inconsistent when pasting from one XLSX spreadsheet to another.

a) Copy range of cells from spreadsheet A to spreadsheet B, click Paste Special, and the Paste Special menu comes up with 'bullets' style menu
(Paste: All, Formulas, Values, Formats, Comments, Validation, etc.)
(Operation: None, Add, Subtract, Multiply, Divied)
(3 buttons across the bottom (Paste Link / OK / Cancel))

Other times:
b) Copy range of cells from spreadsheet A to spreadsheet B, click Paste Special, and the Paste Special menu comes up with 'scroll bar' style
(Radio buttons: Paste / Paste Link)
(Combo box: Microsoft Office Excel Worksheet Object, Picture (Enhanced Metafile), Bitmap, etc.)
(2 buttons on the bottom (OK / Cancel))

Both dialog boxes say "Paste Special" at the top, but their function is different.

This is pretty confusing. Can anyone explain why there are two different dialog boxes with the same name? Better yet, we'd just like the 'bullets' style one that contains "Paste Values". Thanks for any help you can offer!

I have a spreadsheet with 6 rows (week1 - week 6), and need to enter the data in the correct row by selected week. I have a command button that opens a combobox form with the 6 "week" selections, and when one is selected the 2nd userform opens. It contains many text boxes, and when a "submit" command button is clicked, it transposes the data into the corresponding row. Problem is, I have no idea how to make the data copy into the row (week) that I initially select, I can only copy into the specific range that I write in the code. Any suggestions? 12 worksheets, one for each month, so "activesheet" will have to be included codes I think. I will happily email a rough example to anybody who can help.........


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.

I have a spreadsheet where sometimes people copy information over from a
previous worksheet. Problem is it sometimes stops the formulas working
because it gives a bad reference. Is there any way of programming so that
when the spreadsheet is open and the user goes to copy and paste that when
they paste it does a paste special and only paste the values without them
haveing to go into paste special?

Hi All

Can anyone help?

Im new to userforms but i think this is the best way to approach this?....

I have a spreadsheet which has data dumped into it on a daily basis. The key feature of this spreadsheet is the column containing a week no (1-52)

Because of the amount of data being stored each week What I would like to do is create a user form where the user could select a Week No from a dropdown list, or type it into an input box and then click a command button which would send all the data for the required week into a seperate sheet.

Is this possible? If so could you possibly help point me in the right direction?

I'm having a problem with the Paste Special functionality. Basically, I want to copy and paste special (values). But when I get to the paste special screen, I get a dialog box that tells me to paste as object. For some reason it thinks I've copied an object when I copied a cell or a range of cells.

This happens only across workbooks. I can paste special (values) just fine from within the same workbook.

Anybody else saw this problem before? I'm suspecting this problem could be related to last week when I tweaked Excel to be able to open two different Windows side-by-side with these instructions:

Any help would be much appreciated!

I want them to be able to copy and paste cell values without it also copying and pasting the validation. This is because I have different validation settings for every single cell, and don't want to change those everytime I copy and paste. I know that you can use paste special to paste values without validations, but that will get tedious, and it doesn't seem to work anyway - when I paste special the value then its as if the paste special overrides whatever validation criteria I was using. Is there a setting or a trick to be able to paste values only all the time without using paste special, AND when I paste values only I dont want it to override the validation I have set? For example, if I have validation set for only allowing numbers between 0 and 2 and input 54 manually an error message occurs of course, but if I copy and paste special the number 54 into the cell it then allows that to happen despite setting validation to only allow numbers between 0 and 2, and despite pasting special values only.

Question: How do I copy/paste just the "header" rows of a data table that has been grouped/outlined using the GROUP functionality?

Seems like a straightforward question, but I could not figure out the solution. When I collapse the outline and select Copy/Paste (or Paste Special Values) it pastes the entire data set, and I just want the Level 1 rows to be pasted.

Can you help?

Morning all,

I hope I can explain this properly!

I have a worksheet that I'd like to update automatically each week. The sheet contains 52 columns and various rows (the columns relate to each week of the year) and each cell is linked to a file that lives in a fixed location.

This file is replaced by a new one each week, called the same so the links still work - it just has the latest week's data.

How do I stop historic data from changing when the source file changes? For example, week 1's data is linked properly but when the file is updated, week 2's data is correct but week 1's changes as well......which I know is the point of links and I know I could go through and Paste Special/Values each time to "lock" the data but I'd really like to be able to keep the links in place!

Have I made any sense?!

All replies appreciated.

I have weekly data on one tab, and daily on the other. On my weekly tab, I have a basic sum formula setup to grab the corresponding seven days. My challenge is how to create a sum formula on my weekly tab that I can drag down the other 51 rows.

Week 1 =sum(e3:39)
Day 1 Week 2 =sum(e10:e16)
Day 2 Week 3

Day 3 Week 4

Day 4 Week 5

Day 5 Week 6

Day 6 Week 7

Day 7 Week 8

Day 8 Week 9

Day 9 Week 10

Day 10 Week 11

Day 11 Week 12

Day 12 Week 13

Day 13 Week 14

Day 14 Week 15

Day 15 Week 16

Day 16


We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!

Hey Guys, First time post, Not sure what is going on, Happens all the time but usually just drop it and try something else...

I am trying to copy a range of cells and Need to transpose the values. Easy Enough? When I right-click and paste special, it gives me a new window with an array of options but not the standard Paste special value screen. I have used the others in the past and just pasted text. However this does not give me the paste transpose option which is what I need.

I am sure this is a User error where there is something about the way that I am copying this information but I am Drawing a Blank... Please help guys

Is it possible to execute (force) a paste special values command (by disabling or replacing paste commands with paste special values) when a user attempts to paste (via toolbar button, edit > paste, right click paste & ctrl-V) for the specific destination worksheet (specific active sheet).
To insure destination cell/s formatting and data validation are not replaced by that of the source.

I'm trying to do a copy and paste (special - values only) between 2
workbooks. I select the cells in one and click the copy button (also have
tried Ctrl-c), but when I switch to the other WB and select the arrow beside
the Paste button, the top options (Formulas, Values, etc) are grayed out. If
I select Paste Special (the bottom option), it gives me a box asking if I
want to paste the data as a Excel Worksheet Object, Picture, Bitmap, ...

If I try to paste in the same workbook (where I am copying from), the
Formulas, Values, etc. options are available.

I have each workbook open in separate Excel sessions - I assume this may be
causing the problem. But why does it do that?

Bill @ UAMS

Hi There!
I know how to use the copy and paste special to copy a formula from one row down many rows in that same column.

When I do this the formula will automatically change by 1 number
{such as =IF(ISBLANK(C5),"",VLOOKUP($C5,Sheet1!$B$3:D4891,3,FALSE)) then when I click on copy and pasted special each following row will change by one number ---- to =IF(ISBLANK(C6),"",VLOOKUP($C6,Sheet1!$B$3:D4892,3,FALSE))

Can this also be done if the row you want to copy and paste is a row with data validation / a drop down menu?
I simply tried using the same process for a row that has data validation the row with the validation has for the source =INDIRECT($B$5)

Is there a way to copy this down about 20 rows and have the ($B$5) change in each row by one number {such as ($B$6 THEN $B$7, ETC...)
Or do I just have to do the whole data validation process for each row?


I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste.
- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,

Hi guys,

I need to copy /rename sheets in two workbooks.

Workbook 1:

I have a special sheet I use monday to saturday year long. I need to copy sheet 1 into approx 300 sheets and name them:

1 jan
3 jan
4 jan
5 jan
6 jan
7 jan
8 jan
10 jan
11 jan
etc I will need to put the sheet name into one cell on the sheet . B2

Workbook 2:
Copy sheet 1 to sheets named:

week 1
week 2
week 3
week 4
etc to 52

Within each week sheet i need 6 cells to be filled with relevant dates, that is for Week 1, I need 6 column headings starting with 3-8 of january (monday to saturday of week 1) and so on.