vba to copy one sheet to another sheet

[BORUCH]

Hi all

I'm having this problem with the below vba code

 Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Application.ScreenUpdating = False
    Set sourceBook = Workbooks.Open("C:\Users\jack\Documents\abcd\Sales invoice.xlsx")
    With Workbooks(sourceBook.Name)
    .Sheets("aaa").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    sourceBook.Close
    Application.ScreenUpdating = True
end with
End Sub

I'm getting error "copy method of worksheet class failed"

it might have to do something with my personal.xlsb file I'm not sure

any help is appreciated

thanks

 

[Marc L]

Hi,​

as it obviously depends on which codeline the error occurs but you forgot to mention it !​

​

Anyway removing the useless :​

Sub CopySheetFromClosedWorkbook()
        Application.ScreenUpdating = False
    With Workbooks.Open("C:\Users\jack\Documents\abcd\Sales invoice.xlsx")
        .Sheets("aaa").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        .Close False
    End With
        Application.ScreenUpdating = True
End Sub

 

[BORUCH]

Hi,​

​

as it obviously depends on which codeline the error occurs but you forgot to mention it !​

​

Anyway removing the useless :​

Sub CopySheetFromClosedWorkbook()
        Application.ScreenUpdating = False
    With Workbooks.Open("C:\Users\jack\Documents\abcd\Sales invoice.xlsx")
        .Sheets("aaa").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        .Close False
    End With
        Application.ScreenUpdating = True
End Sub

The error occurs on this line

.Sheets("aaa").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

and I'm still having the same issue

 

[Marc L]

​

So the source sheet does not exist - check its name - or for some reason the issue is on destination workbook side so try manually​

and if it works activate the Macro Recorder and redo the same operation in order to compare with the generated code …​

 

[BORUCH]

​

So the source sheet does not exist - check its name - or for some reason the issue is on destination workbook side so try manually​

and if it works activate the Macro Recorder and redo the same operation in order to compare with the generated code …​

this is how the macro recorder does it
Workbooks.Open Filename:= _ "C:\Users\jack\Documents\abcd\Sales invoice.xlsx" Sheets("Roll Out Summary").Select Sheets("Roll Out Summary").Copy After:=Workbooks("wwww.xlsx").Sheets( _ 1) ActiveWindow.Close End Sub

THE problem is i don't want to have to specify the sheet name it can always be a different sheet name so it works like this but it wouldn't be efficient to me

 

[Marc L]

​

So it was your bad as the sheet did not exist ‼​

​

Now you must elaborate your strategy to copy some sheet without it's name …​

 

[BORUCH]

​

So it was your bad as the sheet did not exist ‼​

​

Now you must elaborate your strategy to copy some sheet without it's name …​

ok my goal is to open a workbook in a certain directory and copy the sheet named "Roll Out Summary" to the other active workbook that i originally opened and then close the other workbook where it copied it form
Thanks

 

[Marc L]

​

So obviously just update the sheet name in the post #2 VBA procedure …​

 

[BORUCH]

​

So obviously just update the sheet name in the post #2 VBA procedure …​

​

So obviously just update the sheet name in the post #2 VBA procedure …​

Its not working maybe you can rewrite the code

 

[Marc L]

​

Or use your post #5 code (without the Select codeline) …​