Paste data to another tab based on meeting criteria

[psthink]

Hi,

Hoping someone can help, I've got a workbook where the user inputs data and once "submit" is pressed the data is cut and pasted to another workbook.

Currently the data is pasted onto one tab however I'd like it pasted to a specific tab based on information in the data.

The data includes a name and I'm wondering if it's possible that when copy and pasting the line of data it pastes to the corresponding named tab on another workbook.

Name is input into cell L3 in the 'input' tab, example names are Matt and Joe. The file 'MTA Log' has two tabs named Matt and Joe.

Here's my current code (Not experienced so put together by watching youtube videos and helpful advice from this forum):

Sub submit2()
If Application.CountBlank(Range("B3:K3")) = 0 Then
  With Rows(3)
    .Copy
    Workbooks.Open Filename:="N:\REPORTING\Liam\MTA Log.xlsm"
    Rows(3).insert
    ActiveWorkbook.Close True
    Application.CutCopyMode = False
    .ClearContents
  End With
  Range("C3").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""ddd"")"
    Range("B3").Select
Else
  MsgBox "Information missing, please make sure all cells are filled before submitting - if no premium enter £0."
End If
End Sub

[rollis13]

Have a try with some changes.

Option Explicit
Sub submit2()
    Dim newTab   As String                          '<= added
    If Application.CountBlank(Range("B3:K3")) = 0 Then
        With Rows(3)
            .Copy
            newTab = Range("L3")                    '<= added
            Workbooks.Open Filename:="N:\REPORTING\Liam\MTA Log.xlsm"
            Workbooks("MTA Log.xlsm").Sheets(newTab).Rows(3).Insert '<= changed
            ActiveWorkbook.Close True
            Application.CutCopyMode = False
            .ClearContents
        End With
        Range("C3").Select
        ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""ddd"")"
        Range("B3").Select
    Else
        MsgBox "Information missing, please make sure all cells are filled before submitting - if no premium enter £0."
    End If
End Sub

[psthink]

Thank you that works perfectly! Really appreciate your help I think it's time to invest in a course to learn more!

[rollis13]

Glad having been of some help .