Copy selected data to new workbook
 

Hi, I'm a bit of a newbie to this and I have a problem, which I'm
pretty sure somebody can help me out with?

I'm working in Excel 2000, but the users who will need this will be
using Excel 97.

I have a cumulative report from our AS400 system, which represents
financial transactions that have been input to the system. We can run
the report each day but because it is cumulative we have to manually
identify and split out the new transactions (we can't just use dates
or just cut the end of the report because of the way the report is
created in the AS400). I was hoping that somebody would be able to
show me how to automate this within a macro?

The excel report is seven columns across and can be several thousands
of lines. Each cell in column C is always unique and this is what we
use to identify if a new item has been entered onto the report.

The current process:
Each day the report is downloaded into excel and saved as cashx, the
following day the report is run again and downloaded into excel and
saved as cashy. I would like to be able to extract the new data (in
cashy) that was input into the system by looping through cashx and
cashy and find anything new (column C identifies this) in cashy and
save it to an excel workbook (cashz).

I hope the above is clear!!!

Copy selected data to new workbook
 

Abdul, that wouldn't be practical to enter the criteria in the code,
as there are too many items!

I was hoping that a loop could identify all of the new items and then
paste those new items to the new workbook!

Copy selected data to new workbook
 

Maybe you could use a helper column and add a formula that does the compare
between worksheets.

Maybe something like:

=IF(ISNUMBER(MATCH(C1,[cashx.xls]Sheet1!$C$2:$C$9999,0)),"Old","New")

Then you could filter on that and take just the new rows.

You may want to look at some alternatives at Chip Pearson's site, too:
http://www.cpearson.com/excel/duplicat.htm


Pete wrote:

Hi, I'm a bit of a newbie to this and I have a problem, which I'm
pretty sure somebody can help me out with?

I'm working in Excel 2000, but the users who will need this will be
using Excel 97.

I have a cumulative report from our AS400 system, which represents
financial transactions that have been input to the system. We can run
the report each day but because it is cumulative we have to manually
identify and split out the new transactions (we can't just use dates
or just cut the end of the report because of the way the report is
created in the AS400). I was hoping that somebody would be able to
show me how to automate this within a macro?

The excel report is seven columns across and can be several thousands
of lines. Each cell in column C is always unique and this is what we
use to identify if a new item has been entered onto the report.

The current process:
Each day the report is downloaded into excel and saved as cashx, the
following day the report is run again and downloaded into excel and
saved as cashy. I would like to be able to extract the new data (in
cashy) that was input into the system by looping through cashx and
cashy and find anything new (column C identifies this) in cashy and
save it to an excel workbook (cashz).

I hope the above is clear!!!

--

Dave Peterson

Copy selected data to new workbook
 

Maybe create a macro that adds that formula, does the filter and copy and
paste??

I made some assumptions:

The cashx worksheet was named Sheet1. The data on that sheet was limited to
c2:c9999 (big enough to not worry).

I used Column C to find the last used row in the new cash file (cashy??).

I had one header row in my new file (cashy).

I could do whatever I wanted with column H.

If those aren't valid, you can modify the following.

Option Explicit
Sub testme01()

'=IF(ISNUMBER(MATCH(C1,[cashx.xls]Sheet1!$C$2:$C$9999,0)),"Old","New")

Dim wks As Worksheet
Dim cashxWks As Worksheet
Dim rng As Range
Dim rngF As Range
Dim newWks As Worksheet

Set cashxWks = Nothing
On Error Resume Next
'fix the worksheet name
Set cashxWks = Workbooks("cashx.xls").Worksheets("sheet1")
On Error GoTo 0

If cashxWks Is Nothing Then
MsgBox "please open current cashx workbook" & vbLf & "And try again"
Exit Sub
End If

Set wks = ActiveSheet 'workbooks("cashy.xls").worksheets("sheet1") ??

With wks
.AutoFilterMode = False 'turn off autofilter
.Range("H1").Value = "New/Old"
Set rng = .Range("h2:h" & .Cells(.Rows.Count, "C").End(xlUp).Row)
rng.Formula _
= "=IF(ISNUMBER(MATCH(C2,[cashx.xls]Sheet1!$C$2:$C$9999,0))" _
& ",""Old"",""New"")"
.Range("H:H").AutoFilter field:=1, Criteria1:="new"

Set rng = .AutoFilter.Range
On Error Resume Next
Set rngF = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rngF.Cells.Count = 1 Then
MsgBox "No new values!"
Else
Set newWks = Worksheets.Add
rngF.EntireRow.Copy _
Destination:=newWks.Range("a1")
End If

'clean up
.AutoFilterMode = False
.Range("H:H").EntireColumn.Delete

End With

End Sub




Pete wrote:

Dave,

I could also do a simple iserror lookup, but i have to think of the
users. They have very limited experience of Excel (and they don't
have the greatest of learning curves). Therefore, I would like to
make this as simple as possible i.e. at the press of a button/add-in.

Appreciate any help!

--

Dave Peterson