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 :thumb.