"xlPasteColumnwidths"错误我的代码可以更高效吗? [英] Error on "xlPasteColumnwidths" and can my code be more efficient?

问题描述

在工作簿之间复制数据的最佳方法是什么?我已经编写了下面的代码，用于将数据从给定的工作簿复制到此工作簿".但是我遇到了一些问题，我开始认为这不是正确的方法?

What is the best way to copy data between workbooks? I have written the code below for copying data from a given workbook to "thisworkbook". However I'm facing some problems and I am starting to think this is not the correct way to do this?

我到底想要什么:

我有一个工作簿，其中有很多数据，我希望将某些给定范围从该工作簿复制到此工作簿"中.复制完这些后，我要删除数据之间的所有空白行

I have a workbook in which I have lots of data, I want some given ranges copied from that workbook into "thisworkbook". After these are copied I want to delete all blank rows between the data

我所拥有的:

Application.ScreenUpdating = False

Dim wb As Workbook

Set wb = Workbooks.Open("path")

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("B113:D117").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A106")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("F3:F107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("D1")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

With wb
    Sheets("sheet1").Range("F113:F117").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("D106")
    .PasteSpecial xlPasteAll
    .PasteSpecial xlPasteColumnWidths
End With

wb.Close False

On Error Resume Next

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Application.ScreenUpdating = True

为什么在"xlPasteColumnwidths"处出现错误，我可以使此代码更有效吗?

Why do I get an error at "xlPasteColumnwidths and can I make this code more efficient?

推荐答案

在PasteSpecial操作中不能有多个枚举，您需要分别执行每个操作:

You can't have multiple enumerations in a PasteSpecial operation, you'll need to perform each operation separately:

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A1")
    .PasteSpecial xlPasteAll
End With

With wb
    Sheets("sheet1").Range("B3:D107").Copy
End With

With ThisWorkbook.Sheets("sheet1").Range("A106")
    .PasteSpecial xlPasteColumnWidths
End With

关于性能，我看不到您的代码有任何问题.当然，您可以考虑将整个工作表整理成一个数组，然后写出所需的位，但是对于这种简单的操作来说，这太过分了.

Regarding performance I can't see any issue with your code. Sure, you could look at picking up the whole sheet into an array and then writing out the bits you need but that would be overkill for something this simple.

这篇关于"xlPasteColumnwidths"错误我的代码可以更高效吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！