复制并粘贴每第N行

I need a macro that will copy a column from one worksheet and paste into another spreadsheet on every 5th row. I've tried using snippets of code, but I haven't found one that I can modify to make this work. It's driving me crazy. I'm sure this is pretty easy for those who are more advanced with VB code.

我需要一个宏来复制一个工作表中的列并粘贴到每个第5行的另一个电子表格中。我已经尝试过使用代码片段,但是我还没有找到一个可以修改的代码片段。这让我疯狂。我相信对于那些使用VB代码更先进的人来说,这很容易。

Copy until Column A is empty

复制,直到A列为空

[Workbook1]
Column A
Name1
Name2
Name3
Name4
etc.

[Workbook2]
Column A
Paste Name1 in Row 5
Paste Name2 in Row 10
Paste Name3 in Row 15
Paste Name4 in Row 20
etc.

1 个解决方案

#1

Can we assume that both worksheets are in the same workbook? If so, it might look like the following code:

我们可以假设两个工作表都在同一个工作簿中吗?如果是这样,它可能看起来像下面的代码:

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lngRowCounter As Long
Dim lngRowMultiplier As Long


lngRowCounter = 1
lngRowMultiplier = 5

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Do While Not IsEmpty(ws1.Range("A" & lngRowCounter))
  ws2.Range("A" & lngRowCounter * lngRowMultiplier).Value = _
    ws1.Range("A" & lngRowCounter).Value
  lngRowCounter = lngRowCounter + 1
Loop

End Sub

You can either paste the contents of the Sub into your macro function, or you can paste that code at the bottom of your macro module and put a line of code Call Test inside your Macro.

您可以将Sub的内容粘贴到宏函数中,也可以将该代码粘贴到宏模块的底部,并在宏中放置一行代码Call Test。

I hope this helps.

我希望这有帮助。

#1