+ Reply to Thread
Results 1 to 2 of 2

Copying + pasting special a dynamic range of cells through a macro

  1. #1
    Forum Contributor
    Join Date
    03-21-2007
    Posts
    118

    Copying + pasting special a dynamic range of cells through a macro

    Thanks to SolnaJeff I have the following macro that I use for a worksheet:

    Please Login or Register  to view this content.
    The purpose of it is to auto-fill the formulas on one table according to the raw data I copy/paste to another table (in the same worksheet).

    So I went on to assign this macro to a button so that I do the following:

    a) Create my data table by copying pasting all the raw data I need
    b) Click on my button to have my other table get autofilled with the formulas I have put in the first row (the macro auto-fills the rows under the one row that contains all my formulas).

    All this is happening into one worksheet. Now my next problem is that I have far too many rows which means equally far too many cells autofilled with formulas. Unfortunately Excel 2002 that I am using has the defect of only 64 RAM that can handle within a document. This results to error messages and corrupted files after a few dozen thousand rows full of formulas.

    The way to solve this problem (as I have figured it out) is the following:

    Suppose that my original table with the first row with the formulas is at K5-R5 and I have rows from K6-R10000 autofilled with the macro. Then I select the cells from K6-R10000, copy->paste special->paste values. This way I replace all the autofilled cells with their hardcoded values instead of formulas, which clears the available memory for excel to use it in the next worksheet.

    Is there any way to do this with the same macro (or an extra one?). My problem is that the number of rows that are autofilled is not specific and can vary dramatically. So I would want an addition to the macro above that after it has autofilled the rows/cells according to the table of raw data, it then proceeds on doing a copy->paste special->paste value to all the cells it has autofilled.

    Is that possible?

    Also, another small bit, is it possible that when the original formula autofills the rows below it does NOT copy the visual format of the row above (borders, colors etc) ?

    If I am not making sense please let me know and I will try to become more clear.
    Last edited by kostas; 06-25-2007 at 05:36 AM.

  2. #2
    Forum Contributor
    Join Date
    03-21-2007
    Posts
    118
    I have figured out the following solution (note: the starting cell where the past special starts from is known and is always Q11).

    Please Login or Register  to view this content.
    Seems to be working ok. If anyone has any other tip to make it better let me know.

    Also I would like some help on avoiding carrying on the visual formatting of the first row in the autofilled ones. I do not want to have the borders/colors copied down.

    Any ideas?

    Cheers,

    Kostas

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1