VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

Results 1 to 6 of 6

Thread: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

     
    Hi All,

    Long time lurker first time poster here, relatively new to VBA. I have a workbook with a series of sheets that I would like to copy (all except the first) into a different book while also replacing any sheets with the same name in the destination workbook.

    Is it possible to have a macro look at the name of the sheets in my source workbook then replace any sheets in the destination workbook if the same name exists, with any new sheets just being copied over ?

    I currently have some code used to open the destination workbook, but struggle with the process above

    Code:
      Sub CopyReplaceWorksheets()
    Dim strDestPath As String
    Dim strFileDest As String
    Dim strSourcePath As String
    Dim strSourceFile As String
    
    strDestPath = Range("A1").Value
    strFileDest = Range("A2").Value
    strSourcePath = Range("A3").Value
    strSourceFile = Range("A4").Value
    
    info = IsWorkbookOpen(strDestPath)
    If info = True Then
    MsgBox "File is being used"
    
    
    Else
    MsgBox "File is closed"
    End If
    
    
    If info = False Then
    Workbooks.Open strDestPath
    End If
    Any help or advice is much appreciated.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,162
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

    Hi & welcome to MrExcel
    Try this
    Code:
    Sub CopyReplaceWorksheets()
        
        Dim DestWbk As Workbook
        Dim SrcWbk As Workbook
        Dim Ws As Worksheet
           
        Set SrcWbk = ThisWorkbook
        
        On Error Resume Next
        Set DestWbk = Workbooks("Book11.xlsm")
        On Error GoTo 0
        If DestWbk Is Nothing Then
            Set DestWbk = Workbooks.Open("C:\Users\Fluff\Documents\Excel files\book11.xlsm")
            If DestWbk.ReadOnly Then
                MsgBox "Destination workbook is ""ReadOnly""", vbCritical, "Read Only"
                Exit Sub
            End If
        End If
        With CreateObject("scripting.dictionary")
            For Each Ws In DestWbk.Worksheets
                .Add Ws.Name, Ws
            Next Ws
    
            For Each Ws In SrcWbk.Worksheets
                If Not Ws.Index = 1 Then
                    Application.DisplayAlerts = False
                    If .exists(Ws.Name) Then .Item(Ws.Name).Delete
                    Application.DisplayAlerts = True
                    Ws.copy after:=DestWbk.Sheets(Sheets.Count)
                End If
            Next Ws
        End With
                
    End Sub
    This needs to go in the source workbook. Change the name & file path of the destination workbook (in red) to suit.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

    Hi Fluff,

    Appreciate the response ! This is definitely what I'm looking for, however I am running into a run-time error 1004 on the line
    Ws.copy after:=DestWbk.Sheets(Sheets.Count)
    The message box indicates excel cannot insert sheets into the destination workbook since it contains fewer rows and columns than the source workbook.

    There may be more or less rows of data between the sheets being transferred, is there a workaround to the error above ?

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,162
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

    Is the source book an .xlsm & the destination an .xls?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

    It was set up like that but I have since changed both to .xlsm and it appears to be working like a charm.

    Thank you very much !

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,162
    Post Thanks / Like
    Mentioned
    111 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA - Copy sheets from book1 to book2 and replace if the sheet exists in book2

      
    Glad to help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com