Excel VBA Copy Range Transpose from Another Spreadsheet

Question

I want to copy a range from a workbook and transpose it into my current sheet.

Why would I get a "Subscript out of range" error on this line:

Workbooks("Libraries\Documents\Book1.xlsx").Worksheets("Sheet1").Range("A1:A5").Copy

Sub PasteSpecial_Examples()
'https://stackguides.com/questions/8852717/excel-vba-range-copy-transpose-paste
'https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/

    Workbooks("Libraries\Documents\Book1.xlsx").Worksheets("Sheet1").Range("A1:A5").Copy
    ActiveSheet.Range("A1").PasteSpecial Transpose:=True

End Sub

Answer 1

• Excel only permits one workbook open with a certain filename at the same time, even if those workbooks exist in different directories (which they must, or they couldn't have the same filename).

• The Workbooks collection's index is just the filename, not the fully-qualified path and name.

I'm not sure whether the first point is the reason for the second point, or whether the second point is the reason for the first point, but they will be related.

So your code should be:

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

---

Based on comments implying that you haven't yet opened Libraries\Documents\Book1.xlsx when you run your code, you could do this:

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

which continues to refer to the workbook by its name.

Or, slightly better, do this:

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

which assigns a Workbook object to refer to the newly opened workbook and then uses that object in the Copy statement.

Note: In this code "Libraries\Documents\Book1.xlsx" is a relative reference to the file, e.g. if the current directory was C:\Temp then it would look for the file C:\Temp\Libraries\Documents\Book1.xlsx. You should seriously consider using an absolute reference if possible.

Answer 2

I do it like this:

    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

Don't forget to close your workbooks when you are done. I had to trim a bunch of code because my file launches into a large case select. But often you select a workbook, open it, select some data, copy it, and paste it, close the workbook. Happens alot. Hope this helps. I believe that I found that you had to activate the newly selected workbook to perform actions on it. You can always refer to the workbook with the code in it as ThisWorkbook

To avoid confusion and since they are used in a bunch of modules I have a global variables module with the following in it but you could do this at the top of the sub if you don't have a complex project.

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

I use this method alot . . . . . .

Oh, I call the public variable set up upon workbook open (you can find that method). In order to call a private sub you must use.

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

Cheers, Happy coding - WWC