how to solve vba Automation error

[m_vishal_c]

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

Please Login or Register  to view this content.

please guide me . heaps thanks in advance

[bakerman2]

Please use Code Tags when posting code. TY

Shouldn't it be

Please Login or Register  to view this content.

[m_vishal_c]

Hi Bakerman2

thanks for replying. but now its shows below error
"Method 'Cells' of Object'_Worksheet' Failed.

and it diverts me on

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

do i need to place another Thread or someone can answer in same.

[m_vishal_c]

hi guys , please guide me
thanks

[Marc L]

Hi !

Maybe I know where is your obvious logic error but as you did not respect forum rules …
So edit your initial post & add code tags or use the specific icon # …

[m_vishal_c]

hi as you informed me use specific icon, do you mean to use Post icon ?
thanks

[scottiex]

Mouse over the # sign above where you post messages - it will say "Wrap code tags around selected text"

you want to edit your post - select your code - and click this

do this because it makes the forum look neater and makes it easy for us to select the code.

it is a mandatory forum rule as per Marc above, afterwards it will look like bakerman's code.

[m_vishal_c]

hi please guide me

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

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.Close 0
Set RocWorkbook = Nothing
myrow = myrow + 1


Next rowPointer


End Sub

please guide me . heaps thanks in advance

[m_vishal_c]

hi Please let me know, is this right format. sorry inconvenience. heaps thanks

[m_vishal_c]

Please Login or Register  to view this content.

Please help me. Please read above corrospondance . i did not know about rules so by mistake i placed another forum but please consider this. Other one i am going to marked as "Solved", there is no any other option. thanks

[jason.b75]

First of all, you need to edit your original post and add code tags to your code there. Reposting the code is not following the rules, it's just making a mess of the thread.

Also, as was pointed out in your duplicate thread on this forum, it is also a cross post. This means that we cannot help you until you provide links to your cross posts in your original post, or to be more specific, we cannot help you until you correct all of your rule breaches.

Anticipating that you will ask for guidance again, please read the section below. Alterantively, you could go to the top of the page and click on 'Forum Rules'. If you read them they give you some great guidance on how to post without breaking the rules.

At the moment, your thread is breaking rule #8, your original post is breaking rule #3 and posts 8 and 10 could be viewed as indirectly breaking rule #12.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[m_vishal_c]

thanks for letting me know. my question was answered / solved by some other website. so please ignore this tread. and let me know how can i delete this tread on this website
thanks

[jason.b75]

You cannot delete your thread.

Even though you no longer require assistance from us we would still appreciate you following the rules and making the corrections asked of you.