VBA Help - Same Function needed for multiple sheets

[birdman15]

I am new to using VBA in Excel. Just want to get that out in the open. What I am trying to do is create an inventory list for multiple products. Basically, I will have one sheet in my workbook per device type. I will have 4 columns. 1= Serial Number / 2= MAC Address / 3= Device Type / 4= Distributed? (column 4 has a drop down list of yes or no). I want to be able to go to my list on Sheet1 and select 'yes' in the drop down list of column 4. Then, that row automatically gets moved out of Sheet1 and onto Sheet2 (A distributed list of devices). I then have some basic formulas that keep count of the device list on Sheet1 (this is not the issue).

I was able to find some code on another forum that has this functionality working great from Sheet1 to Sheet2. No issues at all, and it does exactly what I want it to do. The way I did this was Module1 set up with the code with sheet names in the code and a Sheet1 code that was also posted along wih the orignal code. See below:

Module1:

Sub MoveBasedOnValue()
    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long
    A = Worksheets("XBs Avail").UsedRange.Rows.Count
    B = Worksheets("XBs Distributed").UsedRange.Rows.Count
    If B = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("XBs Distributed").UsedRange) = 0 Then B = 0
    End If
    Set xRg = Worksheets("XBs Avail").Range("D1:D" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
    For C = 1 To xRg.Count
        If CStr(xRg(C).Value) = "yes" Then
            xRg(C).EntireRow.Copy Destination:=Worksheets("XBs Distributed").Range("A" & B + 1)
            xRg(C).EntireRow.Delete
            If CStr(xRg(C).Value) = "yes" Then
                C = C - 1
            End If
            B = B + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Z As Long
    Dim xVal As String
    On Error Resume Next
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Z = 1 To Target.Count
        If Target(Z).Value > 0 Then
            Call MoveBasedOnValue
        End If
    Next
    Application.EnableEvents = True
End Sub

However, I have other device types on other Sheets that I would like to do the same exact thing in the same workbook. I would have the same exact set up as sheet1 and sheet2 for the data side, but I would want to be able to do this same functionality but from Sheet3 (a diff device inventory list) to Sheet4 (the distributed device list for that type).

I tried creating another Module and repeating the code with the sheet names replaced with the new ones as well as Sheet2 code just copied over and I get an Ambiguous error.

Can anyone assist a novice with this?

 

[Micron]

Ambiguous Name Detected? That means you have 2 or more things with the same name and they are both in scope. If you're interested in the topic of scope:

Understanding scope and visibility (VBA)

Office VBA reference topic

learn.microsoft.com

When working in modules procedures are public by default (unless they are event procedures). You can probably fix this by ensuring all your procedures have unique names. I suspect you just copied the procedures and edited the code within them. It is best to have procedures that you can re-use if at all possible as it makes for a lot less maintenance if you have to change/add something. To do that, your procedure can accept parameters such as sheet names or range objects/addresses. Virtually anything can be passed to a procedure by way of parameters. If there are variations that can't be handled with parameters you can use decision blocks to handle the possibilities (e.g. IF or Select Case blocks of code). If you need a return value from a procedure, it must be written as a function, not a sub.

 

[birdman15]

Ambiguous Name Detected? That means you have 2 or more things with the same name and they are both in scope. If you're interested in the topic of scope:

Understanding scope and visibility (VBA)

Office VBA reference topic

learn.microsoft.com

When working in modules procedures are public by default (unless they are event procedures). You can probably fix this by ensuring all your procedures have unique names. I suspect you just copied the procedures and edited the code within them. It is best to have procedures that you can re-use if at all possible as it makes for a lot less maintenance if you have to change/add something. To do that, your procedure can accept parameters such as sheet names or range objects/addresses. Virtually anything can be passed to a procedure by way of parameters. If there are variations that can't be handled with parameters you can use decision blocks to handle the possibilities (e.g. IF or Select Case blocks of code). If you need a return value from a procedure, it must be written as a function, not a sub.

Thank you! This helped guide me.