Excel VBA复制范围从其他电子表格转置

• Excel只允许同时打开一个具有特定文件名的工作簿,即使这些工作簿存在于不同的目录中(它们必须存在,否则不能具有相同的文件名)。

• 这个 Workbooks 集合的索引只是文件名,而不是完全限定的路径和名称。

我不确定第一点是否是第二点的原因,或者第二点是否是第一点的原因,但它们是相关的。

因此,您的代码应该是:

Sub PasteSpecial_Examples()
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1:A5").Copy
    ActiveSheet.Range("A1").PasteSpecial Transpose:=True    
End Sub

基于暗示您尚未打开的评论 Libraries\Documents\Book1.xlsx 运行代码时,可以执行以下操作:

Sub PasteSpecial_Examples()
    Dim wsDst As WorkSheet
    Set wsDst = ActiveSheet        
    Workbooks.Open "Libraries\Documents\Book1.xlsx"
    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1:A5").Copy
    wsDst.Range("A1").PasteSpecial Transpose:=True    
End Sub

它继续以其名称引用工作簿。

或者,更好的做法是:

Sub PasteSpecial_Examples()
    Dim wbSrc As WorkBook
    Dim wsDst As WorkSheet
    Set wsDst = ActiveSheet        
    Set wbSrc = Workbooks.Open("Libraries\Documents\Book1.xlsx")
    wbSrc.Worksheets("Sheet1").Range("A1:A5").Copy
    wsDst.Range("A1").PasteSpecial Transpose:=True    
End Sub

其中指定了 Workbook 对象引用新打开的工作簿,然后在 Copy 陈述

注:在本规范中 "Libraries\Documents\Book1.xlsx" 是一个 相对的 对文件的引用,例如,如果当前目录为 C:\Temp 然后它会查找文件 C:\Temp\Libraries\Documents\Book1.xlsx . 如果可能的话,您应该认真考虑使用绝对引用。

我是这样做的:

    Dim Finfo As String
    Dim FilterIndex As Long
    Dim Title As String
    Dim ExportFilename As Variant
    Dim CopyBook As Workbook
    Dim CopySheet As Worksheet
    Dim MnthName As String


    'Set up file filter
    Finfo = "Excel Files (*.xls*),*.xls*"
    'Set filter index to Excel Files by default in case more are added
    FilterIndex = 1
    ' set Caption for dialogue box
    Title = "Select a the DD Revenue Master file to Export to"

    'get the Forecast Filename
    ExportFilename = Application.GetOpenFilename(Finfo, FilterIndex, Title)

    'Handle file Selection
    If ExportFilename = False Then
        'No Export File was Selected
        MsgBox "No file was selected"

    Else
        'Check and see if this is a correct Export File
        Workbooks.Open (ExportFilename)
        Set CopyBook = ActiveWorkbook
        Set CopySheet = CopyBook.Worksheets(1)

        MsgBox "Valid File Selected."

        Application.CutCopyMode = False


        revenueSheet.Range("A1:BO500").Copy
        CopyBook.Worksheets(1).Activate
        CopyBook.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
        CopyBook.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, _
                    SkipBlanks:=False, Transpose:=False

         Application.CutCopyMode = False 'erase the clipboard

'close your stuff that you dont want open
End If

End Sub

做完后别忘了把作业本合上。我不得不删减一堆代码,因为我的文件启动到一个大的case select中。但通常您会选择一个工作簿,打开它,选择一些数据,复制它,粘贴它,关闭工作簿。经常发生。希望这有帮助。我相信我发现您必须激活新选择的工作簿才能对其执行操作。您始终可以将包含代码的工作簿称为ThisWorkbook

为了避免混淆,由于它们用于一系列模块中,我有一个全局变量模块,其中包含以下内容,但如果您没有复杂的项目,可以在子模块的顶部执行此操作。

Option Explicit

Public thisWB As Workbook
Public functionSheet As Worksheet
Public revenueSheet As Worksheet
Public salesSheet As Worksheet
Public scratchSheet As Worksheet
Public lastRow As Double


'**********************************************************
'This sub routine will be used to intialize public variables
'**********************************************************

Private Sub SetPublicVariables()
    Set thisWB = ActiveWorkbook
    Set functionSheet = thisWB.Worksheets("Data Functions")
    Set revenueSheet = thisWB.Worksheets("DD Monthly Revenue")
    Set salesSheet = thisWB.Worksheets("Salespersons")
    Set scratchSheet = thisWB.Worksheets("ScratchSheet")

End Sub

我经常用这种方法。

哦,我调用在工作簿上设置的公共变量open(您可以找到该方法)。为了呼叫私人sub,您必须使用。

Application.Run "Global_Variables.SetPublicVariables"
'that is modulename.methodname if you want to pass arguments following
'Application.Run "modulename.methodname", arg1, arg2, etc.

干杯,快乐编码-WWC