Hi

I was wondering if anyone could help with the following spreadsheet I
am trying to produce.

Basically, everyday I run a report showing volumes of trading (varies
every day, some days only a few trades, others there are hundreds)

Each trade has a unique transaction number.

Everyday we report the trades that have been confirmed within a four
day period. However, if some trades have been reported in the previous
days report, we do not want to duplicate it, therefor have to manually
delete it.

I was wondering if there was a macro that I could create so that I can
delete any data from the sheet named "NEW" that already appears on
sheets "Day1" "Day2" "Day3" "Day4"

Then after the first macro is run i can just paste the data from sheet
"NEW" into "Day 4" the data from Day 4 into sheet "Day 3" and so on
etc.

Any help would be greatly appreciated and I hope ive made some sense of
what im trying to achieve!

Cheers

Brian


+-------------------------------------------------------------------+
|Filename: NewTest.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3661 |
+-------------------------------------------------------------------+

--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

Hi McCrimmon,

I was wondering if there was a macro that I could create so that I can
delete any data from the sheet named "NEW" that already appears on
sheets "Day1" "Day2" "Day3" "Day4"

See Chip Pearson's Tagging Duplicates page at:

http://www.cpearson.com/excel/duplic...gingDuplicates


See particularly, the section entitled: 'Extracting Values Common To Two
Lists'


---
Regards,
Norman



"mccrimmon" wrote
in message ...

Hi

I was wondering if anyone could help with the following spreadsheet I
am trying to produce.

Basically, everyday I run a report showing volumes of trading (varies
every day, some days only a few trades, others there are hundreds)

Each trade has a unique transaction number.

Everyday we report the trades that have been confirmed within a four
day period. However, if some trades have been reported in the previous
days report, we do not want to duplicate it, therefor have to manually
delete it.

I was wondering if there was a macro that I could create so that I can
delete any data from the sheet named "NEW" that already appears on
sheets "Day1" "Day2" "Day3" "Day4"

Then after the first macro is run i can just paste the data from sheet
"NEW" into "Day 4" the data from Day 4 into sheet "Day 3" and so on
etc.

Any help would be greatly appreciated and I hope ive made some sense of
what im trying to achieve!

Cheers

Brian


+-------------------------------------------------------------------+
|Filename: NewTest.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3661 |
+-------------------------------------------------------------------+

--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

The problem is that Its only one column that is unique with its own
reference so a formula like that would work, however, i need to keep
all the other data in the spreadsheet aswell, just deleting any rows
that have already been previously reported.

Could anyone have a bash at this and see what they come up with?

Cheers

McCrimmon


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

Anyone please? :

--
mccrimmo

-----------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...nfo&userid=633
View this thread: http://www.excelforum.com/showthread.php?threadid=39147

pretty please :)


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

Hi McCrimon,

Assume that the unique transaction numbers start in A2 on each sheet.

The following deletes all rows on New whose transaction number is found in
column A on any of the Day1, Day2, Day3 or Day4 sheets.

The Day1 sheet is then renamed "ArchiveCopy" with an appended date.

The following name changes are effecte:

Day4=== Day3
Day3=== Day2
Day2===Day1

Finally, the New sheet is renamed Day4.

Test this on a *copy* of your workbook.

'===============================
Sub TestIt()

Dim Arr As Variant
Dim sh As Worksheet, sh2 As Worksheet
Dim Rng1 As Range, Rng2 As Range
Dim delRng As Range
Dim rCell As Range
Dim Lrow As Long
Dim lastRow As Long
Dim i As Long

Application.ScreenUpdating = False

Arr = Array("Day1", "Day2", "Day3", "Day4")

Set sh = ThisWorkbook.Sheets("New")

Lrow = sh.Cells(Rows.Count, "A").End(xlUp).Row

Set Rng1 = sh.Range("A2").Resize(Lrow - 1)

For Each rCell In Rng1.Cells
For i = LBound(Arr) To UBound(Arr)
Set sh2 = Sheets(Arr(i))
lastRow = sh2.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = sh2.Range("A1").Resize(lastRow)
If Not IsError _
(Application.Match(rCell.Value, Rng2, 0)) Then
If Not delRng Is Nothing Then
Set delRng = Union(rCell, delRng)
Else
Set delRng = rCell
End If
Exit For
End If
Next i
Next rCell

