mperrah
09-14-2007, 07:08 AM
This is a new one I'm working on.
This is basically an inventory tracker and parts allocator
As new products come in I have an input range that I scan barcodes into
then if the products are receivers, the input gets added to the receiver sheet totals
If the new products are Small Parts, they get added to the small parts sheet totals
I have a data validation list in the input range to pick what type of item.
I only have one code to process the data,
I would like a way to use the same code and pick the target based on the dv cell.
This is what I have so far..
Sub UpdateLogWorksheet()
Dim rcvrWks As Worksheet
Dim prtsWrks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
'cells to copy from Input sheet
myCopy = "D4,D6,D7,D8,D9,D10"
Set inputWks = Worksheets("Input")
Set rcvrWks = Worksheets("Receivers")
Set prtsWrks = Worksheets("Parts")
With rcvrWks ' value of dv cell goes here?
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With rcvrWks
oCol = 1
For Each myCell In myRng.Cells
.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub
This is basically an inventory tracker and parts allocator
As new products come in I have an input range that I scan barcodes into
then if the products are receivers, the input gets added to the receiver sheet totals
If the new products are Small Parts, they get added to the small parts sheet totals
I have a data validation list in the input range to pick what type of item.
I only have one code to process the data,
I would like a way to use the same code and pick the target based on the dv cell.
This is what I have so far..
Sub UpdateLogWorksheet()
Dim rcvrWks As Worksheet
Dim prtsWrks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
'cells to copy from Input sheet
myCopy = "D4,D6,D7,D8,D9,D10"
Set inputWks = Worksheets("Input")
Set rcvrWks = Worksheets("Receivers")
Set prtsWrks = Worksheets("Parts")
With rcvrWks ' value of dv cell goes here?
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With rcvrWks
oCol = 1
For Each myCell In myRng.Cells
.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End Sub