A tester for the experts!

[Sykovampyr]

Hi Guys and Gals! A good two part question! If any one could help i would be in debted! I am looking to create a button to copy the values from one set of cells from all sheets in a work book to another set of cells in the same workbook. For example copy the values from E4-E39 from sheets 1-15 and place just the Vaules in cells B4-B39 in the respective sheets. Once it has paste the values i would then like it to clear the cells E4-E39 of Values.

Thean in addition to this i would like to create another button that copies from G4-G39 to B4-b3- and clears C4-C39...............

any ideas or can someone point me in the right Direction??

 

[RichardMGreen]

Are the sheets named something specific or have you left them as the default Excel names (Sheet1, Sheet2, etc)?
Also, do you have any "header" sheets, i.e. and sheets that should be excluded from the copy/paste routine?

 

[Boller]

Dim ws As Worksheet
For Each ws In Worksheets
    ws.[B4:B39] = ws.[E4:E39].Value
    ws.[E4:E39].ClearContents
Next

And similarly for the second bit of your question.

 

[Sykovampyr]

The sheets are are called something different but for examples sake we could call them the defult! there are Three sheets that would need to be excluded and 6 that require smaller formulas.................... If it is easier i could do a button per sheet..............................


How would i put the code into button format?!

All confusing!

 

[jose001]

Right click on Toolbar and select forms. Select button and draw one on your sheet. The macro dialog should appear, type a name for it and select new, copy and paste code!

Jose

 

[Sykovampyr]

Thanks Jose! And Thanks Boller for the code, will have a play and see what i can do .......................