vba Excel 宏循环遍历行并复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19656247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro loop through rows and copy to another sheet
提问by Rupal
I'm new to VBA and trying to acheive this in Excel:
我是 VBA 的新手，并试图在 Excel 中实现这一点：
I have a sheet1 where there are rows with values. I have created a button which executes and copy the first row of data to another sheet(Sheet2), into some predefined cells (i.e. value in Cell A1 goes into for instance B3). Then I'm going to save that into a pdf.
我有一个 sheet1，其中有带有值的行。我创建了一个按钮，它执行并将第一行数据复制到另一个工作表（Sheet2）中，并将其复制到一些预定义的单元格中（即单元格 A1 中的值进入例如 B3）。然后我要把它保存成pdf。
Now I want my code to be more dynamic and do this for each of the rows in Sheet1. So first all values in A1-D1 gets copied to sheet2, create pdf, then next row (A2-D2) to sheet 2, create pdf and so on.
现在我希望我的代码更加动态，并对 Sheet1 中的每一行执行此操作。因此，首先将 A1-D1 中的所有值复制到 sheet2，创建 pdf，然后将下一行 (A2-D2) 复制到 sheet 2，创建 pdf，依此类推。
How can I acheive this?
我怎样才能做到这一点？
My code that works fine for one row:
我的代码适用于一行：
Private Sub CommandButton1_Click()
'Get name
Worksheets("Sheet2").Range("B5").Value = Worksheets("Sheet1").Range("V2").Value & " " & Worksheets("Sheet1").Range("W2").Value
'Get adress
Worksheets("Sheet2").Range("B6").Value = Worksheets("Sheet1").Range("AB2").Value
'Create pdf for this row
RDB_Worksheet_Or_Worksheets_To_PDF ()
End Sub
回答by Rupal
Solved!
解决了！
Final code that works (for me):
最终有效的代码（对我来说）：
Dim pointer As Integer
pointer = 2
Do While Not IsEmpty(Worksheets("Sheet1").Range("V" & pointer))
Private Sub CommandButton1_Click()
'Get name
Worksheets("Sheet2").Range("B" & pointer).Value = Worksheets("Sheet1").Range("V" & pointer).Value & " " & Worksheets("Sheet1").Range("W"& pointer).Value
'Get adress
Worksheets("Sheet2").Range("B" & pointer).Value = Worksheets("Sheet1").Range("AB" & pointer).Value
'Create pdf for this row
RDB_Worksheet_Or_Worksheets_To_PDF ()
pointer = pointer + 1
Loop
End Sub
回答by Rupal
You need a loop to iterate through all cells in your V column.
您需要一个循环来遍历 V 列中的所有单元格。
Private Sub CommandButton1_Click()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
Dim cell As Range
For Each cell In ws1.Range("V2:V" & ws1.Range("V" & Rows.Count).End(xlUp).Row)
ws2.Range("B5") = cell & Chr(32) & cell.Offset(0, 1)
ws2.Range("B6") = cell.Offset(0, 6)
'RDB_Worksheet_Or_Worksheets_To_PDF ()
Next
End Sub
