ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ yangjie ©ó 2013-9-24 16:24 ½s¿è

½Ð±Ð¤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         ÁÂÁÂ

¦^´_ 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
1

µû¤À¤H¼Æ

    • GBKEE: ºë«~¤å³¹ª÷¿ú + 2

TOP

¦^´_ 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°õ¦æ

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-9-26 10:58 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ yangjie ©ó 2013-9-26 11:27 ½s¿è

ÁÂÁ    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

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-9-26 14:56 ½s¿è

¦^´_ 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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD