Need help correcting macro

[michaelsmith559]

The code worked once, but it is not working now. It errors on "Set rng2 =" and says rng2 = nothing. Here is the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rng As Range, rng2 As Range, myData As Workbook
    Dim lastRow As Long, answer As Integer, lastrow2 As Long
    
    answer = MsgBox("Do you want to save recorded data?", vbYesNo + vbQuestion, "Save Data")

If answer = vbYes Then
    With Application
        .Iteration = False
    End With
    
lastrow2 = Worksheets("Eur_Usd").Range("O1048576").End(xlUp).Row

Set rng = Worksheets("Eur_Usd").Range("J2", Range("J" & lastrow2).End(xlToRight))
    Worksheets("Sheet2").Range("J2").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
    lastRow = Worksheets("Sheet2").Range("O1048576").End(xlUp).Row
    
Set rng2 = Worksheets("Sheet2").Range("J2", Range("J" & lastRow).End(xlToRight))
    Set myData = Workbooks.Open("C:\Documents\Eur_Usd 2016\Eur_Usd_File_2016.xlsm")
    
    With Worksheets("Sheet1")
        Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng2.Cells.Value
    End With
    myData.Save
    
    Worksheets("Eur_Usd").Range("H4").Value = 1
    If Not Me.Saved Then
        Msg = "Do you want to save the changes you made to "
        Msg = Msg & Me.Name & "?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case Ans
            Case vbYes
                Me.Save
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
Else
End If
End Sub

 

[tygrrboi]

Does this work?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rng As Range, rng2 As Range, myData As Workbook
    Dim lastRow As Long, answer As Integer, lastrow2 As Long
    
    answer = MsgBox("Do you want to save recorded data?", vbYesNo + vbQuestion, "Save Data")

If answer = vbYes Then
    With Application
        .Iteration = False
    End With
    
lastrow2 = Worksheets("Eur_Usd").Range("O1048576").End(xlUp).Row

Set rng = Worksheets("Eur_Usd").Range("J2:J" & lastrow2).End(xlToRight)
    Worksheets("Sheet2").Range("J2").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
    lastRow = Worksheets("Sheet2").Range("O1048576").End(xlUp).Row
    
Set rng2 = Worksheets("Sheet2").Range("J2:J" & lastRow).End(xlToRight)
    Set myData = Workbooks.Open("C:\Documents\Eur_Usd 2016\Eur_Usd_File_2016.xlsm")
    
    With Worksheets("Sheet1")
        .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng2.Cells.Value
    End With
    myData.Save
    
    Worksheets("Eur_Usd").Range("H4").Value = 1
    If Not Me.Saved Then
        Msg = "Do you want to save the changes you made to "
        Msg = Msg & Me.Name & "?"
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case Ans
            Case vbYes
                Me.Save
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
Else
End If
End Sub

 

[michaelsmith559]

It now gives an error for "Set myData =". Says it cannot find my file and asks if it has been deleted, changed, etc.

 

[tygrrboi]

Thats probably because its been missing the whole time (even before) but it previously crashed before that line, so you did not get the error message until now.

I do not know where that file is... Did you delete it? Rename it? Check to see if its there...

 

[michaelsmith559]

Yes the file is there. It has not been renamed, deleted, or anything. I can click on documents, eur_usd 2016 (folder), and eur_usd_file_2016 (file) is right where it is suppose to be. Also, I had to change
"Range("J2:J" & lastrow2).End(xlToRight)" back to what I had originally. This works fine except for Set rng2, not sure why this is giving an error.

 

[tygrrboi]

The only reason I could think of is that the capitalisation is off... or its not an xlsm file...

Try opening that file and in the VBA Immediate window type
?ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name

Copy that and paste it into the code again...

 

[michaelsmith559]

Thanks for the help. I finally have it working. Instead of the second Set rng 2, I had to use copy and paste.