Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy & paste-special-values data to selected worksheets | Excel Discussion (Misc queries) | |||
Make copy of workbook based on selected criteria | Excel Worksheet Functions | |||
copy selected tabs from multiple workbooks to a new workbook | Excel Worksheet Functions | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
how can I duplicate or copy a workbook then divide selected cells. | Excel Discussion (Misc queries) |