Transferring rows with variable criteria

  • Thread starter Thread starter chris100
  • Start date Start date
C

chris100

Hi all,

As always thanks for the great help...without it i'd be very very stuc
- which i am now at the moment!

Following is a little macro which copies rows with a specified date t
another sheet:-

Sub ARCHIVEDAILYLIST()

Dim SourceRange As Range, TargetRange As Range
Dim SearchRange As Range, LastWrittenCell As Range
Dim i, n As Integer, k As Integer, ItemToSearchFor
'--------------------------------------
' User definitions
n = 15 ' number of columns to append
Set SourceRange = [FF1] 'this a list of different dates pertaining t
different sales in General format e.g 06/10/05 = 38631 (U.K English)
Set TargetRange = Range("ARCHIVE!A1") 'Sheet to append to
'--------------------------------------
Set SearchRange = Range(SourceRange, SourceRange.End(xlDown))
If IsEmpty(TargetRange) Then
Set LastWrittenCell = TargetRange
k = 0
Else
k = 1
If IsEmpty(TargetRange.Offset(1, 0)) Then
Set LastWrittenCell = TargetRange
Else
Set LastWrittenCell = TargetRange.End(xlDown)
End If
End If

ItemToSearchFor = [FG1] 'Source of date to look for
If ItemToSearchFor = "" Then
Exit Sub
End If

For Each i In SearchRange
If i.Value = ItemToSearchFor Then
Range(i, i.Offset(0, n - 1)).Copy LastWrittenCell.Offset(k + j, 0)
j = j + 1
End If
Next
Sheets("ARCHIVE").Select

End Sub

I need to adapt this for a different sheet where a number becomes th
criteria in ItemToSearchFor.

Specifically, i need to to copy any rows in that column that have
positive or non null value i.e where a sale has occurred. If i'm no
making myself clear below is a simplified table:

DATE PRODUCT QUANTITY

A E 1
A F
A G 2
A H 3

In this example all rows would be transferred except for Product "F
where no sale was made.

I'm afraid i'm not very good at VBA but i'm sure this could be adapte
to suit my needs. Again thanks to all the great help from you guys
 
Just bringing this up to the top as i notice no one's viewed over the
last 3 days. Anyone with suggestions don't be shy.... :)
 
Back
Top