vba Run Time error '-2147221080(800401a8), Method 'Cells' of object'_Workdsheet' Failed

[m_vishal_c]

HI i have below code and it gives "" Run Time error '-2147221080(800401a8), Method 'Cells' of object'_Workdsheet' Failed "" . please guide

Private Sub CommandButton1_Click()


Dim xlBook As Workbook
Dim xlNewBook As Workbook
Dim xlSheet As Worksheet
Dim LastRow, myrow, lastColumn, rowPointer As Long
Dim MergeSheet, RocSheet As Worksheet
Dim MergeWorkbook As Workbook
Dim RocWorkbook As Workbook


Dim i As Long


Const strPath As String = "D:\TestFolder\New"
Const strROC As String = "D:\TestFolder\New\New ROC Template 22-12-17.xlsx"
Const strMerge As String = "D:\TestFolder\New\2COO-67 ROC Merge file.xlsx"
Set MergeWorkbook = Workbooks.Add(strMerge)
'Set MergeWorkbook = Workbooks(strMerge)
Set RocWorkbook = Workbooks.Add(strROC)


Set MergeSheet = MergeWorkbook.Worksheets("Sheet1")
Set RocSheet = RocWorkbook.Worksheets("Roc Template")


LastRow = MergeSheet.Cells(Rows.Count, "a").End(xlUp).Row
myrow = 2
    
       
    For rowPointer = 1 To LastRow
        
        'Set xlNewBook = Workbooks.Add(Template:=strFileB)
            RocSheet.Cells(2, 4).Value = MergeSheet.Cells(myrow, 1).Value
            RocSheet.Cells(3, 4).Value = MergeSheet.Cells(myrow, 2).Value
            RocSheet.Cells(4, 4).Value = MergeSheet.Cells(myrow, 3).Value
            'RocWorkbook.SaveAs strPath & CStr(RocSheet.Range("D" & 3)) & ".xlsx"
            RocWorkbook.SaveAs strPath & CStr(RocSheet.Range("D3")), 51
            RocWorkbook.Close 0
            Set RocWorkbook = Nothing
            myrow = myrow + 1
            
           
    Next rowPointer
     


End Sub

heaps thanks in advance

 

[Leith Ross]

Hello m_vishal_c,

On which line does the error occur?

 

[m_vishal_c]

Hello m_vishal_c,

On which line does the error occur?

first time execution, it works fine but when 2nd time comes in loop then it gives error
this below line

RocSheet.Cells(2, 4).Value = MergeSheet.Cells(myrow, 1).Value

thanks

 

[Fluff]

It's because you are closing the "RocWorkbook" inside the loop. So on the second iteration the workbook no longer exists.

 

[m_vishal_c]

hi Thanks for reply. I moved that code outside for loop but now it gives me " Object required, runtime error 424 ". please guide me

 

[Fluff]

What line gives that error?

 

[RoryA]

Please take a minute to read the forum rules on cross-posting (which are the same in all the forums you have posted in) and follow them. Thanks.

 

[m_vishal_c]

how to solve vba Automation run time error '-2147221080 (800401a8)

hi
i am trying to copy cell value from one file to another file and save that workbook for that i am using below code but i am facing error in For loop

First time it execute well but send time it gives errror

[/COLOR][COLOR=#333333]Dim i As Long[/COLOR]
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Const strPath As String = "D:\TestFolder\New\"
Const strROC As String = "D:\TestFolder\New\New ROC Template 22-12-17.xlsx"
Const strMerge As String = "D:\TestFolder\New\Merge file.xlsx"
Set MergeWorkbook = Workbooks.Add(strMerge)
'Set MergeWorkbook = Workbooks(strMerge)
Set RocWorkbook = Workbooks.Add(strROC)

Set MergeSheet = MergeWorkbook.Worksheets("Sheet1")
Set RocSheet = RocWorkbook.Worksheets("Roc Template")

LastRow = MergeSheet.Cells(Rows.Count, "a").End(xlUp).Row
myrow = 2
    
       
    For rowPointer = 1 To LastRow
       
        
            RocSheet.Cells(2, 4).Value = MergeSheet.Cells(myrow, 1).Value
            RocSheet.Cells(3, 4).Value = MergeSheet.Cells(myrow, 2).Value
            RocSheet.Cells(4, 4).Value = MergeSheet.Cells(myrow, 3).Value
            RocSheet.Cells(5, 4).Value = MergeSheet.Cells(myrow, 4).Value
            RocSheet.Cells(6, 4).Value = MergeSheet.Cells(myrow, 6).Value
            RocSheet.Cells(11, 4).Value = MergeSheet.Cells(myrow, 5).Value
            RocSheet.Cells(12, 4).Value = MergeSheet.Cells(myrow, 8).Value
            RocSheet.Cells(13, 4).Value = MergeSheet.Cells(myrow, 9).Value
            RocSheet.Cells(15, 4).Value = MergeSheet.Cells(myrow, 10).Value
            RocSheet.Cells(16, 4).Value = MergeSheet.Cells(myrow, 11).Value
            RocSheet.Cells(18, 2).Value = MergeSheet.Cells(myrow, 12).Value
            RocSheet.Cells(20, 2).Value = MergeSheet.Cells(myrow, 13).Value
            RocSheet.Cells(22, 4).Value = MergeSheet.Cells(myrow, 14).Value
            RocSheet.Cells(22, 5).Value = MergeSheet.Cells(myrow, 15).Value
            
            'RocWorkbook.SaveAs strPath & CStr(RocSheet.Range("D" & 3)) & ".xlsx"
            RocWorkbook.SaveAs strPath & CStr(RocSheet.Range("D3")), 51
           
            RocWorkbook.Close 0
            MergeWorkbook.Close 0
            Set RocWorkbook = Nothing
            
            myrow = myrow + 1
           
            
       
    Next rowPointer
        
        
 </code>[COLOR=#333333]End Sub[/COLOR][COLOR=#333333]

..

heaps Thanks

 

[JonXL]

Re: how to solve vba Automation run time error '-2147221080 (800401a8)

The problem, I believe, lies with the placement of this block:

            RocWorkbook.Close 0
            MergeWorkbook.Close 0
            Set RocWorkbook = Nothing

Because you have it inside the loop, it closes the files you need on the first pass through - so they aren't available when it runs through the second time.

And that is the error you get.

Try moving that block after the Next rowPointer line.

 

[m_vishal_c]

Re: how to solve vba Automation run time error '-2147221080 (800401a8)

The problem, I believe, lies with the placement of this block:

            RocWorkbook.Close 0
            MergeWorkbook.Close 0
            Set RocWorkbook = Nothing

Because you have it inside the loop, it closes the files you need on the first pass through - so they aren't available when it runs through the second time.

And that is the error you get.

Try moving that block after the Next rowPointer line.

Heaps thanks JonXL. Much appreciate. you have solved my big issue

thanks again