VBA Extract Data and Paste to another sheet

[dbnfl]

Hello,

I'm only new to VBA and have been able to get some pretty cool stuff done to my database for my transport company.

I am trying to copy data based from my data sheet "Data" to report sheet "Pre Alert" based on 2 criteria's selected on "Pre Alert" sheet.

The code have is below. When I run the code it does everything except copy and paste.

Any help would be greatly apricated

Dale

Option Explicit
Sub Extract_Data()
Dim datasheet As Worksheet
Dim reportsheet As Worksheet
Dim jobstatus As String
Dim agent As String
Dim finalrow As Integer
Dim i As Integer
Set datasheet = Sheet1
Set reportsheet = Sheet8
jobstatus = reportsheet.Range("C3").Value
agent = reportsheet.Range("E3").Value

reportsheet.Range("B6:W200").ClearContents


datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 5 To finalrow
If Cells(i, 3) = jobstatus And Cells(i, 5) = agent Then
Range(Cells(i, 2) And Cells(i, 3) And Cells(i, 4) And Cells(i, 5) And Cells(i, 6) And Cells(i, 8) And Cells(i, 9) And Cells(i, 10) And Cells(i, 16) And Cells(i, 18) And Cells(i, 19) And Cells(i, 24) And Cells(i, 38) And Cells(i, 39) And Cells(i, 41) And Cells(i, 43) And Cells(i, 44) And Cells(i, 46)).Copy
reportsheet.Select
Range("B200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
datasheet.Select
End If
Next i
reportsheet.Select
Range("C3").Select
End Sub

 

[Fluff]

That's a good point.
To combat that how about

   jobstatus = LCase(reportsheet.Range("C3").Value)
   agent = LCase(reportsheet.Range("E3").Value)
   
   reportsheet.Range("B6:W200").ClearContents
   
   datasheet.Select
   finalrow = Cells(Rows.Count, 1).End(xlUp).Row
   For i = 2 To finalrow
      If LCase(Cells(i, 3)) = jobstatus And LCase(Cells(i, 5)) = agent Then
         MsgBox "Ok"
         Ary = Application.Index(Rows(i), 1, Array(2, 3, 4, 5, 6, 8, 9, 10, 16, 18, 19, 24, 38, 39, 41, 43, 44, 46))

 

[dbnfl]

https://1drv.ms/x/s!Ah_y3HoNCprta_rpfPlFFWn93BI

 

[Fluff]

OK, you need to make this change

   finalrow = Cells(Rows.Count, [COLOR=#ff0000]2[/COLOR]).End(xlUp).Row

Column A is blank so finalrow =1

 

[dbnfl]

thank you so much. This has made my day/night.

Now to work out how to take formula on "Data" sheet in Col's AL, AM, AN, AP, AR, AS & AT and VBA code on sheet so that when entry enters it updates. I'm hoping I can do something like if no match found leave blank or if match found copy and paste data to "Data" sheet.

 

[Fluff]

You're welcome & thanks for the feedback.
If you get stuck on the next part, you can start a new thread.