Email:      Pass:    Pass?
Close Window   
TE
Free Excel Tips in Your Email!
Join Over 20,000 Happy Subscribers!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

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 Video Tutorials

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







Need help making macro that will copy the important data and special paste it in certain cell based on drop down list that i created week 1-4.

still learning macros but anyone able to show me basically what i need to know to use if statement inside macro based on week 1 - 4 and how to do copy and special paste, into certain area depending on it being week 1-4.


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.


Hi,
I'm pretty sure I require a macro for this one - but am a TOTAL novice with Visual Basic so if you could help it would be really appreciated,
I have a spreadsheet which details the top ten, and bottom ten performers in my team on a weekly basis. I have a seperate tab which I have to copy & paste special the information into each week, to keep a weekly record of the best/worst performers. This second tab is set up as follows:

Week 21 (cells B2:E2) Week 22 (cells G2:I2) etc going across the top of the page.

I copy and paste special the small tables with the employee data into the cells immediately below these.
I can record a macro that copies and paste specials the information into the sheet - but I don't know how to make the data paste into the specific column relative to that week (sorry if that explanation isnt particularly clear)
If you could point me in the right direction with some code it would be great,

Many Thanks
Rob


Paste special "formula only" does not seem to work.
When copying a column that contains data content for a formula and the formula it self to a new column, the paste special function pastes the formula and the data to the new location. I expect the paste special function bring the formula only and set the data to zero.
The scenario is tracking staff hours. Seven days of the week run to a sum of that week for 52 weeks which then sums the weekly summary for an annual total.
Begin a new year or have a new hire, copy and paste of column leaves the original employees recorded time.
Thoughts? or is this a bug?
Thanks


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?

Thanks.


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?

Thanks,
Brian


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 ?


Thanks


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


Good Afternoon,

I've got a formula driven data from left to right by week. As below

24-Jan 31-Jan 07-Feb 14-Feb 19.52 19.61 20.06 20.06 7.66 7.56 7.53 7.53 0.32 0.32 0.32 0.32

At the moment, Other week beyond 07th of Feb is hidden and before I unhide the following week. I usually copy&paste special the last column....What I'd like is a VBA code to do this. Could anyone help.

Thanks,
Ade


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

Sometimes:
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.........


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.


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?



I have a report in which I need to copy information from one file to another. I copy the row or column and then Paste Special the information in my other file selecting Values and Transpose each time. The information in the rows and columns is not consistent; some are longer or shorter than others. A while ago, I created a custom button to Paste Special Values Only, but I don't remember how I did it. I can't write macros so I am not exactly sure how I did it. When I look at the macros that are active in my file, none of them relate to Paste Special Values Only. Is there a way to create a button without creating a macro? If not, how would I write a macro to create Paste Special Values and Transpose? Thanks


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:
http://ericappel.net/blog/2008/03/17...ideBySide.aspx

Any help would be much appreciated!


I get a worksheet each week with about 27 pages of data. I currently select a range of cells, copy, then paste to another sheet, which outputs some results, then repeat about 100 times.

I have created a button on sheet2, I would like to manually select the range of cells, then click this button and have it paste the selected cells to sheet1!F3.

Thank you for your assistance. This would save me about an hour each week.


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?
Thanks!
JavaJoe




I am back again. I need more help. (By the way, I love this site). I need help with something I'm sure if very simple to many of you. I need to create a macro to copy a selection of data from an existing excel spreadsheet and copy it over to a new excel workbook keeping all of the same formatting (including column widths, etc.). I have most of it when I "recorded" a macro that simply made a copy of the spreadsheet and then moved it into a new workbook, which on the most part works, but the new worksheet has vlookups and command buttons on it that I do not need. I just the selected area that contains the data. I then recorded the macro to copy and special paste to remove the vlookup links, which I believe works, then I selected the data that I do not want and deleted it and I had to go into the Developer tab and activate "Design Mode" so I could delete the two (2) command buttons, then deactive Design Mode. That is pretty much how I recorded my macro. When I run it, it mostly works--it copies the entire page over to a new workbook, I believe it does the special paste for the selected data area that I want to keep, but then I get an "ERROR 1004" where it will not delete the selected area that I do not want nor the command buttons. I am sure it is something very simple that I am missing. It actually seems that this ERROR 1004 is coming up in the new workbook that is created, possibly because of the two command buttons that are being moved to the new workbook.

Here is the code that I have so far.



Please Login or Register  to view this content.



I have created a timesheet in Excel, with meal breaks, end times and total hours worked for a two week period. The formula =SUM(INT(X10)+Z10) calculates correctely in day 1 of week 1 and through the first week. When I copy the formula to week 2, it does not calculate. I have even entered the formula again, but it does not work. I even tried putting it on a new sheet. I also tried doing a paste special. I need help to complete this timesheet.


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.