首先感谢您的帮助。我需要复制/粘贴数据。下一个想法是：根据工作表AAA中的单元格内容，我想将数据复制/粘贴到相应的工作表中（如果XXX，则复制到XXX或如果ZZZ，则粘贴到ZZZ）。我的宏有效，但是问题是我有一个偏移量困扰着我。想象一下，第一圈将数据粘贴到XXX，但是第二圈将复制到ZZZ，在这种情况下，我遇到了一个问题，因为它将粘贴复制到了第三个单元格（3,1）而单元格（2,1）是空的
Sub CopyPastingMyDate()
Dim i As Long
Dim lrow As Long
Dim lcol As Long
Dim RngOne As Range
Dim RngTwo As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("AAA")
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set RngOne = ws.Range(ws.Cells(lrow, 1), ws.Cells(1, lcol))
For i = 2 To lrow
Set RngOne = ws.Range(ws.Cells(lrow, 1), ws.Cells(1, lcol))
If ws.Cells(i, 1) = "XXX" Then
Set RngTwo = ThisWorkbook.Worksheets("SheetXXX").Range(ThisWorkbook.Worksheets("SheetXXX").Cells(i, 1), ThisWorkbook.Worksheets("SheetXXX").Cells(i, lcol))
RngOne.Copy
RngTwo.PasteSpecial xlAll
End If
If ws.Cells(i, 1) = "ZZZ" Then
Set RngTwo = ThisWorkbook.Worksheets("SheetZZZ").Range(ThisWorkbook.Worksheets("SheetZZZ").Cells(i, 1), ThisWorkbook.Worksheets("SheetZZZ").Cells(i, lcol))
RngOne.Copy
RngTwo.PasteSpecial xlAll
End If
Next i
End Sub
请如何解决？我想从第一个可用单元格复制粘贴到。感谢大家。贾娜
试试这个。我可能会误解您要复制的内容：我假设每行都需要复制到正确的工作表上吗？
Sub CopyPastingMyDate()
Dim i As Long
Dim lrow As Long
Dim lcol As Long
Dim RngOne As Range
Dim RngTwo As Range
Dim ws As Worksheet, dest, wsDest As Worksheet
Set ws = ThisWorkbook.Worksheets("AAA")
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set RngOne = ws.Range(ws.Cells(lrow, 1), ws.Cells(1, lcol))
For i = 2 To lrow
Select Case ws.Cells(i, 1).Value 'which destination sheet?
Case "XXX": dest = "SheetXXX"
Case "ZZZ": dest = "SheetZZZ"
Case Else: dest = ""
End Select
If Len(dest) > 0 Then
ws.Cells(i, 1).Resize(1, lcol).Copy 'copy the row
Set wsDest = ThisWorkbook.Worksheets(dest)
wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlAll
End If
Next i
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句