vbaexcel

vba + copy sheet from one wb to another wb


This question is covered in many forums. I have the code, but it keeps saying error: mismatch. I have no idea why...

I've got 3 WorkBooks. The one with the macro that I'm writing now (1). One wb from which the sheets needs to be copied (2) and then 1 wb to where the sheets needs to be copied to (3).

my code in (1):

Dim sh As Worksheet

Application.ScreenUpdating = False
Sheets("Sheet1").Select

PathNameOrigineel = Range("C2").Value
filenameOrigineel = Range("B2").Value

PathNameNew = Range("C3").Value
FilenameNew = Range("B3").Value

Set wbori = Workbooks.Open(PathNameOrigineel & filenameOrigineel)
Set wbNew = Workbooks.Open(PathNameNew & FilenameNew)
   
wbori.Activate
    For Each sh In wbori.Worksheets
        If sh.Name <> "Database" And sh.Name <> "Template" And sh.Name <> "Help" And sh.Name <> "OVERVIEW" And sh.Name <> "Develop" And sh.Name <> "Schedule" And sh.Name <> "Information" And sh.Name <> "Announcements" And sh.Name <> "Summary"        Then
        Sheets(sh).Copy After:=Workbooks(FilenameNew).Sheets(Sheets.Count)
        End If
    Next

Solution

  • sh is already defined as a worksheet so you don't need Sheets(sh).Copy, just sh.Copy

    For Each sh In wbori.Worksheets
        If sh.Name <> "Database" And sh.Name <> "Template" And sh.Name <> "Help" And sh.Name <> "OVERVIEW" And sh.Name <> "Develop" And sh.Name <> "Schedule" And sh.Name <> "Information" And sh.Name <> "Announcements" And sh.Name <> "Summary"        Then
        sh.Copy After:=Workbooks(FilenameNew).Sheets(wbNew.Sheets.Count)
        End If
    Next