+ Reply to Thread
Results 1 to 9 of 9

Removing formulas in all sheets by Paste/Special

  1. 10-03-2008, 02:25 PM #1
    kapiszon2929
    kapiszon2929 is offline
    Registered User
    Join Date
    03-06-2008
    Posts
    8

    Removing formulas in all sheets by Paste/Special

    Hi,

    I have written a short piece of code based on some other posts and for some reason it doesn't work and it's driving me mad. The task seems to be very easy - I'm trying to get rid of some formulas in all worksheets and keep the actual values only. The part that is failing is the following: ws.Range("A1:P31").Select

    Please Login or Register  to view this content.
    When I take out "ws" from ws.Range("A1:P31").Select then the code works fine, but it doesn't loop through all worksheets - it executes 78 times the code on the active worksheet....

    Any ideas on how to fix it and why this happens?
    Last edited by kapiszon2929; 10-06-2008 at 05:54 PM.
    Reply With QuoteRegister To Reply
  2. 10-03-2008, 02:32 PM #2
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    This is some code written by Aaron Blood
    Please Login or Register  to view this content.
    Adapt it for your use
    Please Login or Register  to view this content.
    Last edited by royUK; 10-03-2008 at 02:35 PM.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  3. 10-03-2008, 06:04 PM #3
    Mallycat
    Mallycat is offline
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    select you sheet first, then your cell range
    Reply With QuoteRegister To Reply
  4. 10-04-2008, 06:32 AM #4
    kapiszon2929
    kapiszon2929 is offline
    Registered User
    Join Date
    03-06-2008
    Posts
    8
    It doesn't let me execute this macro, with all sheets selected, I can't use the option Paste/Special. I've already tried to do it before even without any macro, simply by selecting all worksheets, copying the range and pasting it, but for some reason the button Paste/Special is locked, I can't do it. The same situation seems to apply to the macro you suggested...

    The macro fails in the following code line:
    Selection.PasteSpecial Paste:=xlPasteValues
    Reply With QuoteRegister To Reply
  5. 10-04-2008, 06:41 AM #5
    MatrixMan
    MatrixMan is offline
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    I've tested this with no problem ... perhaps it's something else in your code? Post your file & I'll take a look ...
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.
    Reply With QuoteRegister To Reply
  6. 10-04-2008, 06:47 AM #6
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Is the range Locked and Protected?
    Reply With QuoteRegister To Reply
  7. 10-04-2008, 06:52 AM #7
    kapiszon2929
    kapiszon2929 is offline
    Registered User
    Join Date
    03-06-2008
    Posts
    8
    All right, I've just found the cause - in each worksheet I was using tables (Excel 2007, in old Excel 2003 they were called lists) and these tables were blocking mine macro from executing... When I got rid of them and converted them to normal ranges, the macro worked fine...

    So the conclusion is in the future not to use tables when working with multiple sheets at once. Now I'm gonna need some macro to convert all tables to ranges in the whole workbook... Do you have anything that might come in handy?
    Reply With QuoteRegister To Reply
  8. 10-04-2008, 07:21 AM #8
    kapiszon2929
    kapiszon2929 is offline
    Registered User
    Join Date
    03-06-2008
    Posts
    8
    I've got it, it worked fine for me.

    Please Login or Register  to view this content.
    Last edited by royUK; 10-04-2008 at 09:01 AM.
    Reply With QuoteRegister To Reply
  9. 10-04-2008, 08:59 AM #9
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Please read the Forum Rules & use Code Tags when posting code.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with code that creates formulas on new sheets
    By westonkw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2008, 10:41 AM
  2. Nested formula's, data from 2 sheets.....
    By gurj_sandhu in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2008, 10:41 AM
  3. Using cell names on multiple sheets for formulas
    By cardigans1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-21-2008, 12:34 PM
  4. Combining 2 sheets into a 3rd and removing duplicate values
    By Muttly in forum Excel General
    Replies: 7
    Last Post: 06-17-2007, 04:23 PM
  5. combining multiple sheets & removing blank lines
    By rosemwd in forum Excel General
    Replies: 0
    Last Post: 12-04-2006, 12:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1