Excel Paste Special Help

[frog40]

I have Win Vista and Office 2010. I have an Excel Workbook that has several worksheets. These worksheets have several cells that are linked to each other. I have one cell that needs to be YTD. The operator of this worksheet "zeros" out the main data at the end of each month. New data is entered in the new month, but the YTD data is wiped out because it is linked to the previous months worksheet. I know I can copy, Paste Special and Paste Values. This saves the YTD number and can be carried forward to the next month.
I would like to be able to have this Copy, Paste Special, Paste Values to be performed automatically. Can you tell me how I can do this?

 

[xlbo]

You would need to have some trigger that performs the code - whether that is a button click or an "event" within the workbook.

Best bet is probably to have it as a button click - your code would be something like

Range("A10").copy
Range("A10").pastespecial xlpastespecialvalues

You would create a macro, add a button to the sheet and then link the macro to the button

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

It is a common mistake for novice spreadsheet users to chop their data up into sheets. You did it that way, because it is the way that you want to see it. (BTW, THAT way is usually a REPORT) That's fine, but it is a horrible way to be able to analyze and report on ALL your data. Your question reflects that shortcoming.

ALL your data ought to be in ONE table in ONE sheet. From such a table, you can very simply calculate YTD. From such a table, you can very simply report any day, week, month, quarter.

Consolidate ALL your monthly data into ONE SHEET! that will solve 95% of your problems.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[frog40]

I appreciate your help, but unfortunately this is totally new territory for me. I did manage to create the macro, but I need "elementary" details on creating a button or an event and then linking the two.

 

[xlbo]

To "Create" a button, simply go to View>Toolbars>Forms - this will ensure the forms toolbar is visible

From there, click on the button and then drop it onto the worksheet. When you do that it will ask you to assign a macro to it - simply choose your macro from the list

your macro should look something like:

Sub DoThePasteSpecialForMe()

Range("A10").copy
Range("A10").pastespecial xlpastespecialvalues

End Sub

You would click on the equivalent of "DoThePasteSpecialForMe" when prompted to assign a macro to the button

That said, I agree completely with Skip. The most common mistake I come across when people start using excel is to mix up data storage and data reporting. Ideally, your data storage should NOT be your reporting so as Skip says, store ALL your data on one sheet with dates as identifiers then REPORT on it on a seperate sheet - this actually tends to reduce file size and complexity as you have many less sheets and formulae to contend with.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[frog40]

Thank you. It now works very well. I will look at setting this up as a real database. It has just been this way for years and the operator is not open to changing. However, I do understand what you are saying and I will look into doing this change again.

Thanks again for your help