Value before error in cell and corresponding cells

[RioDegenero]

I will appreciate help with VBA code to copy the value in the last cell of column D before errors (which in this case is D16), corresponding date in column A and change in column E to a different spreadsheet.
The last value before the errors in column D will not always be located in D16 as there may be more or less transactions.
Many thanks in advance

 

[RioDegenero]

It is not enough to put the "does not work" you must put here the error message sent by the macro. That way I can know the problem and can help you.

Check that in the output workbook you have a sheet with the name "Summary", check that the name of the sheet is spelled correctly and that it does not have spaces to the left or to the right. How do you do this? very easy, edit the name of the sheet and write the name "Summary" again, save the file and try the macro again.

The code worked!!!
Thanks so much for your help. There was space to the right of the tab name before and once that was sorted it worked and works

 

[offthelip]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End Sub

 

[DanteAmor]

copy the value in the last cell of column D before errors (which in this case is D16), corresponding date in column A and change in column E

Again you do not indicate where to paste.

The following pastes into sheet2 in cell A2

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      .Range("A" & i & ",D" & i & ":E" & i).Copy Sheets("Sheet2").Range("A2")
    End If
  End With
End Sub

 

[RioDegenero]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End S
[QUOTE="offthelip, post: 6027642, member: 419487"]
try this:
[CODE=vba]
Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End Sub

[/QUOTE]

Again you do not indicate where to paste.

The following pastes into sheet2 in cell A2

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
 
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      .Range("A" & i & ",D" & i & ":E" & i).Copy Sheets("Sheet2").Range("A2")
    End If
  End With
End Sub

Thanks for your response and I wanted to copy to sheet 2 cells C5, C8,C10 and C12.

The code above is giving me error the error "variable not defined". Any suggestions?

 

[offthelip]

Two possible solutions:
1: delete the option explicit statement
2: add statements
Dim lastrow
Dim inarr

 

[DanteAmor]

Thanks for your response and I wanted to copy to sheet 2 cells C5, C8,C10 and C12.

I guess the order A in C5, B in C8, C in C10 and E in C12
But you need to be more specific so we don't have to guess what you want.

Try this:

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Sheet2")
  
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      sh2.Range("C5").Value = .Range("A" & i).Value
      sh2.Range("C8").Value = .Range("B" & i).Value
      sh2.Range("C10").Value = .Range("C" & i).Value
      sh2.Range("C12").Value = .Range("E" & i).Value
    End If
  End With
End Sub

 

[RioDegenero]

I guess the order A in C5, B in C8, C in C10 and E in C12
But you need to be more specific so we don't have to guess what you want.

Try this:

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  Dim sh2 As Worksheet
 
  Set sh2 = Sheets("Sheet2")
 
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      sh2.Range("C5").Value = .Range("A" & i).Value
      sh2.Range("C8").Value = .Range("B" & i).Value
      sh2.Range("C10").Value = .Range("C" & i).Value
      sh2.Range("C12").Value = .Range("E" & i).Value
    End If
  End With
End Sub

Thanks

 

[DanteAmor]

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Regards.

 

[RioDegenero]

Thanks

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Regards.

Not quite, I guess that was because I wasn't clear from my initial post - apologies for that. I have just reposted in another thread making it very clear of the location and also what I wanted. I really appreciate your help