Excel VBA Run Time Error 1004 “Application-Defined or Object-Defined Error”

Question

I am writing a program that currently, when the user clicks "OK", saves the current file, copies certain values in the file; then pastes the values to a different file. Then both files close. My problem is that it gives me an error:

Run Time Error: 'Application-Defined or Object Defined Error'

It highlights this line:

ThisWorkbook.Worksheets("data").Range("C31", Range("C106").End (xlToRight)).Copy

I separated this line and the one right after it to test it alone and it worked like I wanted to. I tested the rest of the program without those two lines and it worked like I expected it to. I tried to limit it to just copying one cell and it worked. I made sure that the name of the sheet was correct. I tried to just copy one row:

Range("C31", range("C31").End(xlToRight)) 

it still did not work and I came across the same error message. I want to say I referenced something wrong but I don't see it. Any reply at all would be helpful, Thank you.

P.S. I am sorry for redundancy in the code, I don't use "Dim" as much as I should.

Sub Button425_Click()
Dim FName As String
Dim FPath As String
Dim yourmsg As String
Dim testmsg As String
yourmsg = "Are you sure that you want to save and exit?"
testmsg = MsgBox(yourmsg, vbOKCancel + vbExclamation)
FPath = "I:\a\d\f\daily log recycle\"
FName = Sheets("Sheet1").Range("C3").Text
If testmsg = 1 Then
    ThisWorkbook.SaveAs filename:=FPath & "\" & FName
    Workbooks.Open ("I:\a\d\f\new daily log 1.xlsm")
    ThisWorkbook.Worksheets("data").Range("C31", Range("C31").End(xlToRight)).Copy
    Workbooks("new daily log 1.xlsm").Worksheets("data").Range("D31").PasteSpecial xlPasteValues
    ThisWorkbook.Worksheets("sheet1").Range("E45").Copy
    Workbooks("new daily log 1.xlsm").Worksheets("sheet1").Range("E44").PasteSpecial
    Workbooks("new daily log 1.xlsm").Worksheets("sheet1").Range("E45").ClearContents
    Workbooks("new daily log 1.xlsm").Save
    Workbooks("new daily log 1.xlsm").Close
    ThisWorkbook.Saved = True
    Application.Quit
Else 'do nothing
End If

End Sub

Answer 1

You're mixing a fully addressed range

ThisWorkbook.Worksheets("data").Range("C31"

with a relatively addressed range

Range("C106").End (xlToRight)

Try

 ThisWorkbook.Worksheets("data").Range("C31", ThisWorkbook.Worksheets("data").Range("C106").End (xlToRight))

Answer 2

I see a couple of issues:

FPath = "I:\a\d\f\daily log recycle\"
ThisWorkbook.SaveAs filename:=FPath & "\" & FName

When you attempt to save you will have ...\daily log recycle\\<filename>. Remove one of the backslashes \, I'd suggest removing the one in the .SaveAs line.

Then, with these two lines:

Workbooks.Open ("I:\a\d\f\new daily log 1.xlsm")
ThisWorkbook.Worksheets("data").Range("C31", Range("C31").End(xlToRight)).Copy

The ThisWorkbook probably isn't pointing to the one you think it is. On all the following lines you specify Workbooks("new daily log 1.xlsm")., why don't you replace ThisWorkbook with an explicit Workbooks("...") so you can be 100% certain of which of the two open workbooks you're referencing.

I would also do that for these two lines:

ThisWorkbook.Worksheets("sheet1").Range("E45").Copy
ThisWorkbook.Saved = True

Also, just to be clear, ThisWorkbook.Saved = True tells Excel to not prompt you to save the workbook when you close it, but it doesn't actually save the workbook. If you want to actually save it, you'll need to use .Save, just like you do a couple lines above that for Workbooks("new daily log 1.xlsm").Save.