PDA

View Full Version : Need macro help



rcbricker
01-23-2007, 11:40 AM
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
01-23-2007, 12:39 PM
To assign a button, check out http://vbaexpress.com/forum/showthread.php?goto=newpost&t=11142

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

rcbricker
01-23-2007, 02:05 PM
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
01-23-2007, 02:32 PM
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
01-24-2007, 07:05 AM
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
01-24-2007, 12:04 PM
Open both files, Run code from the button on Master

rcbricker
01-24-2007, 01:38 PM
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
01-24-2007, 01:41 PM
You'll need to define the column headings, required order and any possible variations to these names.

rcbricker
01-24-2007, 02:02 PM
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
01-24-2007, 02:05 PM
I forgot the attachment and edit didn't offer a chance to attach.

mdmackillop
01-24-2007, 03:00 PM
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

rcbricker
01-25-2007, 07:31 AM
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


the above code needs to replace:


Sub SortAndImportData(wb As Workbook)
With wb.Sheets(1)
.Columns("E:E").Cut
.Columns("B:B").Insert
.Columns("G:G").Cut
.Columns("D: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


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
01-25-2007, 08:15 AM
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
01-25-2007, 12:28 PM
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
01-25-2007, 01:12 PM
I've no idea what "Working" sheets means. You must be specific.

rcbricker
01-25-2007, 01:37 PM
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
01-25-2007, 01:51 PM
There is no sheet called "working ADJ" in your sample.

EDIT
OK, I found it in your new sample.

rcbricker
01-25-2007, 01:56 PM
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
01-29-2007, 09:29 AM
bump!

:cool: