VBA to copy and paste based on filter and date

[justin1021]

Appreciate anyone's help creating the VBA code to execute a copy and paste function base upon certain criteria being selected. I think it needs to be a VBA script because I have a macro to clear the data in the New PO's tab once an "order" is written and the information is copied to the PO's tab. I currently have a copy and paste script which works perfect and copies 3 cells from the New PO tab to the POs tab, see below script. This is being executed by a button to "Copy the Data".

I am now looking for a VBA script which will recognize the Category selected from the drop down in Column G and copy the total quantity in column S and extended cost in Column Z to the last row available in the POs tab is the column for the correct month which can be found on the New Po's tab in cell T12. The additional challenge is that when the category changes for example from 00 to 01, it should drop to the next row on the PO's tab and change category as well. I have categories from 00-99 as possible options.

Here are screen shots of the two tabs in discussion and again appreciate any insight into how to make this work.

New Po Tab: View image: New PO Tab
PO Tab: View image: PO tab

Sub Copy_Data()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("New PO").Range("T12")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("POs")
Lr = DestSheet.Cells(Rows.Count, "C").End(xlUp).Row

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("C" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


'fill in the Source Sheet and range
Set SourceRange = Sheets("New PO").Range("T13")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("POs")
Lr = DestSheet.Cells(Rows.Count, "D").End(xlUp).Row

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("D" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


'fill in the Source Sheet and range
Set SourceRange = Sheets("New PO").Range("N10")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("POs")
Lr = DestSheet.Cells(Rows.Count, "B").End(xlUp).Row

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("B" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("New PO").Range("S44")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("POs")
Lr = DestSheet.Cells(Rows.Count, "I").End(xlUp).Row

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("I" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

With Application
.ScreenUpdating = True
.EnableEvents = True
End With


End Sub