Copy Contents From Worksheet to Another Worksheet

[Dean P.]

I have a spreadsheet with a worksheet named Team. I would like to clear all
of the rows except row 1 from Team_A worksheet each time the script is
executed. Next use the criterias listed below to copy the contents from Team
worksheet to Team_A worksheet.


Thanks so much for the help.

Team worksheet (Criteria to copy the row)
1. First record start in row 7.
2. Copy Columns A to Z
3. If record in Column K is greater than current day copy the record

 

[OssieMac]

Hi Dean,

This should do what you want.

Sub Copy_Data()
Dim wsA As Worksheet
Dim ws As Worksheet
Dim rowNumb As Long
Dim rngK As Range
Dim c As Range


Set wsA = Sheets("Team_A")
Set ws = Sheets("Team")

With wsA
rowNumb = .UsedRange.Rows.Count
.Range(.Rows(2), .Rows(rowNumb)).ClearContents
End With

With ws
Set rngK = .Range(.Cells(7, "K"), _
.Cells(.Rows.Count, "K").End(xlUp))
End With

For Each c In rngK
If c.Value > Date Then
Range(c.Offset(0, -10), c.Offset(0, 15)).copy _
Destination:=wsA.Cells(Rows.Count, "K") _
.End(xlUp).Offset(1, -10)
End If
Next c
End Sub