Error | Copying Worksheet from Closed WB to Active WB.

[TryingBest]

Hi,

This code gives me an error. Unable to get why .. as where I see the same code for copying a sheet from closed workbook to active (This) workbook.
Please help!

Sub copyFirstWS()
    Dim wb As Workbook

    Set wb = Workbooks.Open("C:\Users\-")

    With ThisWorkbook
        wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count)
    End With

   wb.Close savechanges:=False


End Sub

 

[Anthony47]

And which is the name of the file you try to open?

 

[TryingBest]

And which is the name of the file you try to open?

That is in Downloads (let's say), with the name Summary.xlsx
Code opens the file, but Sheet(1) is not copied to the active workbook.

 

[Anthony47]

The code copy workbooks(1) from the initially closed workbook to the workbook where the macro is hosted, not to the "active workbook" (that, btw, is the freshly open workbook).

For debugging add the instructions marked ++ in the code:

Sub copyFirstWS()
    Dim wb As Workbook
    Application.EnableEvents = False        '++
    Set wb = Workbooks.Open("xyz")                                     '<<< The full path and name of the workbook to open
    Stop                                    '++1
    With ThisWorkbook
        wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count)
    End With
    Stop                                    '++2
    wb.Close savechanges:=False
    Application.EnableEvents = True         '++
End Sub

When you run the macro, it will halt on the first Stop: check that the new workbook has been open
Then return to the vba window, press F5 to continue the macro; it will halt on the second Stop: check that one worksheet har been added to your main workbook (the one with the macro)
Complete the macro by pressing again F5 in the vba window.
Tell us what you see in this test.

The two "Stop" can be then deleted, whereas the other added line is better be left in the code

 

[TryingBest]

The code copy workbooks(1) from the initially closed workbook to the workbook where the macro is hosted, not to the "active workbook" (that, btw, is the freshly open workbook).

For debugging add the instructions marked ++ in the code:

Sub copyFirstWS()
    Dim wb As Workbook
    Application.EnableEvents = False        '++
    Set wb = Workbooks.Open("xyz")                                     '<<< The full path and name of the workbook to open
    Stop                                    '++1
    With ThisWorkbook
        wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count)
    End With
    Stop                                    '++2
    wb.Close savechanges:=False
    Application.EnableEvents = True         '++
End Sub

When you run the macro, it will halt on the first Stop: check that the new workbook has been open
Then return to the vba window, press F5 to continue the macro; it will halt on the second Stop: check that one worksheet har been added to your main workbook (the one with the macro)
Complete the macro by pressing again F5 in the vba window.
Tell us what you see in this test.

The two "Stop" can be then deleted, whereas the other added line is better be left in the code

HI,

Thank you for the reply. So, attached is the error message and getting this error while the below line is executed before the second stop.

" wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count) "

 

[mumps]

Try:

Sub copyFirstWS()
    Dim desWB As Workbook, srcWB As Workbook
    Set desWB = ThisWorkbook
    Set srcWB = Workbooks.Open("C:\Users\-") 'change to suit your needs
    With srcWB
        .Sheets(1).Copy Before:=desWB.Sheets(.Sheets.Count)
        .Close savechanges:=False
    End With
End Sub

 

[Anthony47]

Don't miss the message from mumps, before this one, and try his suggestion.

I don't have any idea of what could be the situation...
Are you with Excel 2016?

Modify this part of the macro as follows:

    Stop
    With ThisWorkbook
        Debug.Print "A", wb.Name
        Debug.Print "B", wb.Worksheets(1).Name
        Debug.Print "C", .Name
        Debug.Print "D", .Worksheets(.Worksheets.Count).Name
        wb.Worksheets(1).Copy
        wb.Worksheets(1).Copy Before:=.Worksheets(1)
    End With

Then run the macro; it wil halt on the Stop before With ThisWorkbook.
Now open the vba "Immediate" window (typing Contr-g should do the job; or Menu /View /Immediate window).
Then use F8 to excecute one single instruction, one after the other.
-after executing the several "Debug.Print", check that what is displayed in the Immediate window is consistent with your situation; ie: the Name of the newly open workbook, and its Worksheet(1) Name; the Name of the main workbook (the one with the macro), and the name of its last Worksheet
-after excecuting the line wb.Worksheets(1).Copy check that a new workbook has been created that include the desired worksheet
-after excecuting the second wb.Worksheets(1).Copy, check that the desired worksheet has been inserted in the main workbook

Depending on this information maybe we can make a new hypothesis

 

[TryingBest]

Don't miss the message from mumps, before this one, and try his suggestion.

I don't have any idea of what could be the situation...
Are you with Excel 2016?

Modify this part of the macro as follows:

    Stop
    With ThisWorkbook
        Debug.Print "A", wb.Name
        Debug.Print "B", wb.Worksheets(1).Name
        Debug.Print "C", .Name
        Debug.Print "D", .Worksheets(.Worksheets.Count).Name
        wb.Worksheets(1).Copy
        wb.Worksheets(1).Copy Before:=.Worksheets(1)
    End With

Then run the macro; it wil halt on the Stop before With ThisWorkbook.
Now open the vba "Immediate" window (typing Contr-g should do the job; or Menu /View /Immediate window).
Then use F8 to excecute one single instruction, one after the other.
-after executing the several "Debug.Print", check that what is displayed in the Immediate window is consistent with your situation; ie: the Name of the newly open workbook, and its Worksheet(1) Name; the Name of the main workbook (the one with the macro), and the name of its last Worksheet
-after excecuting the line wb.Worksheets(1).Copy check that a new workbook has been created that include the desired worksheet
-after excecuting the second wb.Worksheets(1).Copy, check that the desired worksheet has been inserted in the main workbook

Depending on this information maybe we can make a new hypothesis

Hi,

So, the issue at C - "This Workbook". So, I have actually saved a file, where all my macros are saved and are available for all the excel files at all the time.
But, the Workbook that is open is the report, where I am working (of course with a different name).

So, I guess, the macros is referring (ThisWorkboos) as where all the macros are saved and not to the workbook that I am working on.

A Summary.xlsx
B Uptime
C PERSONAL_Autosave.xlsb
D Sheet1

 

[Anthony47]

For sure ThisWorkbook refers to the workbook that include the macro (see post #4)
If you need to refer for example to the workbook that was active before opening the new one then try:

Sub copyFirstWS()
    Dim wb As Workbook
    Dim cWb As Workbook
    
    Application.EnableEvents = False
    Set cWb = ActiveWorkbook
    Set wb = Workbooks.Open("XXXXXxlsm")                                     '<<< The full path and name of the workbook to open
    With cWb
        wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count)
    End With
    wb.Close savechanges:=False
    Application.EnableEvents = True
End Sub

 

[TryingBest]

For sure ThisWorkbook refers to the workbook that include the macro (see post #4)
If you need to refer for example to the workbook that was active before opening the new one then try:

Sub copyFirstWS()
    Dim wb As Workbook
    Dim cWb As Workbook
   
    Application.EnableEvents = False
    Set cWb = ActiveWorkbook
    Set wb = Workbooks.Open("XXXXXxlsm")                                     '<<< The full path and name of the workbook to open
    With cWb
        wb.Worksheets(1).Copy Before:=.Worksheets(.Worksheets.Count)
    End With
    wb.Close savechanges:=False
    Application.EnableEvents = True
End Sub

Thanks a ton! The code works perfectly.
I really admire your approach to TS and solving the issue