Need macro help

[rcbricker]

Hello all long time no post!

I have a new job and new set of problems that I need your help with. I have workbook with multiple sheets. I have data coming in as checks paid that needs to be sorted and the data moved to the appropriate sheet. In the end each line item will end up on only one sheet depending on the criteria. there are a lot of parts to this so I think it might be better to go one part at a time till we get them all figured out.

I would like this to be a simple cut and paste into the first sheet and then click a button and have the workbook sort the data out into the appropriate pages.

Part one is to create the button that will run the VBA/Macros. We can begin by having it sort the sheet by invoice number and identify the duplicates.

Spreadsheet is attached.

[mdmackillop]

To assign a button, check out http://vbaexpress.com/forum/showthre...ewpost&t=11142

[VBA]Option Explicit

Sub SortAndMark()
Dim Rng As Range
Set Rng = Sheets("Master").Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
DoSort Rng
MarkDups Rng
Range("A1").Select
End Sub

Sub DoSort(Rng As Range)
Rng.Resize(, 3).Select
Rng.Resize(, 3).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub MarkDups(Rng As Range)
Dim cel As Range, c As Range
Dim firstaddress As String
For Each cel In Rng
If cel.Interior.ColorIndex <> 6 Then
With Rng
Set c = .Find(cel, LookIn:=xlValues, After:=Range("A1"))
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Address <> firstaddress Then
c.Interior.ColorIndex = 6
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
End If
Next
End Sub
[/VBA]

[rcbricker]

I was just told that I will need more information than what I had. So I am posting both the SS that I receive and the data that I will need to keep. I need a way to get rid of the information that I do not need, reorganize the information into the format on the Organized sheet, and finally paste on the master sheet of the first SS AND then have it look for duplicates.

Any questions?

AND THANKS FOR THE HELP!

[mdmackillop]

Are you wanting to add the new data below the existing or to replace the existing data on Master? Where does the IN or INA prefix come from?

[rcbricker]

IN stands for Invoice and INA stands for Invoice adjusted.

The master list on the first spreadsheet was not complete I need all the information from workbook 2 sheet 2 extracted from sheet 1 and copied into the Master sheet in workbook 1.

Just so we are clear. Workbook 2 is a list containing an itemized check paying invoices. Workbook 1 is the format that will identify problems with the payment (such as duplicates) so that we can address each problem. I deleted most of the rows. The data found in in workbook 2 is the first 25 lines from a 5000 line spreadsheet. There are going to be multiple conditions that we seperate out the data into a given sheet in workbook 1. Each invoice will only appear on one sheet.

Questions?

And thanks for your time.

[mdmackillop]

Open both files, Run code from the button on Master

[rcbricker]

That works great except I need one change. Sometimes the spreadsheet comes with a little different set of columns. However, the four I need are always part of the data. Is it possible to tell the import to match the column headers and bring only the data in the columns that match?

Thanks

And there will be more steps. After we get this working I need code to start moving the information to be identified and then moved to the proper sheets.

[mdmackillop]

You'll need to define the column headings, required order and any possible variations to these names.

[rcbricker]

I identified the columns and the ones I will need are on page two of WB 177776B file. They should remain the same and if they ever change I can copy in the correct name. If you can just make it so it looks for these four headers and imports the data into the correct columns on the master sheet of Autozone test that would be great.

[rcbricker]

I forgot the attachment and edit didn't offer a chance to attach.

[mdmackillop]

[VBA]Sub SortAndImportData(wb As Workbook)
Dim Headings, h, c As Long
Headings = Array("PMTDT", "INVDT", "AMT", "INV#")
With wb.Sheets(1)
For Each h In Headings
c = .Rows(1).Find(h).Column
.Columns(c).Cut
.Columns(1).Insert
Next
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 4).Copy
End With
Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True
End Sub
[/VBA]

[rcbricker]

[vba]Sub SortAndImportData(wb As Workbook)
Dim Headings, h, c As Long
Headings = Array("PMTDT", "INVDT", "AMT", "INV#")
With wb.Sheets(1)
For Each h In Headings
c = .Rows(1).Find(h).Column
.Columns(c).Cut
.Columns(1).Insert
Next
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 4).Copy
End With
Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True
End Sub
[/vba]

the above code needs to replace:

[VBA]
Sub SortAndImportData(wb As Workbook)
With wb.Sheets(1)
.Columns("E:E").Cut
.Columns("B:B").Insert
.Columns("G:G").Cut
.Columns("D").Insert
.Columns("E:G").ClearContents
Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 4).Copy
End With
Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True
End Sub
[/VBA]

In the original fix that you gave me?

And this will find the headers ("PMTDT", "INVDT", "AMT", "INV#") copy the data and paste them into the columns under the appropriately named headers in the master sheet?

Thanks

[rcbricker]

I got it to work.

Ok next Part.

We need to break the data up. These payments routinely have Duplicates payments, Duplicate deductions, Deductions, Adjustments, and duplicate adjustments. I need to identify these and move them to their appropriate places.

In the INV# column there are invoices that begin with INA or A, these are adjustments. I need those moved to the "working Adj" page. This needs to be done first as there will sometimes be negative values in the "AMT" column.

Step two is back on the master sheet we then need to move all duplicates and their original counterparts to the "working Dup" sheet.

Step 3 is to move all negative values, in the "AMT" column, that are left on the "master" sheet to the "working Ded" sheet.

There are a few more parts after this but we are getting close to finishing.

[rcbricker]

I ran the new spreadsheet and it did a great job pulling the correct information. However, when I ran it on a long spreadsheet the Duplicate function highlighted invoices that were not duplicates.

I am zipping the long spreadsheet and attaching it so you all can see what it does.

[mdmackillop]

I've no idea what "Working" sheets means. You must be specific.

[rcbricker]

It is the name of the sheet. The second through fourth sheets are "working sheets" it is where the data is initially moved to before further manipulation will occur.

Example:

All invoices that start with INA or A will be moved to sheet "working Adj" from here further manipulation will occur based on the type of adjustment.

[mdmackillop]

There is no sheet called "working ADJ" in your sample.

EDIT
OK, I found it in your new sample.

[rcbricker]

OK try again. I must have sent the wrong one sorry.

The first sheet on this one is the "Master" then the "working adj"

[rcbricker]

bump!