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]

Hi & welcome to MrExcel.
Try

   Dim Ary As Variant
   datasheet.Select
   finalrow = Cells(Rows.Count, 1).End(xlUp).Row
   For i = 2 To finalrow
      If Cells(i, 3).Value = jobstatus And Cells(i, 5).Value = agent Then
         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))
         reportsheet.Range("B200").End(xlUp).Offset(1, 0).Resize(, 18).Value = Ary
      End If
   Next i

 

[dbnfl]

Hello,

Thank you for he reply. pasted in your suggestion and still the same. The code runs as it clears the cells (I add to cells to make sure they clear out) and it selects cell c3when done. I just cannot get it to copy he data I want and paste.

Dale.

PS Can I some how attach my file.

 

[Fluff]

You cannot upload files to the site, it's not allowed.
If nothing is getting copied check the spelling of whatever is in C3 & E3 compared to the cells in the data sheet.
If you add the msgbox as shown and run the code, does it appear?

      If Cells(I, 3).Value = jobstatus And Cells(I, 5).Value = agent Then
        [COLOR=#0000ff] MsgBox "Ok"[/COLOR]
         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]

Added msg box and it doesn't come up. Spelling is all ok as they are selected from dropdown lists

 

[Fluff]

If the msgbox doesn't appear then you do not have any rows on the data sheet where col C = jobstatus AND col E = agent.
Make sure that the values do not have any leading/trailing spaces.

 

[dbnfl]

I checked spelling and for spaces. all looks ok. I'm not sure what I've done wrong.

I tried changing jobstatus to another status an still no msgbox or copy paste.

I can feel is something very simple but its doing m head in.....

 

[Fluff]

Can you upload the file to a share site such as OneDrive, GoogleDrive, DropBox, mark for sharing & post the link to the thread?

 

[dmt32]

Fluff,
I liked your suggestion better so did not post but just a thought, could it be case sensitive issue maybe?

Dave

 

[dbnfl]

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

I hope i've done it correct