Microsoft Excel – How to link or copy cells to a different worksheet on every nth row. I have an interesting problem. It is probably simple, but I can’t figure it out.

I have a list of cells (about 10 columns across and over thousand rows, soon to be expanding) A1-A10 and down These are my headings and main input cells (let’s call this Summary Sheet)

I need these cells copied and preferably linked to the adjacent worksheet (Detailed Sheet), but in such a way that each cell (or 10 cell across) is copied on every 20 th line.

At times I will be adding and deleting rows from the Summary Sheet. Therefore I would love to be able to use autofill or some feature like that in the Detailed Sheet. After I update my Summary Sheet and could then update Detailed Sheet by autofilling the formulas down.

More detailed info:
Each row to which these cells are copied becomes a heading row for each 20 line module. The 19 rows below the copied headings remains empty and detailed calculations are done on the 19 rows further right. Each section is an identical module with many detailed calculations and the results on the 1 row in each module. Presently I only use the Detailed Sheet and it is very time consuming to add or remove modules and their headings.

Thanks for any answers. Martin

  • Any problem in excel you can't articulate in 4 or 5 paragraphs is not simple! Just the wide variety of answers so far is an indication that people don't understand the question I think. Is it possible for you to make a sample spreadsheet available somewhere? – DaveParillo Jan 23 '10 at 0:31
  • You can make my code more transportable if you use named ranges. – Lance Roberts Jan 25 '10 at 22:19
  • You used too much words instead of a before and after screenshot – helena4 Dec 15 '15 at 16:32

Perhaps this will work:

Create a column A, Counter, and set its cells to 0,1,2,3,...,(number of rows). Make a new column, RowType. Set RowType to =IF(MOD(A1,20) = 0, "Summary", "Detail") and drag that down.

That will create a table that looks like this:

 0  Summary
 1  Detail
 2  Detail
 3  Detail
 4  Detail
 5  Detail
 6  Detail
 7  Detail
 8  Detail
 9  Detail
10  Detail
11  Detail
12  Detail
13  Detail
14  Detail
15  Detail
16  Detail
17  Detail
18  Detail
19  Detail
20  Summary
21  Detail
22  Detail
23  Detail
24  Detail

Then filter the RowType column for Summary and paste your cells as usual

  • Thanks for the suggestion. I already have filtering in place. When I apply filter the detailed sheet shows headings only. I tried your suggestion. When I paste the cells into the filtered sheet, the cells being pasted are pasted also into the hidden cells. Therefore it does now work. Or am I missing something? – Martin Jan 23 '10 at 0:35
  • To explain what I need: I made a simplified Excel file showing everything. You get download it here. mediafire.com/?o2j3lnjmmqg Martin – Martin Jan 23 '10 at 4:10

I'm not sure I understand your question, but when you mentioned autofill, there is a way to create a custom autofill list and maybe that will help you.

In 2003, go to the Tools Menu -- then down to "Options". In the Dialog box, go to the "Custom Lists" tab. You can either type the list in the "List Entries" box separated by commas or you can import the list from existing cells.

When you want to autofill the list, just type any one entry in a cell and drag the fill handle. It will give you the list beginning with that entry.

If you have 2007, you can get to Custom Lists by clicking the Office Button and then clicking the "Excel Options" button. You'll see a button in there for Custom Lists.


Place a button on your Summary sheet, and put this code behind it, changing the sheetnames and the button name to what you want to use. Then every time you want to redo the Detail lines, hit the button. Note: I've written this so the Summary sheet is Sheet2, and the Detail sheet is Sheet1:

Private Sub RedoDetailedSheet_Click()

Dim i As Integer, j As Integer

For i = 0 To Worksheets("Sheet2").UsedRange.Rows.Count - 1
    For j = 1 To Worksheets("Sheet2").UsedRange.Columns.Count

        Worksheets("Sheet1").Cells(20 * i + 1, j).Formula = "=Sheet2!" _
            & Worksheets("Sheet2").Cells(i + 1, j).Address

    Next j
Next i

End Sub

Here's the code based on your sample file for that specific situation. You'll need to change the format of the Model column to General.

Private Sub RedoDetailedSheet_Click()

Dim i As Integer, j As Integer

For i = 0 To Worksheets("Input").UsedRange.Rows.Count - 2
    For j = 1 To Worksheets("Input").UsedRange.Columns.Count

        Worksheets("Output").Cells(20 * i + 29, j + 2).Formula = "=Input!" _
            & Worksheets("Input").Cells(i + 2, j).Address

    Next j
Next i

End Sub
  • Can you tell me how to adjust the cell addresses. The original (sheet1) Summary range is from A2:J2 and down. The destination (sheet2) Detailed Sheet range is C30:L30 and down (every 20 lines). Thanks for you help. – Martin Jan 23 '10 at 1:07
  • @Martin, I had written it so that Sheet2 was the Summary, and Sheet1 was the Detail (I'll edit that into my answer). You'll have to play with the indexes to get them to work right, but I'm out of time to figure it out now. I'll be back on Sunday or Monday, and if you haven't solved it yet, I'll hammer the right numbers out. – Lance Roberts Jan 23 '10 at 2:00
  • Thank you for the help. To explain what I need: I made a simplified Excel file showing everything. You get download it here. mediafire.com/?o2j3lnjmmqg Martin – Martin Jan 23 '10 at 4:07
  • @Martin, ok, I modified it for your sample file, read the edit – Lance Roberts Jan 25 '10 at 22:16
  • Thanks Lance, I really appreciate your help. I will try your code tonight. Martin – Martin Jan 25 '10 at 23:51

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.