[µo°Ý] ¥i§_¤£¥Î¥´¶}B.xls,C.xls´N¥i¶i¦æsheets(1)¶×¥X ¶×¤J

yangjie

½Ð±Ð¤j¤j:
                ±¡ªp¬O¦p¦¹:
      A.xls¤U¥¨¶°VBAÀ³¦p¦ó¤U»yªk? ¥i¨Ï
                           B.xls¤º sheets(array("sheet1","sheet2","sheet3"))½Æ»s¨ì  C.xls¤º(B.xls,C.xls©|¥¼WorkBooks().OPen)
                 1. ¥i§_¤£¥Î¥´¶}B.xls,C.xls´N¥i¶i¦æ¶×¥X ¶×¤J?VBAÀ³¦p¦ó¤U»yªk?
                 2.­Y¤@©w±o¥´¶}    ¥´¶}®É¦]B.xls,C.xls¥»¨­¦³¥¨¶° À³¦p¦ó¤£Ä²µo Sub ¦A¶i¦æ¶×¥X ¶×¤J?VBAÀ³¦p¦ó¤U»yªk?
                                                                                             ·q½Ð¨ó§U¦h¤é¨Óªº§xÂZ         ÁÂÁÂ

stillfish00

¦^´_ 1# yangjie
2.    Application.EnableEvents = False

1. Sub Test()
   
2.   Dim wb1 As Workbook, wb2 As Workbook
   
3.   
   
4.   Application.EnableEvents = False
   
5.   Application.ScreenUpdating = False
   
6.   Application.DisplayAlerts = False
   
7.   
   
8.   Set wb1 = Workbooks.Open("C:\B.xls")
   
9.   Set wb2 = Workbooks.Open("C:\C.xls")
   
10.   wb1.Sheets(Array("sheet1", "sheet2", "sheet3")).Copy After:=wb2.Sheets(wb2.Sheets.Count)
    
11.   wb1.Close False
    
12.   wb2.Close True
    
13.   
    
14.   Application.DisplayAlerts = True
    
15.   Application.ScreenUpdating = True
    
16.   Application.EnableEvents = True
    
17. End Sub

½Æ»s¥N½X

yangjie

¦^´_ 2# stillfish00
   ÁÂÁÂ stillfish00
                  «Ü¶¶§Q    ¸U¤À·P¿E   ¦b¦¸½Ð±Ð
                 wb1.Sheets(Array("¾Ç¥Í°ò¥»¸ê®Æ", "½Ð°²¨Æ¥Ñ", "¤é´Á")).Copy After:=wb.Sheets(wb.Sheets.Count)
                ­Y Sheets(Array("¾Ç¥Í°ò¥»¸ê®Æ", "½Ð°²¨Æ¥Ñ", "¤é´Á")) ¥u­n¦³¤@­Ó¤£¦s¦b ©Î "½Ð°²¨Æ§O"®t¤@¦rµ¥, «h·|¥X²{ error¦s¤£¦s¦b
                À³¦p¦ó§PÂ_ Sheets(Array("¾Ç¥Í°ò¥»¸ê®Æ", "½Ð°²¨Æ¥Ñ", "¤é´Á"))¦s¤£¦s¦b,, ¦s¤£¦s¦b³£¨SÃö«Y?
               ¥u­n¥i¨Ï  wb1.Sheets(Array("¾Ç¥Í°ò¥»¸ê®Æ", "½Ð°²¨Æ¥Ñ", "¤é´Á")).Copy After:=wb.Sheets(wb.Sheets.Count)¶¶§Q°õ¦æ

yangjie

¦^´_ 3# yangjie
½Ð±Ð
                  wb1.Sheets(Array("sheet1", "sheet2", "sheet3")).Copy After:=wb.Sheets(wb.Sheets.Count)
                  ­Y wb¤w¦³Sheets("sheet1"),Sheets("sheet3"),¥i§_¥ý±Nwb.Sheets("sheet1") §ó¦W³Æ¥÷¦b®Çwb.Sheets("sheet1_old")  
                 ¦A¶i¦æ  wb1.Sheets(Array("sheet1", "sheet2", "sheet3")).Copy After:=wb.Sheets(wb.Sheets.Count)
             ½Ð±Ð  À³¦p¦ó¤U»yªk          ÁÂÁ¨ó§U

stillfish00

¦^´_ 3# yangjie

1. Sub Test()
   
2.   Dim wb1 As Workbook, wb2 As Workbook
   
3.   Dim s
   
4.   
   
5.   Application.EnableEvents = False
   
6.   Application.ScreenUpdating = False
   
7.   Application.DisplayAlerts = False
   
8.   
   
9.   Set wb1 = Workbooks.Open("C:\B.xls")
   
10.   Set wb2 = Workbooks.Open("C:\C.xls")
    
11.   
    
12.   On Error GoTo ERR_HANDLE
    
13.     For Each s In Array("sheet1", "sheet2", "sheet3")
    
14.       wb1.Sheets(s).Copy After:=wb2.Sheets(wb2.Sheets.Count)
    
15.     Next s
    
16.   On Error GoTo 0
    
17.   
    
18.   wb1.Close False
    
19.   wb2.Close True
    
20.   
    
21.   Application.DisplayAlerts = True
    
22.   Application.ScreenUpdating = True
    
23.   Application.EnableEvents = True
    
24.   Exit Sub
    
25.    
    
26. ERR_HANDLE:
    
27.   '©¿²¤(°}¦C¯Á¤Þ¶W¥X½d³ò)¿ù»~
    
28.   If Err.Number = 9 Then Resume Next
    
29.   
    
30.   'Other Error
    
31.   Debug.Print "Error :" & Err.Number & Chr(13) & Err.Description
    
32.   Stop
    
33. End Sub

½Æ»s¥N½X

stillfish00

¦^´_ 4# yangjie
§Ú°õ¦æ«á¡A­Y wb¤w¦³sheet1, sheet3, ·|½Æ»s¦¨ sheet1 (1)¡Bsheet3 (1)¡A­ì¥»´N¤£·|Âл\¸ê®Æ¤F¡A¤£¾å±o³Æ¥÷¬O¬Æ»ò¥Î·N?

yangjie

ÁÂÁ    stillfish00
¨Æ¦]    1.  VBA¼g¦bA.xls¸Ì ±±¨î¤u§@ªí  «ü©w¦WºÙsheets("sh1")
            2.  A.xls¨ó§U¬Y¾Ç®Õ§@¼Æ¦ìºÞ²z ¦Ó¾Þ§@¤H­û¤ñ¸û¤£¼ô.xls
            3.  §Ú¨C­×§ïVBA©Î¼W¥\¯à.¦¨·sªºA.xls  ¦ý´Á¶¡¦b¾Ç®ÕªºA.xls¤S¦³³Ì·s¸ê®Æ¦bsheets("sh1") ¸Ìµ¥µ¥¡A¬G¶·±N¾Ç®ÕªºA.xls.sheets("sh1") ¸Ìµ¥µ¥sheet   copy ¦Ü ·sªºA.xls¦¨  .sheets("sh1") µ¥µ¥ ¦Ó«Dsheets("sh1(2)") ¡C
         ¦³ÂI½ÆÂø  Sorry
            4.  ¦]¾á¤ß¾Þ§@¤H­û¤ñ¸û¤£¼ô.xls ¦Ó¨C¨C·d¿ù ¬G¼g¦¨¶Ì¥Ê«¬ªí³æ³B²z

