VBA Code - Import External Worksheet into Active Workbook

Question

Hi,

Need help with VBA Code.

I've searched and tried a few codes but have been having trouble finding something that works.

What I want to do:

1. I'm in an open and active workbook
2. I want to insert and new sheet at the end of all the sheets in the open workbook
3. The sheet I want to insert is from a closed workbook with several worksheets.

Info, Please use in example code, appreciated.

1. location:   "C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx"

Appreciated,

Have a g'day!!!

Answer 1

Public Sub cpy()

    Application.ScreenUpdating = False

    Set fileB = Workbooks.Open("C:\Users\Xxxx\Documents\Xxxx\Target File.xlsx")

    fileB.Sheets("Sheet2").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    fileB.Close

    Application.ScreenUpdating = True

End Sub

Answer 2

Correct

Dim fileB As Workbooks

as

Dim fileB As Workbook

Answer 3

The error message means "Sheet4" cannot be found.

You should make sure the worksheet "Sheet4" is present (the name you see in the worksheet tab), and do not confuse with the underlying worksheet object name

Answer 4

Hi Wyman,

Corrected but now have the following problem, can you help?

Answer 5

Hi Wyman,

Firstly thanks, so far so good but have an issue when trying to run.

Problem:

1. I had to define a varible for fileB so did that.
2. Now have error "Compile Error: Method or Data number not found"

    It relates to this in code:  fileB.Sheets("Sheet4").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

    It looks like fileB.Sheets is the problem.

    fileB. options don't contain Sheets in it, not sure how to correct.

3. Can you please test/correct, appreciated.

Current Code:

Option Explicit

Public Sub SPCXS()

     Dim fileB As Workbooks

     Application.ScreenUpdating = False

     Set fileB = Workbooks.Open("C:\Users\Nick\Documents\Work\zOther\SPC Master.xlsx")

     fileB.Sheets("Sheet4").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

     fileB.Close

     Application.ScreenUpdating = True

 End Sub