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

 

[Fluff]

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

@m_vishal_c

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Please read rule 12 here: Forum Rules.
And whilst you are reading the rules please note what RoryA said in post#7
I have merged both threads