求助大神帮我修改代码，无法运行。。打开一个工作簿，复制其中一个工作表到另一个...

bavinfeather

按钮 ProdList   代码如下。。。现在无法运行

Prod Sample Update.rar (662.86 KB, 下载次数: 0)

2018-10-9 08:58 上传

点击文件名下载附件

Daily Sample Status Review Tool.rar (1.39 MB, 下载次数: 0)

2018-10-9 08:57 上传

点击文件名下载附件

Private Sub ProdList_Click()
'
' ProdList Macro
'
Application.ScreenUpdating = False
p = ThisWorkbook.Path & "\"
f = Dir(p & "Prod Sample Update.xlsx")
Dim fl

Dim strbook As Workbook

Dim sht As Worksheet

Dim arr

Dim i As Integer

Dim ii As Integer
Worksheets("ProdList").Range("C2:C99999").ClearContents

Do While f <> ""
         If InStr(f, "Prod") Then
             Set sht = Workbooks.Open(p & f)

With ThisWorkbook.Sheets("ProdList")

For Each sht In strbook.Sheets



If sht.Name = "sample" Then

sht.Select

  i = sht.Range("A90000").End(xlUp).Row
  
  ii = .Range("A90000").End(xlUp).Row + 1
  
  sht.Range("A2:A" & i).Copy .Cells(ii, 3)
  
  sht.Range("B2:B" & i).Copy .Cells(ii, 4)

  sht.Range("C2:C" & i).Copy .Cells(ii, 5)

  sht.Range("D2:D" & i).Copy .Cells(ii, 6)

  sht.Range("E2:E" & i).Copy .Cells(ii, 7)

  sht.Range("G2:G" & i).Copy .Cells(ii, 8)

  sht.Range("H2:H" & i).Copy .Cells(ii, 9)

  sht.Range("I2:I" & i).Copy .Cells(ii, 10)
  sht.Range("J2:J" & i).Copy .Cells(ii, 11)
  sht.Range("K2:K" & i).Copy .Cells(ii, 12)
  sht.Range("L2:L" & i).Copy .Cells(ii, 13)
  sht.Range("M2:M" & i).Copy .Cells(ii, 14)
  sht.Range("N2:N" & i).Copy .Cells(ii, 16)
sht.Range("P2:P" & i).Copy .Cells(ii, 17)
sht.Range("Q2:Q" & i).Copy .Cells(ii, 18)
sht.Range("R2:R" & i).Copy .Cells(ii, 19)
sht.Range("S2:S" & i).Copy .Cells(ii, 20)
     Set sht = Nothing

End If
  
Loop
  
Next

End With

strbook.Close False



Worksheets("ProdList").UsedRange.ClearFormats
    Sheets("ProdList").Select
    Sheets("ProdList").Calculate
    With Selection
        .WrapText = False
    End With



Sheets("ProdList").Calculate
      Worksheets("ProdList").Range("C2:O90000").ClearFormats
     Application.ScreenUpdating = True
  End If
  
End Sub

蓝桥玄霜

建议用F8逐句调试，是在哪一句上出了问题。

bavinfeather

蓝桥玄霜 发表于 2018-10-9 10:15
建议用F8逐句调试，是在哪一句上出了问题。

是loop问题…