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

 

[BORUCH]

​

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

I'm confused what is the exact code to do what i want to do please if you can post
thanks

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: vba to copy one sheet to another sheet
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rlv01]

This works on my PC:

Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Dim fname As String, SheetName As String, S As String
    Dim ExistFile As Boolean, ExistSheet As Boolean
    
    SheetName = "Roll Out Summary"
    fname = "C:\Users\jack\Documents\abcd\Sales invoice.xlsx"
    
    ExistFile = (Dir$(fname) <> "")
    
    If Not ExistFile Then
        MsgBox "File '" & fname & "'" & vbCr & "not found", vbInformation
        Exit Sub             'file not found
    End If
    
    Set sourceBook = Workbooks.Open(fname)
    With sourceBook
        On Error Resume Next
        S = .Sheets(SheetName).Name
        ExistSheet = (S = SheetName)
        On Error GoTo 0
        
        If Not ExistSheet Then
            MsgBox "Sheet '" & SheetName & "'" & vbCr & "not found", vbInformation
            Exit Sub          'sheet not found
        End If
         Application.ScreenUpdating = False        
        .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        .Close False
        Application.ScreenUpdating = True
    End With
End Sub

 

[BORUCH]

This works on my PC:

Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Dim fname As String, SheetName As String, S As String
    Dim ExistFile As Boolean, ExistSheet As Boolean
   
    SheetName = "Roll Out Summary"
    fname = "C:\Users\jack\Documents\abcd\Sales invoice.xlsx"
   
    ExistFile = (Dir$(fname) <> "")
   
    If Not ExistFile Then
        MsgBox "File '" & fname & "'" & vbCr & "not found", vbInformation
        Exit Sub             'file not found
    End If
   
    Set sourceBook = Workbooks.Open(fname)
    With sourceBook
        On Error Resume Next
        S = .Sheets(SheetName).Name
        ExistSheet = (S = SheetName)
        On Error GoTo 0
       
        If Not ExistSheet Then
            MsgBox "Sheet '" & SheetName & "'" & vbCr & "not found", vbInformation
            Exit Sub          'sheet not found
        End If
         Application.ScreenUpdating = False       
        .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        .Close False
        Application.ScreenUpdating = True
    End With
End Sub

Hi

For some reason this code only works if i first unhide my personal.xlsb file and then run your code ,then it adds the sheet to the personl.xlsb file
but if i don't unhide it i get the error "copy method of worksheet class failed" any reason as to why that happens

 

[rlv01]

So are the workbook you are trying to move sheets to is your personal macro workbook (personal.xlsb)? That changes the rules somewhat, as that is a bit of a special case. You'll need to unhide it to add any sheets to it.

Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Dim fname As String, SheetName As String, S As String
    Dim ExistFile As Boolean, ExistSheet As Boolean
    
    SheetName = "Roll Out Summary"
    fname = "C:\Users\jack\Documents\abcd\Sales invoice.xlsx"
    
    ExistFile = (Dir$(fname) <> "")
    
    If Not ExistFile Then
        MsgBox "File '" & fname & "'" & vbCr & "not found", vbInformation
        Exit Sub             'file not found
    End If
    
    Set sourceBook = Workbooks.Open(fname)
    With sourceBook
        On Error Resume Next
        S = .Sheets(SheetName).Name
        ExistSheet = (S = SheetName)
        On Error GoTo 0
        
        If Not ExistSheet Then
            MsgBox "Sheet '" & SheetName & "'" & vbCr & "not found", vbInformation
            Exit Sub          'sheet not found
        End If
        Application.ScreenUpdating = False
        Windows("PERSONAL.XLSB").Visible = True
        .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
        ActiveWindow.Visible = False
        .Close False
        Application.ScreenUpdating = True
    End With
End Sub

 

[BORUCH]

n

So are the workbook you are trying to move sheets to is your personal macro workbook (personal.xlsb)? That changes the rules somewhat, as that is a bit of a special case. You'll need to unhide it to add any sheets to it.

Sub CopySheetFromClosedWorkbook()
    Dim sourceBook As Workbook
    Dim fname As String, SheetName As String, S As String
    Dim ExistFile As Boolean, ExistSheet As Boolean
   
    SheetName = "Roll Out Summary"
    fname = "C:\Users\jack\Documents\abcd\Sales invoice.xlsx"
   
    ExistFile = (Dir$(fname) <> "")
   
    If Not ExistFile Then
        MsgBox "File '" & fname & "'" & vbCr & "not found", vbInformation
        Exit Sub             'file not found
    End If
   
    Set sourceBook = Workbooks.Open(fname)
    With sourceBook
        On Error Resume Next
        S = .Sheets(SheetName).Name
        ExistSheet = (S = SheetName)
        On Error GoTo 0
       
        If Not ExistSheet Then
            MsgBox "Sheet '" & SheetName & "'" & vbCr & "not found", vbInformation
            Exit Sub          'sheet not found
        End If
        Application.ScreenUpdating = False
        Windows("PERSONAL.XLSB").Visible = True
        .Sheets(SheetName).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.count)
        ActiveWindow.Visible = False
        .Close False
        Application.ScreenUpdating = True
    End With
End Sub

No I'm not trying to copy it to personal.xlsb the problem I'm having is that the code above in order for it to work, it must be put in a module in the workbook that you are copying the sheet to
if i store the code in the standard personal.xlsb it doesn't work I'm not sure why

 

[rlv01]

No I'm not trying to copy it to personal.xlsb the problem I'm having is that the code above in order for it to work, it must be put in a module in the workbook that you are copying the sheet to
if i store the code in the standard personal.xlsb it doesn't work I'm not sure why

It is because you are using the 'ThisWorkbook' object. If you move the code into personal.xlsb, then when you run it, 'ThisWorkbook' refers to personal.xlsb. Try changing your code to use 'ActiveWorkbook' instead

         Application.ScreenUpdating = False        
        .Sheets(SheetName).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        .Close False

 

[BORUCH]

It is because you are using the 'ThisWorkbook' object. If you move the code into personal.xlsb, then when you run it, 'ThisWorkbook' refers to personal.xlsb. Try changing your code to use 'ActiveWorkbook' instead

         Application.ScreenUpdating = False       
        .Sheets(SheetName).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        .Close False

Thank you very much this worked