+ Reply to Thread
Results 1 to 3 of 3

Copy 1 cell formula to a range of cells and loop every 3 rows

Hybrid View

  1. #1
    Registered User
    Join Date
    12-02-2013
    Location
    Thohoyandou, South Afric
    MS-Off Ver
    Excel 2010
    Posts
    2

    Question Copy 1 cell formula to a range of cells and loop every 3 rows

    Here are the individual lines of code. How does one create a macro loop instead of all the lines of code:

    Range("D21").Select 'copy Subject
    Selection.Copy
    Range("E21:O21").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D24").Select 'copy Next Subject
    Selection.Copy
    Range("E24:O24").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D27").Select 'copy Next Subject
    Selection.Copy
    Range("E27:O27").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D30").Select 'copy Next Subject
    Selection.Copy
    Range("E30:O30").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D33").Select 'copy Next Subject
    Selection.Copy
    Range("E33:O33").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D36").Select 'copy Next Subject
    Selection.Copy
    Range("E36:O36").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D39").Select 'copy Next Subject
    Selection.Copy
    Range("E39:O39").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D42").Select 'copy Next Subject
    Selection.Copy
    Range("E42:O42").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D45").Select 'copy Next Subject
    Selection.Copy
    Range("E45:O45").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Range("D48").Select 'copy Next Subject
    Selection.Copy
    Range("E48:O48").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    I'm not too sure about the cell references. This must take place in the active worksheet of a workbook that has 67 worksheets. I can loop through the worksheets but this has me a bit stuck.

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Copy 1 cell formula to a range of cells and loop every 3 rows

    for count = 21 to 48 step 3
    Range("D" & Count).copy Range("E" & count & ":O" & count)
    next
    ?

  3. #3
    Registered User
    Join Date
    12-02-2013
    Location
    Thohoyandou, South Afric
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Copy 1 cell formula to a range of cells and loop every 3 rows

    Thanks, that worked!

+ 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] Delete a range of nearby rows where a cell = 0 (and loop to catch all)
    By jdodz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-12-2013, 08:09 PM
  2. [SOLVED] Loop through column check for colored cell and copy that cell to cells below it.
    By RENCE19 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2013, 04:51 AM
  3. Replies: 1
    Last Post: 01-23-2013, 03:00 PM
  4. [SOLVED] Loop Through A Range, Find Cells > 1, Copy Offset To Another Sheet
    By bugmenot in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-25-2008, 06:30 PM
  5. Macro Copy Active Cell to Range Name, loop
    By zigstick in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-27-2005, 03:06 PM

Tags for this Thread

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