对象变量/块变量未设置调试

Question

我不知道什么是错误的，但每当我运行宏，它给我一个运行时错误91对象变量或块变量未设置。这是一个总结，我想从一个只读工作簿（主）复制只名称（Sakinah），列A：Q并粘贴到Sakinah列A：Q。现在，复制部分工作正常，它只是粘贴部分。此代码被输入Sakinah，我已将其定义为Thisworkbook我不确定哪里出了问题。请帮忙。

下面是我的代码：我已经强调在发生错误的部分：

Sub Update_ReadOnly_Click() 

Dim copyStartCellSakinah As Long 

Dim excelFilePath 
Dim masterWorkBook As Workbook 

Dim readLastCell As Long 
Dim manager As String 
Dim A 
Dim B 
Dim C 
Dim d 
Dim E 
Dim F 
Dim G 
Dim H 
Dim I 
Dim J 
Dim K 
Dim L 
Dim M 
Dim N 
Dim O 
Dim P 
Dim Q 

Dim sheet As String 


Dim sakinahWorkBookPath As String 
Dim masterWorkBookPath As String 
Dim Sakinahworkbook As ThisWorkbook 

excelFilePath = Application.ActiveWorkbook.Path 

masterWorkBookPath = excelFilePath & "\Master.xlsm" 

Set masterWorkBook = Workbooks.Open(masterWorkBookPath, ReadOnly = True) 

'get copy row 
copyStartCellSakinah = 4 

readLastCell = masterWorkBook.Sheets("Master").Cells(Rows.Count, 

1).End(xlUp).Row + 1 

    For x = 4 To readLastCell 
    cell = "C" & x 
    manager = masterWorkBook.Worksheets("Master").Range(cell).Value 

     If UCase(manager) = "SAKINAH" Then 
      sheet = "Sakinah" 


      cell = "A" & x 
      A = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "B" & x 
      B = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "C" & x 
      C = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "D" & x 
      d = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "E" & x 
      E = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "F" & x 
      F = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "G" & x 
      G = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "H" & x 
      H = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "I" & x 
      I = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "J" & x 
      J = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "K" & x 
      K = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "L" & x 
      L = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "M" & x 
      M = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "N" & x 
      N = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "O" & x 
      O = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "P" & x 
      P = masterWorkBook.Worksheets("Master").Range(cell).Value 
      cell = "Q" & x 
      Q = masterWorkBook.Worksheets("Master").Range(cell).Value 

“直到这一点是很好，但是当我进入Sakinahworkbook.worksheets（片）这是给我的错误。

'copy 


cell = "A" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = A 
     cell = "B" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = B 
     cell = "C" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = C 
     cell = "D" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = d 
     cell = "E" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = E 
     cell = "F" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = F 
     cell = "G" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = G 
     cell = "H" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = H 
     cell = "I" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = I 
     cell = "J" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = J 
     cell = "K" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = K 
     cell = "L" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = L 
     cell = "M" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = M 
     cell = "N" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = N 
     cell = "O" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = O 
     cell = "P" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = P 
     cell = "Q" & copyStartCellSakinah 
     Sakinahworkbook.Worksheets(sheet).Range(cell).Value = Q 


     copyStartCellSakinah = copyStartCellSakinah + 1 

    End If 
    Next x 

' CLOSE THE SOURCE FILE. 
    masterWorkBook.Close True    ' FALSE - DON'T SAVE THE SOURCE FILE. 
    Set masterWorkBook = Nothing 
Application.EnableEvents = True 



'Me.Visible = True 
MsgBox "Updated Successfully" 
End Sub 

Answer 1

为了摆脱这种错误的，你必须分开申报和分配：

Dim Sakinahworkbook As ThisWorkbook 

分为：

Dim Sakinahworkbook As Workbook 
Set Sakinahworkbook = ThisWorkbook 

此外，我会建议由一个循环做拷贝操作，而不是简化了这个代码为每一列引入变量：

For x = 1 to readLastCell 
    For i = 1 to 17 
     Sakinahworkbook.Worksheets(sheet).Range(x,i).Value = masterWorkBook.Worksheets("Master").Range(x,i).Value 
    Next i 
Next x 

Answer 2

你应该声明Sakinahworkbook为Workbook，然后Set它ThisWorkbook：

Dim Sakinahworkbook As Workbook 
Set Sakinahworkbook = ThisWorkbook 

我非常惊讶的是As ThisWorkbook甚至允许语法。