VBA Transfer Macro

Bob Chiskey · Jul 13th 2016

Dear Forum,

I currently have the below code in my workbook that transfers data from a data sheet into individual employee sheets. This works fine so far but I would like to ask if anyone could please assist me in a few adjustments?

What I am trying to achieve with the amendments would be:

1. Is it possible when the user hits the Submit button on the UserForm1 that the sheets automatically update? reason I ask is that now it works great but I need to run the code each time new data is added, and this then means duplicate entries, which would also lead me to ask;
2. if 1. is possible is there a way to avoid duplicate entries? however it is possible the same ticket number a. could appear for more than 1 user and b. could appear more than once for the same user

Code

Sub transfer()
    
    
    Dim ws As Worksheet, wsName As Worksheet
    Dim lRow As Long, lPaste As Long
    Dim sName As String
    
    
    Set ws = Worksheets("Update Quality Check Data")
    
    
    With ws
        For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            sName = .Cells(lRow, 2)
            On Error GoTo NoSheettFound
Jumper:
            Set wsName = Worksheets(sName)
            On Error GoTo 0
            lPaste = wsName.Cells(Rows.Count, 3).End(xlUp).Row + 1
            .Cells(lRow, 1).Copy Destination:=wsName.Cells(lPaste, 3)
            .Cells(lRow, 3).Copy Destination:=wsName.Cells(lPaste, 4)
        Next lRow
    End With
    
    
    Exit Sub
    
    
NoSheettFound:
    Set wsName = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsName.Name = sName
    ws.Select
    GoTo Jumper
End Sub

Display More

Kind Regards

Bob

**Ger Plante** · Jul 13th 2016

Re: VBA Transfer Macro

Not sure I follow... Why not just call the transfer procedure as part of the Submit button?

Ger

Bob Chiskey · Jul 13th 2016

Re: VBA Transfer Macro

Hi Ger,

Can you advise me please? I'm not so great with VBA (yet). I already have the below code for the Submit Buttom (CommandButton1), can you advise me where / how I add the transfer procedure?

Code

Private Sub CommandButton1_Click()


Dim emptyRow As Long


'Make Sheet2 active
 Sheet2.Activate


'Determine emptyRow
 emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


'Transfer information
 Cells(emptyRow, 1).Value = TextBox8.Value
 Cells(emptyRow, 2).Value = AgentNameBox.Value
 Cells(emptyRow, 3).Value = TicketNumberTextBox.Value
 Cells(emptyRow, 4).Value = TextBox9.Value
 Cells(emptyRow, 5).Value = NameComboBox.Value
 Cells(emptyRow, 6).Value = TextBox2.Value
 Cells(emptyRow, 7).Value = PhoneComboBox.Value
 Cells(emptyRow, 8).Value = TextBox3.Value
 Cells(emptyRow, 9).Value = StatusComboBox.Value
 Cells(emptyRow, 10).Value = TextBox4.Value
 Cells(emptyRow, 11).Value = NextStepsComboBox.Value
 Cells(emptyRow, 12).Value = TextBox5.Value
 Cells(emptyRow, 13).Value = CBTComboBox.Value
 Cells(emptyRow, 14).Value = TextBox6.Value
 Cells(emptyRow, 15).Value = SpellingComboBox.Value
 Cells(emptyRow, 16).Value = TextBox7.Value


End Sub

Display More

**Ger Plante** · Jul 13th 2016

Re: VBA Transfer Macro

Just put this call to the transfer macro right before the end of your sub...

Code

call transfer
end sub

Ger

**Ger Plante** · Jul 13th 2016

Re: VBA Transfer Macro

Note - you might also have to change the definition of transfer

from

Code

sub transfer()

to

Code

public sub transfer()

Ger

Bob Chiskey · Jul 13th 2016

Re: VBA Transfer Macro

Thanks Ger, you are the man working as I needed, many thanks!

VBA Transfer Macro

Participate now!

Share