复制一个区域并将该区域中的每个单元格值粘贴到excel[重复]中的特定新单元格中

Question

Sub cp()
Worksheets("copy").Select
Range("b2").Select
Selection.copy
Range("b7").Select
ActiveSheet.Paste
Range("c2").Select
Selection.copy
Range("e7").Select
ActiveSheet.Paste
Range("d2").Select
Selection.copy
Range("h7").Select
ActiveSheet.Paste
Range("e2").Select
Selection.copy
Range("k7").Select
ActiveSheet.Paste
end sub

Answer 1

Sub cp
Dim ws as Worksheet
Set ws = Worksheets("copy")
ws.Range("B7") = ws.Range("B2")
ws.Range("E7") = ws.Range("C2")
ws.Range("H7") = ws.Range("D2")
ws.Range("K7") = ws.Range("E2")

End Sub

？？

最好在可能的情况下避免选择、复制和粘贴。尝试直接引用所需的单元格、区域、工作表和工作簿。因此这里的改进可能是指定工作簿

Dim wb as workbook
Set wb = workbooks("MyWorkbook")
dim ws as worksheet
Set ws = wb.Sheets("copy")
...

这样excel就永远不会混淆您所引用的单元格。它在打开工作簿或创建新工作簿时特别方便。

Dim wb as workbook
Set wb = workbooks.add 'Creates a new workbook, and binds it to the variable wb, so you always know any action on wb is being done to that new workbook

Dim x as Integer
Dim ws as Worksheet
Set ws = Worksheets("copy")
For x = 2 to 500
    ws.Cells(7,x) = ws.Cells(2,x)
Next x