Runtime error 91 in Excel/VBA on assigning Workbook variable

Question

I have this code in Creator workbook and I am copying data from the data file I select. But the code gives me the following error:

Object variable or with block variable not set

Sub transfer()
Dim myfile As Workbook
Dim myWs As Worksheet
Dim DataWs As Worksheet

Set myWs = ThisWorkbook.ActiveSheet

myfile = Application.GetOpenFilename(, , "Browse For Data file")
Workbooks.Open myfile
Set DataWs = myfile.Sheets("Instru Input")

myWs.Range("C3:C11000").Copy
DataWs.Range("E2").PasteSpecial xlPasteAll
myWs.Range("E3:E11000").Copy
DataWs.Range("F2").PasteSpecial xlPasteAll
myWs.Range("G3:G11000").Copy
DataWs.Range("G2").PasteSpecial xlPasteAll
myWs.Range("I3:I11000").Copy
DataWs.Range("H2").PasteSpecial xlPasteAll
myWs.Range("K3:K11000").Copy
DataWs.Range("I2").PasteSpecial xlPasteAll
myWs.Range("M3:M11000").Copy
DataWs.Range("J2").PasteSpecial xlPasteAll
myWs.Range("O3:O11000").Copy


ThisWorkbook.SaveAs

ThisWorkbook.Close

End Sub

Not an answer, but a general comment/query: If you're doing `Range1.Copy` immediately followed by `Range2.PasteSpecial xlPasteAll`, why not just do it all in 1 line without the clipboard as `Range1.Copy Destination:=Range2`? — Chronocidal, Jan 29 '19 at 10:01
In genreal, try to avoid selecting and activating anything in Excel with VBA - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Vityata, Jan 29 '19 at 10:04
@Chronocidal its a nice suggestion but i am new to vba so i really dont know how to get that ... if you could elaborate it for me ..that would be great — j.doe, Jan 29 '19 at 10:09
@Chronocidal - good idea indeed, it could be even shorter, omitting the named parameter. — Vityata, Jan 29 '19 at 10:24
@j.doe For example, `myWs.Range("C3:C11000").Copy` and `DataWs.Range("E2").PasteSpecial xlPasteAll` can be combined in 1 line: `myWs.Range("C3:C11000").Copy Destination:=DataWs.Range("E2")` - it also makes it slightly easier to read when debugging, because you can see it just says "copy this data to this location" — Chronocidal, Jan 29 '19 at 10:34

Vityata · Accepted Answer · 2019-01-29T10:23:34.750

2

This is what GetOpenFileName does by the specification from here:

Displays the standard Open dialog box and gets a file name from the user without actually opening any files.

Thus, once you get the file name (with the file path) as a string, it should be recorded as a string. Using this string, a workbook variable could be assigned with Set myWb = Workbooks.Open(fileName):

Sub TestMe()

    Dim fileName As String
    fileName = Application.GetOpenFilename(, , "Browse For Data file")
    Debug.Print fileName

    Dim myWb As Workbook
    Set myWb = Workbooks.Open(fileName)

End Sub

In general, this is how a working code, taking some info from one worksheet to a worksheet in another workbook would look like:

Sub TestMe()

    Dim fileName As String
    fileName = Application.GetOpenFilename(Title:="Browse For Data file")
    Debug.Print fileName

    Dim targetWs As Worksheet
    Set targetWs = Workbooks.Open(fileName).Worksheets("Instru Input")

    Dim sourceWs As Worksheet
    'To avoid using the ActiveSheet:
    Set sourceWs = ThisWorkbook.Worksheets("NameOfTheWorksheet") 

    With sourceWs
        .Range("C3:C11000").Copy targetWs.Range("E2")
        .Range("E3:E11000").Copy targetWs.Range("F2")
        'And so on ...
    End With

    ThisWorkbook.SaveAs "WriteFileAddressHere.xlsb"
    ThisWorkbook.Close

End Sub

Make sure that you change "NameOfTheWorksheet" string and "WriteFileAddressHere.xlsb" to something relevant.

edited Jan 29 '19 at 10:23

answered Jan 29 '19 at 09:56

Vityata

42,633
8
55
100

so what change should i make to the above code? to get that functionality of choosing the file and then copying data from that workbook? – j.doe Jan 29 '19 at 10:07
1

worked with some changes but my error got solved an now i am getting the expected output ...thanks alot! – j.doe Jan 29 '19 at 11:25
question...how do i close the selected workbook after copying? @Vityata – j.doe Jan 29 '19 at 12:40
@j.doe - write - `targetWs.Parent.Close True` or better declare the other workbook as a variable and close it this way. – Vityata Jan 29 '19 at 12:44
didnt get you ... will this come in the end of the code? – j.doe Jan 30 '19 at 06:14
@j.doe - write it instead of `ThisWorkbook.Close`. But, the better option is probably to refactor, declare the other workbook as a variable and close it. – Vityata Jan 30 '19 at 19:05

Runtime error 91 in Excel/VBA on assigning Workbook variable

1 Answers1