+ Reply to Thread
Results 1 to 4 of 4

Short "Basic" Macro to copy and paste formulas "N" times.

  1. #1
    Registered User
    Join Date
    12-09-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    8

    Short "Basic" Macro to copy and paste formulas "N" times.

    Hi!

    Have been stalking this forum for a loooong time, but have found something that I cannot find an answer for. Currently doing a student placement at an engineering firm and I am trying to build improve one of the Civil Engineers excel built calculation tools that lets you enter drainage information (cover levels, pipe sizes, invert levels etc) and then spits out a ready made manhole schedule that you can simply copy and paste into our CAD program for issue.

    Now, as it stands, it current requires you to edit the into on one sheet, then another, then another. I've already managed to shorten some of this down, but one of the annoying problems I have is that as I am using 4/5 different sheets, even if its now mostly automated (input on sheet 1 for out puts on all the others), when I need to enter a new row, I have to do it for all 5 sheets. Then drag the formula down. Then reformat it with borders, colors, text and the like. A pain and for big jobs can take a good while.

    What I am thinking is to have a button on the 1st sheet that is attached to a macro that then copies and pastes the formula in the top row "N" times into each of the sheets, where N is a number next to the button. How can I do this so that its A) Basic (as I have extremely limited visual basic skills for the macro editor) and B) That does that dragging formula down does. I cant have it just copy and pasting the formula as everything will have the same result. If I drag it down from A1 to A10, the formula in each cell needs to know this (if you get what I mean).

    Thanks a lot,

    Grady

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Short "Basic" Macro to copy and paste formulas "N" times.

    Does this help?

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-09-2014
    Location
    England
    MS-Off Ver
    2013
    Posts
    8

    Re: Short "Basic" Macro to copy and paste formulas "N" times.

    Ultra swift reply! Could you break it down for me slightly? Rather new to any kind of advanced excel work! Will give it a go in a bit as well.

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Short "Basic" Macro to copy and paste formulas "N" times.

    The i variable is used to select the 5 sheets. The x variable is set to indicate how many times to copy. It assumes however, that you do not have a header in row 1 since it is copying that row and inserting 10 time for each sheet. I'm not sure if something like this may help. But let me know.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel 2010 -- "Visual Basic" "Macros" and "Record Macro" all disabled.
    By NicholasL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-07-2017, 06:11 AM
  2. [SOLVED] How to short my formula which is ="("&a1&","&a2&","&a3&","&a4&" end in "&a200&")
    By vengatvj in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-07-2013, 07:49 PM
  3. [SOLVED] Copy multiple rows that contain a certain "name" in column "A" then paste it into new work
    By rschlot2 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2013, 02:25 PM
  4. Need some help with a basic "copy/paste" macro
    By bigkahuna2187 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2012, 09:01 AM
  5. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM

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