英文:
copy and paste data from multiple worksheets with exception
问题
Hello, I'm here to provide the translated content for you:
你好，我是新手使用VBA，我正在使用它来处理Excel。我想从所有工作表复制并粘贴数据，除了一个工作表，应该怎么做？如何确保粘贴的数据不会互相覆盖，并使下一组数据粘贴到下一列？
我尝试了以下代码，但数值互相覆盖，只有最后一个工作表的数值被粘贴到最后，我应该添加什么以排除Sheet2：
Public Sub DoToAll()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Sheet2" Then
ws.Range("A3:A300").Copy
Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Transpose:=True
End If
Next
End Sub
请注意，我已经在代码中添加了一个条件来排除Sheet2工作表，并在粘贴数据时将其放在下一列。
英文:
Hello im new to VBA and I am using it for excel. I want to make the copy and paste the data from all of the worksheets except from one sheet, how do i do it for? How do i make the data to be pasted without overwriting each other and have the next data to be pasted in the next column?
I tried this code, the values just overwrites each other, making only the value of the last worksheet to be pasted at the end, and what should i add to make the exception for sheet2
Public Sub DoToAll()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("A3:A300").Copy
Worksheets("Sheet2").Range("E1").PasteSpecial Transpose:=True
Next
End Sub
答案1
得分: 2
要省略一个工作表，只需在循环中检查名称。
为确保不覆盖目标单元格中的数据并最终只获得一行数据，请确保移至下一行。有多种方法可以实现这一点，在以下示例中，我使用了一个范围变量（destRange），并在复制后使用Offset函数将其设置为下一行。
Sub DoToAll()
Const destSheetName = "Sheet2"
Dim ws As Worksheet
Dim destRange As Range
Set destRange = ThisWorkbook.Sheets(destSheetName).Range("E1")
For Each ws In ThisWorkbook.Sheets
If ws.Name <> destSheetName Then
ws.Range("A3:A300").Copy
destRange.PasteSpecial Transpose:=True
' 转到下一行
Set destRange = destRange.Offset(1, 0)
End If
Next
End Sub
英文:
To omit one worksheet, just check the name inside the loop.
To ensure that you don't overwrite the data in the destination cell and end up with only one row, ensure that you move to the next row. There are many ways to do so, in the following example I use a Range variable (destRange) and set it to the next row after a copy using the Offset-function.
Sub DoToAll()
Const destSheetName = "Sheet2"
Dim ws As Worksheet
Dim destRange As Range
Set destRange = ThisWorkbook.Sheets(destSheetName).Range("E1")
For Each ws In ThisWorkbook.Sheets
If ws.Name <> destSheetName Then
ws.Range("A3:A300").Copy
destRange.PasteSpecial Transpose:=True
' Goto Next row
Set destRange = destRange.Offset(1, 0)
End If
Next
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论