粘贴范围班级特殊方法失败vba

问题描述：

Sub MakeTables() 
    Dim wbTarget As Object 

    Set wb = Workbooks.Open("C:\Users\A9900899\Desktop\Desmond\VBAProject\GenerateTablesFormulas.xlsx") 
    Set wbTarget = Workbooks.Open("C:\Users\A9900899\Desktop\Desmond\VBAProject\USDReport.xlsx") 

    With wb.Sheets("Sheet1").UsedRange 
    .Copy 

    ' Create the new sheet and name it at the end 

     With wbTarget.Sheets("HK").Range("D82:X97") 
     .PasteSpecial xlValues 
     .PasteSpecial xlFormats 
     End With 
    End With 

End Sub

有人可以告诉我我在这里犯了什么错误。它给了我范围类失败的错误。谢谢粘贴范围班级特殊方法失败vba

，如果你想保持复制范围大小，然后

你不能有'随着wbTarget.Sheets（ “HK”）'内'随着wb.Sheets（ “工作表Sheet1”）'是'另一个在Worksheet'另一个'Workbook'对象。您应该关闭第一个'With wb.Sheets（“Sheet1”）。UsedRange' with'End With'，然后才能启动第二个'With wbTarget.Sheets（“HK”）。Range（“D82：X97”） ' –

我不认为这是问题所在。我已经把它分离出来了，但是仍然得到了同样的错误 – Desmond

你在哪一行得到你的错误？ –

答

：

With wb.Sheets("Sheet1").UsedRange 
    .Copy 

    ' Create the new sheet and name it at the end 

    With wbTarget.Sheets("HK").Range("D82") '.Resize(.Rows.Count, .Columns.Count) '<--| you can omit the 'Resize' part but it can be useful to make it clear (code can be read in years to come) you want to stick to copied range size 
     .PasteSpecial xlValues 
     .PasteSpecial xlFormats 
    End With 
End With 
Application.CutCopyMode = False '<--| empty clipboard

否则，如果你想force复制的范围大小与待粘贴一个，那么：

' Create the new sheet and name it at the end 

With wbTarget.Sheets("HK").Range("D82:X97") '<--| reference range you want to paste values to and keep sizes of 
    wb.Sheets("Sheet1").UsedRange.Resize(.Rows.Count, .Columns.Count).Copy '<--| copy the source sheet range with same sizes as referenced range 

    .PasteSpecial xlValues 
    .PasteSpecial xlFormats 
End With 
Application.CutCopyMode = False '<--| empty clipboard

@Desmond;你有没有尝试过这个解决方案？ – user3598756

粘贴范围班级特殊方法失败vba

相关推荐