Copy non adj. cells from two sheets into one

Closed
ChrisCOR - Jun 24, 2011 at 01:17 AM
 ChrisCOR - Jun 24, 2011 at 06:24 AM
Hello,
On two occasions in the past this community has been of incalculable help to me, and I was wondering if there might be one more assist that you might be able to give me. I'm writing a macro for our year end inventory reconciliation reports. The general gist of the macro is to compare a list of part numbers that were counted and the physical count did not match the systems numbers. The master inventory list has a column for price that I would like to have in the newly created sheet along with the business unit(division), part #, description and the amount by which the counts differed(delta number). After putting these columns on the new sheet, the macro multiplies the delta number by the price of hte part to give the total adjustment per part. Currently my problem arises when the macro is supposed to copy the required cells from one sheet to another. I'm also not sure on how to tell it to multiply the delta number (column "E" on the created sheet) and the Price(column "F" on the created sheet).

This is my current code


Sub ReconAdjust()
'This macro compares the list of Warehouse inventory to the list of division inventory
'for the purpose of accounting for total financial adjustment from year end base-line
'counts
 
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh1row As Integer
    Dim sh2row As Integer
    Dim sh1col As Integer
    Dim sh2col As Integer
    Dim rng1ct As Range
    Dim rng2ct As Range
    Dim lastRow As Long
    Dim i As Integer
    Dim a As Integer
   
   
    Set sh1 = ActiveWorkbook.Sheets(1)
    Set sh2 = ActiveWorkbook.Sheets(2)
    sh1row = sh1.Range("b" & Rows.Count).End(xlUp).Row
    sh1col = sh1.Range("b" & Columns.Count).End(xlToLeft).Column
    Set rng1ct = sh1.Range("b3").Resize(sh1row, sh1col)
    sh2row = sh2.Range("c" & Rows.Count).End(xlUp).Row
    sh2col = sh2.Range("c" & Columns.Count).End(xlToLeft).Column
    Set rng2ct = sh2.Range("c3").Resize(sh2row, sh2col)
   
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    sh2.Range("A2, C2, D2, E2, L2").Copy Destination:=Worksheets(3).Range("A1:E1")
    sh1.Range("L2").Copy Destination:=Worksheets(3).Range("F1")
   
    With Worksheets(3)
        .AutoFilterMode = False
        .Range("A1:F1").AutoFilter
    End With
   
    a = 3
   
    For Each row1 In rng1ct
        For Each row2 In rng2ct
            If row2 = row1 Then
                row2.Range("A" & a & ",C" & a & ",D" & a & ",E" & a & ",L" & a).Copy Destination:=Worksheets(3).Range("A" & Rows.Count & ",B" & Rows.Count & ",C" & Rows.Count & ",D" & Rows.Count & ",E" & Rows.Count).End(xlUp)
                row1.sh1.Range("L" & a).Copy Destination:=Worksheets(3).Range("F" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        Next row2
        a = a + 1
    Next row1
   
    lastRow = Range("E" & Rows.Count).End(xlUp).Row
    For i = 2 To lastRow
        If Range("E" & i) = "" Then Exit For
        If Range("E" & i).Value = 0 Then
            Range("E" & i).EntireRow.Delete
            i = i - 1
        End If
    Next i
           
End Sub


I know this is a great deal to request, but any help would be greatly appreciated (I was stuck with this project at 3:45pm, its now 2:05am and I'm still at my desk trying to get this organized by 6:00am).

Thank you graciously in advance



1 response

Ok, the above code has been done away with. My focus has shifted to putting the price one the second sheet if the macro finds the item on both sheets, and then multiplying the delta number by the price to find the financial adjustment. Here is what I'm working with now, this is probably alot easier than it appears to me, but this is a new technique for me...

Sub ReconAdj()
'This macro compares the list of Warehouse inventory to the list of division inventory
'for the purpose of accounting for total financial adjustment from year end base-line
'counts

    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh1row As Integer
    Dim sh2row As Integer
    Dim sh1col As Integer
    Dim sh2col As Integer
    Dim rng1ct As Range
    Dim rng2ct As Range
    
    
    Set sh1 = ActiveWorkbook.Sheets("Price")
    Set sh2 = ActiveWorkbook.Sheets("PUCLW")
    sh1row = sh1.Range("b" & Rows.Count).End(xlUp).Row
    sh1col = sh1.Range("b" & Columns.Count).End(xlToLeft).Column
    Set rng1ct = sh1.Range("b2").Resize(sh1row, sh1col)
    sh2row = sh2.Range("c" & Rows.Count).End(xlUp).Row
    sh2col = sh2.Range("c" & Columns.Count).End(xlToLeft).Column
    Set rng2ct = sh2.Range("c2").Resize(sh2row, sh2col)
    
    For Each row1 In rng1ct
        For Each row2 In rng2ct
            If row2 = row1 Then
                sh1.Range("L" & Rows.Count).Copy Destination:=Worksheets("PUCLW").Range("I" & Rows.Count).End(xlUp)
                sh2.Range("J" & Rows.Count).Value = sh2.Range("H" & Rows.Count) * sh2.Range("I" & Rows.Count)
            End If
        Next row2
    Next row1

End Sub


Bascially the intent is for the value in column "L" of the "Price" worksheet to be copied to the "I" column on the "PUCLW" worksheet, then multiply them together and put the answer in column "J".

I'm currently researching on how to make the copy process cleaner, but when I run this, all that occurs is that a new column is inserted before the original "I" column and both columns are left blank.

Again, thank you in advance for any assistance you may be able to give me.

Chris
0