Vlookup in table to copy multiple sheets

Question

I'm trying to get my VBA code to do a vlookup match in a table with 3 columns and when it finds a match it should copy the right sheets to another file.

I have a userform which a username has to be entered in. Then it matches this username (column A) to a cell with sheetnames in (column C). How can I define this vlookup to match and then copy the multiple sheets. And how should I format the sheetnames in column C?

My table looks like this right now: A: User (for example "Alexandra") B: Password (for example "Test 1") C: Sheets (for example "Sheet 1, Sheet 2")

Thanks!

EDIT:

My current code for the password authorization, this is based on a Userform:

Private Sub cmdLogin_Click()
Dim RowNo As Long
Dim Id As String, pw As String
Dim ws As Worksheet
Dim aCell As Range

On Error GoTo ErrorHandler

If Len(Trim(txtLogin)) = 0 Then
txtLogin.SetFocus
MsgBox "Gebruikersnaam moet ingevuld zijn."
Exit Sub
End If

If Len(Trim(txtPassword)) = 0 Then
txtPassword.SetFocus
MsgBox "Wachtwoord moet ingevuld zijn."
Exit Sub
End If

Application.ScreenUpdating = False

Set ws = Worksheets("PW")
Id = LCase(Me.txtLogin)

Set aCell = ws.Columns(1).Find(What:=Id, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

'~~> If match found
If Not aCell Is Nothing Then
RowNo = aCell.Row
'~~> Rest of your code. For example if the password is
'~~> Stored in Col B then
'~~> Replace txtPassword with the actual name of password textbox
If Me.txtPassword = aCell.Offset(, 1) Then
Call CopySheets()
Unload Me
Else
MsgBox "De ingevoerde gegevens zijn onjuist. Probeer het opnieuw.", vbOKOnly
End If
Else '<~~ If not found
MsgBox "De ingevoerde gegevens zijn onjuist. Probeer het opnieuw.", vbOKOnly
End If
CleanExit:
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume CleanExit
End Sub

Now I need the code to have the sheets copied based on the input in txtLogin.

If you have code you should include it in your question. Exactly which part of this is giving you problems? — Tim Williams
Code has been added. The copying code with input from the txtlogin vlookup is giving me problems. I don't know how to start this code. — McKai

Tim Williams Tim Williams · Accepted Answer · 2014-02-12T17:10:31

If you place the sheetnames in ColumnC like this:

Sheet1|Sheet2|Sheet3

(ie. separated using the "pipe" character) then you can do something like:

'following from finding aCell....
Dim arr
arr = split(aCell.Offset(0,2),"|")
Sheets(arr).Copy '<<if you have a wb you want to copy to then put it here...

Vlookup in table to copy multiple sheets

1 Answers