+ Reply to Thread
Results 1 to 7 of 7

Macro to copy down formulas adjacent to data.

  1. #1
    Registered User
    Join Date
    03-12-2008
    Posts
    5

    Macro to copy down formulas adjacent to data.

    Hi.

    I have some data in columns A to D and formulas in cells E to G.

    At the moment my macro just copies down the formula from E to G to about 500 so I know I capture all the rows in A to D. not ideal.

    The problem is when I refresh my data in A to D new rows are added but the formula does not copy down all the way automatically so that's why i set up a macro to cover this. However it seems pointless and a waste of disk space to copy down so much when its not need.

    I have about 15 workbooks with about 8 sheets in each that need updating like this so all the excess formulas takes up a bit of disk space.

    So basically i want my macro to only copy down the formula to only rows where there is data. Like when you do 'CTRL+down arrow' but only that doesn't record properly in a macro.

    Hope this makes sense

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Hi,

    You can get the number of filled rows (as if you'd pressed end+down) using

    Please Login or Register  to view this content.
    and then use that variable x in your fill routine
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    03-12-2008
    Posts
    5
    erm ok the code i have is very basic.

    Please Login or Register  to view this content.
    How do i make the range it selects go down only as far as the amount of rows in column D?

    Your post aobve was a bit too technical for me :| sorry

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440
    Like this:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-12-2008
    Posts
    5
    Excellent!

    Cheers. thanks a lot for this.

  6. #6
    Registered User
    Join Date
    03-12-2008
    Posts
    5
    Hi, Sorry me again.

    The code given above was very helpful but when there is no data in the report and just headings it causes the macro to debug.

    Is there a way I can do 'if copylength is 0 then go to next sheet else carry on as normal'?

    Here is my code at the moment.

    Please Login or Register  to view this content.
    B4 is the cell that will always contain the header of the report. When the report is refreshed it will always display the header in B4 but sometimes without the data below it.

    thanks for any help you can give.

  7. #7
    Registered User
    Join Date
    03-12-2008
    Posts
    5
    bump?

    cheers

+ 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