If Not delRng Is Nothing Then delRng.EntireRow.Delete

Sheets(Arr(0)).Name = "ArchiveCopy" & _
Format(Date, "yyyy-mm-dd")

For i = LBound(Arr) + 1 To UBound(Arr)
Sheets(Arr(i)).Name = Arr(i - 1)
Next i

Sheets("New").Name = Arr(UBound(Arr))

Application.ScreenUpdating = False

End Sub
'===============================

The bove code should be pated into a normal module in the worbook holding
the data sheets.


---
Regards,
Norman



"mccrimmon" wrote
in message ...

pretty please :)


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

Norman used this line:
Arr = Array("Day1", "Day2", "Day3", "Day4")

These are the worksheet names that his code expects to find.

If you don't have a worksheet named Day1, Day2, ...

Then you'll get that error.

So you can either rename sheets or adjust that line:
Arr = Array("Day1", "Day2", "Day3", "Day4")
to match your worksheet names.

mccrimmon wrote:

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?

--
mccrimmon

------------------------------------------------------------------------
mccrimmon's Profile: http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

--

Dave Peterson

Hi McCrimmon,

The error indicates that one of the sheets whose names is included in:

Arr = Array("Day1", "Day2", "Day3", "Day4")

has not been found.

Check the names and, if necessary, look for initial, traling or intermrdiate
spaces.

If you check the value of i when the error occurs, it will point you to the
problematic sheet name. Bear in mind that the array is 0-based, so if the
error occurs when (say) i = 1, you should check for the presence of a sheet
named "Day2" (i.e, the 2nd array element). To check the value of i, set a
watch on it in the VBE.

I have a working test book if you would like it.

---
Regards,
Norman



"mccrimmon" wrote
in message ...

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread: http://www.excelforum.com/showthread...hreadid=391474

Hi McCrimmon,

Dave has already responded to your problem but, unless my selective
blindness is even more pronounced than normal, it was not there when *I*
replied.

Sorry Dave!


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi McCrimmon,

The error indicates that one of the sheets whose names is included in:

Arr = Array("Day1", "Day2", "Day3", "Day4")

has not been found.

Check the names and, if necessary, look for initial, traling or
intermrdiate spaces.

If you check the value of i when the error occurs, it will point you to
the problematic sheet name. Bear in mind that the array is 0-based, so if
the error occurs when (say) i = 1, you should check for the presence of a
sheet named "Day2" (i.e, the 2nd array element). To check the value of i,
set a watch on it in the VBE.

I have a working test book if you would like it.

---
Regards,
Norman



"mccrimmon" wrote
in message ...

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread:
http://www.excelforum.com/showthread...hreadid=391474

No problem. There's always a time lag between loading the messages, reading,
and responding.

And I think it helps--sometimes it gives alternative suggestions to solve the
same challenge.

Norman Jones wrote:

Hi McCrimmon,

Dave has already responded to your problem but, unless my selective
blindness is even more pronounced than normal, it was not there when *I*
replied.

Sorry Dave!

---
Regards,
Norman

"Norman Jones" wrote in message
...
Hi McCrimmon,

The error indicates that one of the sheets whose names is included in:

Arr = Array("Day1", "Day2", "Day3", "Day4")

has not been found.

Check the names and, if necessary, look for initial, traling or
intermrdiate spaces.

If you check the value of i when the error occurs, it will point you to
the problematic sheet name. Bear in mind that the array is 0-based, so if
the error occurs when (say) i = 1, you should check for the presence of a
sheet named "Day2" (i.e, the 2nd array element). To check the value of i,
set a watch on it in the VBE.

I have a working test book if you would like it.

---
Regards,
Norman



"mccrimmon" wrote
in message ...

with doing all of the above i seem to be getting a runtime error "9":
subscript out of range

The reference it relates to seems to be:

Set sh2 = Sheets(Arr(i))

Any suggestions?


--
mccrimmon


------------------------------------------------------------------------
mccrimmon's Profile:
http://www.excelforum.com/member.php...fo&userid=6338
View this thread:
http://www.excelforum.com/showthread...hreadid=391474




--

Dave Peterson