yangjie

¦^´_ 6# stillfish00

§Ú¨Ì¤j¤j«ü¾É  §@¦p¦¹ step by step¼gªk   ¦ý³t«×¤£§Ö
À³¦p¦ó­×­q
Sub copysheet1()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim wb As Workbook
    Dim Path1, str1, str2 As String
    Path1 = Application.ActiveWorkbook.Path
    Set wb = ActiveWorkbook
    wb.Activate
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ChDrive Split(Path1, ":")(0)
    ChDir Path1
    Dim Filt As String
    Dim FilterIndex As Integer
    Dim FileName As Variant
    Dim xlfileName As String
    Dim Title As String
    Filt = "Excel Files (*.xls),*.xls"
    FilterIndex = 5
    Title = "¿ï¾Ü¸ê®Æ¶×¤J¤§¨Ó·½ExcelÀÉ"
    FileName = Application.GetOpenFilename _
        (FileFilter:=Filt, _
         FilterIndex:=FilterIndex, _
         Title:=Title)
    If UCase(FileName) = "FALSE" Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    xlfileName = Dir(FileName)
    If IsOpen(xlfileName) Then
        Workbooks(xlfileName).Activate
        Set wb1 = Workbooks(xlfileName)
    Else
        Set wb1 = Workbooks.Open(FileName, True, False)
    End If
    wb1.Activate
   
    For i = 1 To wb1.Sheets.Count
        If wb1.Sheets(i).Name <> "°ê¤¤" Then
            For j = 1 To wb.Sheets.Count
                If wb.Sheets(j).Name = wb1.Sheets(i).Name Then
                    wb.Sheets(j).Copy After:=wb.Sheets(wb.Sheets.Count)
                    wb.Sheets(j).Delete
                    Exit For
                End If
            Next
            wb1.Sheets(i).Copy After:=wb.Sheets(wb.Sheets.Count)
        End If
    Next
    wb1.Close SaveChanges:=False
'    wb2.Close True
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

stillfish00

¦^´_ 8# yangjie
³o¼Ë¨ä¹ê¬O¤£¦nªº§ó·s¤èªk¡A¦ý¬O­n§A­«·s§âVBA»P¸ê®Æ³¡¤À¤À¶}¤S¤£¤Ó²{¹ê¡A8# ªºcode ¥i¥H§â

1.             For j = 1 To wb.Sheets.Count
   
2.                 If wb.Sheets(j).Name = wb1.Sheets(i).Name Then
   
3.                     wb.Sheets(j).Copy After:=wb.Sheets(wb.Sheets.Count)
   
4.                     wb.Sheets(j).Delete
   
5.                     Exit For
   
6.                 End If
   
7.             Next
   
8.             wb1.Sheets(i).Copy After:=wb.Sheets(wb.Sheets.Count)

½Æ»s¥N½X

§ï¦¨¤U­±¸Õ¬Ý¬Ý(¨Ì4#©Ò»¡§ó¦W¡Acopy §ï move...¤Ï¥¿wb1¤£Àx¦s)

1.             For j = 1 To wb.Sheets.Count
   
2.                 If wb.Sheets(j).Name = wb1.Sheets(i).Name Then
   
3.                     wb.Sheets(j).Name = wb.Sheets(j).Name & "_old"
   
4.                     Exit For
   
5.                 End If
   
6.             Next
   
7.             wb1.Sheets(i).Move After:=wb.Sheets(wb.Sheets.Count)

½Æ»s¥N½X

yangjie

¦^´_ 9# stillfish00
ÁÂÁ«ü¾É OK ¤F