Microsoft Excel - 如何将单元格复制到每第n行的不同工作表

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.

Microsoft Excel - 如何将单元格复制到每第n行的不同工作表。我有一个有趣的问题。这可能很简单,但我无法弄清楚。

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)

我有一个单元格列表(大约10列,超过千行,很快就会扩展)A1-A10和向下这些是我的标题和主要输入单元格(让我们称之为摘要表)

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.

我需要复制这些单元格并最好链接到相邻的工作表(详细表格),但是每隔20行复制一个单元格(或10个单元格)。

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.

更详细的信息:复制这些单元格的每一行都成为每个20行模块的标题行。复制标题下方的19行仍为空,并在右侧19行上进行详细计算。每个部分都是一个相同的模块,具有许多详细的计算结果,每个模块的第1行结果。目前我只使用详细资料表,添加或删除模块及其标题非常耗时。

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

摘要表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

详细表A B C D 1#A1#B1#C1 ...... 19空行21#A2#B2#C2 ...... 19空行31#A3#B3#C3 ...... 19空行

Thanks for any answers. Martin

谢谢你的回答。马丁

2 个解决方案

#1

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:

在摘要表上放置一个按钮,并将此代码放在其后面,将工作表名称和按钮名称更改为您要使用的名称。然后每次要重做细节线时,点击按钮。注意:我写了这个,所以摘要表是Sheet2,详细信息表是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.

这是基于针对该特定情况的示例文件的代码。您需要将Model列的格式更改为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

#2

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

这可以通过Excel的查找功能轻松处理 - 无需VBA - 这使得在https://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.

更新:我无法从MediaFire获取您的文件,但是从您最近写的内容看来,Lance的代码应该适合您。祝你好运。