Loading a worksheet from another workbook into Excel with VBA

Question

I am attempting to create a subroutine that prompts the user to select a workbook and then adds the first worksheet of the selected workbook as a tab in the existing (active) workbook. Then names the new tab "Data". Here is the code I am using so far:

Sub getworkbook()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook

Set targetWorkbook = Application.ActiveWorkbook

' get the customer workbook
filter = "Text files (*.xlsx),*.xlsx"
caption = "Please Select an input file "
ws = Application.GetOpenFilename(filter, , caption)

ws.Add After:=Sheets(Sheets.Count)

ws.Name = "DATA"

End Sub

This code doesn't seem to be working and is returning the following error:

"ws.Add" method or With Block not set.

Any help is appreciated.

Thanks,

Similar question: http://stackoverflow.com/questions/3840628/creating-and-naming-worksheet-in-excel-vba — Anirudh Ramanathan, Aug 15 '12 at 23:32

score 4 · Accepted Answer · edited May 23 '17 at 12:16

You have declared ws as a worksheet and GetOpenFilename is returning a File name. I would recommend reading my post in this link:

Is this what you are trying?

Note: I have not done any error handling. I am sure you can take care of that.

Sub getworkbook()
    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    filter = "Text files (*.xlsx),*.xlsx"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)

    wb.Sheets(1).Move After:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

    ActiveSheet.Name = "DATA"
End Sub

change `filter = "Text files (*.xlsx),*.xlsx"` to `filter = "Text files (*.xls*),*.xls*"` — Siddharth Rout, Sep 06 '12 at 18:56

Loading a worksheet from another workbook into Excel with VBA

1 Answers1