Select method class range failed

Hi,
I have created a spreadsheet that is 38mb and needed to size it down. I recorded the following macro to do this. This essentialy jut copies the top line of the relevant columns on each page and then copies the formulas down, calculates, and then pastes just the values of these calculations, thereby reducing the sheet to a third of its size. The macro works if I run it from the macro option on the toolbar, but when I assign it to a command button I keep getting ' select method class range failed'.
Can anyone help???

Please Login or Register  to view this content.

I could not replicate the problem

here's a shorter version of the macro, that has gotten rid of all the selects

Please Login or Register  to view this content.

Dave,

Would you not use somthing like

Please Login or Register  to view this content.

or he wants only wants to paste special formulas in a range

Please Login or Register  to view this content.

Also this link may help reduce the size of your file

http://www.contextures.com/xlfaqApp.html#Unused

http://www.decisionmodels.com/optspeedb.htm

VBA Noob

Originally Posted by VBA Noob

Dave,

Would you not use somthing like

Please Login or Register  to view this content.

or he wants only wants to paste special formulas in a range

Please Login or Register  to view this content.

Also this link may help reduce the size of your file

http://www.contextures.com/xlfaqApp.html#Unused

http://www.decisionmodels.com/optspeedb.htm

VBA Noob

Certainly that would work....
The poster is in the beginning stage of learning VBA and thought
copy- destination would be a good code to understand the first time

Hi phillb,
Try and attach a sample of the workbook, so we can see if there is a pattern, maybe you are trying to find the first empty row or something in that area,
Quite unsure as to what you are tying to accomplish

If the Command Button is from the Contol Toolbox toolbar it could be because the property setting for the TakeFocusOnClick is set to True (This is the the default setting).
This can cause a problem when you try & paste even when you use commands to select cells before pasting


To change this setting

Open Control Toolbox Toolbar

Click on the Triangle shaped Icon (This will put you in Edit mode)
Right Click on the button
Select Properties
Change TakeFocusOnClick setting to False
Click on the Triangle shaped Icon (This will take you out of Edit mode)

Can you post the book on a file sharing website like rapidshare.com & then provide a link to it?

Originally Posted by phillb

I can't send an example. even with nearly everything taken out and zipped it is still too large.

The changing of the button in design view didn't work, and I am still getting the error when it moves onto sheet maintenance select line

did you not try the code I sent you?
you should have no selects!!!

Originally Posted by phillb

I have just tried it. it seems to do some quite weird things. It seems to delete on the first sheet the formulae in cell c4 and d4. It then seems to copy cells M3:R3 and also T3:W3. I can't see any reason for this as all of the formulae in the macro you wrote point to row 4.

That is strange, considering it's your macro, just without the selects, but if you look at it you should be able to figure out how to adjust the code