Microsoft Excel – How to copy cells to a different worksheet on every nth row.

Question

Microsoft Excel – How to 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.

Summary Sheet A B C D 1 #A1 #B1 #C1 … 2 #A2 #B2 #C2 …
3 #A3 #B3 #C3 …

Detailed Sheet A B C D 1 #A1 #B1 #C1 … … 19 empty lines 21 #A2 #B2 #C2 …
… 19 empty lines 31 #A3 #B3 #C3 … … 19 empty lines

Thanks for any answers. Martin

Answer 1

This is easily handled with Excel's Lookup functions - no VBA required - and that makes this more appropriately asked on http://superuser.com/.

Update: I couldn't get your file from MediaFire, but it appears from what you've written recently that Lance's code should work for you. Good luck.

Answer 2

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