根据其他单元格中的值将行数据从一张纸复制到一张或多张纸上 [英] copy row data from one sheet to one or more sheets based on values in other cells

问题描述

我在A-C列中有一张包含用户详细信息的工作表.D-H列也是用户已订阅的通讯组列表.(当前复选框链接到单元格，以指示用户已订阅的列表)

I have a sheet with user details in columns A - C. Columns D-H is distribution lists the users are subscribed too. (currently check box linked to cell to indicate which list(s) user is subscribed to)

用户可以订阅的内容超过列表中的内容.

A user can be subscribed to more than on list.

目前，我可以使用可以正常工作的过滤器来过滤列表x的用户，将用户信息复制到另一张纸上，然后过滤下一个列表(用于列表选择的文本)这确实在此工作表的某些用户之间引起了一些问题.(那些不知道如何使用过滤器的人)

At the moment I can use filters which works ok to filter users for list x, copy the user info to another sheet, then next list filterd (used text for the list selection) This does cause some problems between some of the users of this sheet.(those that don't know how to use filters)

我想为每个自动创建的列表创建一个新表.当用户从列表中添加/删除时，他的详细信息会自动从相应的列表"中添加/删除.

I would like to create a new sheet for each list which gets populated automatically. As a user is added/removed from a list, his details is automatically added/removed from the corresponding 'list sheet'.

这样，没有人可以抱怨过滤器然后，他们可以根据需要将所需的列表工作表"导出到另一个xls doc或csv.

This way no one can complain about the filters At the same time they can then export the 'list sheet' they require to another xls doc or csv as required.

我找到了有关执行此操作的各种选项，但是所有这些都只有一个选择列.我以为可以更改示例代码中的某些范围等，但是由于对VB的了解有限，所有操作都失败了.

I have found various options on how to do this, but all of then had only one selection column. I thought I could alter some of the ranges etc etc in the sample code but all failed with the limited knowledge I have with VB.

有关如何执行此操作的任何建议?谢谢！

Any suggestions on how to do this? Thanks!

推荐答案

请不要尝试创建数据的两个副本.保持相同数据的两个版本非常非常困难.

Please do not try and create two copies of your data. Keeping two versions of the same data in step is very, very difficult.

我相信，最好的选择是创建一个宏，用户可以通过该宏选择所需的过滤器.

I believe your best option is to create a macro with which your users can select the filter they require.

您没有详细描述数据，所以我想到了类似以下内容的东西:

You do not describe your data in much detail so I have imagined something like the following:

Name    Addr        Tele   List1    List2   List3   List4   List5
John    London      1234   x                
Jane    Paris       2345            x           
Kevin   Stockholm   3456                    x       
Mary    Brussels    4567                            x   
Nigel   Dublin      5678                                    x
Abby    Athens      6789   x        x               x
Brian   Rome        7890                    x           

鉴于上面的布局，下面的宏显示了我将要提供的东西.

Given the above layout, the following macro shows the sort of thing I would offer.

执行宏后，它会显示如下所示的InputBox:

When the macro is executed, it displays an InputBox like this:

用户可以从中选择所需的过滤器.

from which the user can select the filter required.

我希望这会给您一些想法.

I hope this gives you some ideas.

Option Explicit
Sub SelectFilter()

  Dim ColNum() As Variant
  Dim InxList As Long
  Dim ListName() As Variant
  Dim Prompt As String
  Dim ReturnValue As Variant

  ' Load ListName with user-friendly names for the lists
  ListName = Array("Name list 1", "Name list 2", "Name list 3", _
                   "Name list 4", "Name list 5")
  ' Load ColNum with the column number for each list.  The entries in ColNum
  ' must be in the same sequence as the entries in ListName.  Column "A" is
  ' column 1, column "B" is column 2 and so on.
  ColNum = Array(4, 5, 6, 7, 8)

  ' Combine the user-friendly list names to create a menu
  Prompt = ""
  For InxList = 0 To UBound(ListName)
    Prompt = Prompt & InxList + 1 & ". " & ListName(InxList) & vbLf
  Next
  Prompt = Prompt & "Please enter the number against the list you require." _
           & vbLf & "Leave box empty to cancel selection."

  ' Loop until user cancels or enters a permitted value
  Do While True
    ReturnValue = InputBox(Prompt, "Select filter")
    If VarType(ReturnValue) = vbBoolean Then
      If Not ReturnValue Then
        ' The documentation for InputBox claims it returns False if
        ' the user clicks Cancel.  In my experience it return a
        ' null string but check to be on the safe side.
        Exit Sub
      Else
        ' True is not a documented return value from InputBox.
        ' This code should never be executed but if something
        ' goes wrong there is a message for the user.
        Call MsgBox("Please report there has been a InputBox " & _
                    "error type 1 to Chaka", vbCritical)
        Exit Sub
      End If
    End If
    If VarType(ReturnValue) <> vbString Then
      ' False or a string are the only documented return values
        ' This code should never be executed but if something
        ' goes wrong there is a message for the user.
       Call MsgBox("Please report there has been a InputBox " & _
                    "error type 2 to Chaka", vbCritical)
       Exit Sub
    End If
    If ReturnValue = "" Then
      ' User has clicked cancel or left the text box empty.
      Exit Sub
    End If
    If IsNumeric(ReturnValue) Then
      InxList = ReturnValue - 1
      If InxList >= 0 And InxList <= UBound(ListName) Then
        ' Good selection
        Exit Do
      End If
    End If
  Loop

  With Sheets("Sheet2")
    If .AutoFilterMode Then
      ' AutoFilter is on.  Cancel current selection before applying
      ' new one because criteria are additive.
      .AutoFilterMode = False
    End If

    .Cells.AutoFilter Field:=ColNum(InxList), Criteria1:="x"
  End With

End Sub

这篇关于根据其他单元格中的值将行数据从一张纸复制到一张或多张纸上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！