Move the data from one sheet to another

[fahadalambd]

Hi everyone,

I hope you are doing well and have a wonderful weekend.

I need a VBA Macro Script that automatically moves the data from "sheet 1" into the new tab based on the "Profession Column". For example, in "Pic 1" there are total 4 professions - "Doctor", "Engineer", "Teacher" & "Student". So we need to create 4 tabs ("Pic 2")

"Pic 3" is the expected output result. As you see in "Pic 3" the data for the "Doctor" profession moves here from "Sheet1"

After moving all the data it is required to delete all the data from the "sheet1"

It would be really appreciated if someone helps me to fix this issue.

Thank you so much, everyone

 

[My Aswer Is This]

You said:
I need a VBA Macro Script that automatically moves the data from "sheet 1" into the new tab based on the "Profession Column".

There is hardly anything that happens automatically when using Excel.

I suggest you use this script:
When you double click on any Sheet name in column A of your sheet 1
That row of data will be copied to the sheet name you double clicked on, and the same row will be deleted from sheet 1

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab of your sheet 1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/5/2022  12:00:55 PM  EST
Cancel = True
Application.ScreenUpdating = False
If Target.Column = 1 Then
On Error GoTo M
Dim ans As String
Dim Lastrow As Long
Dim r As Long
ans = Target.Value
r = Target.Row
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Cells(Lastrow, 1)
Rows(r).Delete
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You double clicked on:  " & ans & vbNewLine & "There is no sheet by that name."
Application.ScreenUpdating = True
End If
End Sub

 

[jolivanes]

Or just semi-automatic. Just a click on a button and magic will happen.

Sub Maybe()
Dim c As Range, sh1 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
    For Each c In sh1.Range("A2:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row)
        On Error Resume Next
            If Sheets(c.Value) Is Nothing Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value: _
            ActiveSheet.Range("A1:E1").Value = sh1.Range("A1:E1").Value: sh1.Select
        On Error GoTo 0
    c.Resize(, 5).Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next c
Application.ScreenUpdating = True
End Sub

 

[fahadalambd]

Or just semi-automatic. Just a click on a button and magic will happen.

Sub Maybe()
Dim c As Range, sh1 As Worksheet
Set sh1 = Sheets("Sheet1")
Application.ScreenUpdating = False
    For Each c In sh1.Range("A2:A" & sh1.Cells(Rows.Count, 1).End(xlUp).Row)
        On Error Resume Next
            If Sheets(c.Value) Is Nothing Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = c.Value: _
            ActiveSheet.Range("A1:E1").Value = sh1.Range("A1:E1").Value: sh1.Select
        On Error GoTo 0
    c.Resize(, 5).Copy Sheets(c.Value).Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next c
Application.ScreenUpdating = True
End Sub

Thank you so much mate. Really appreciate for your help :D

 

[fahadalambd]

You said:
I need a VBA Macro Script that automatically moves the data from "sheet 1" into the new tab based on the "Profession Column".

There is hardly anything that happens automatically when using Excel.

I suggest you use this script:
When you double click on any Sheet name in column A of your sheet 1
That row of data will be copied to the sheet name you double clicked on, and the same row will be deleted from sheet 1

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab of your sheet 1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/5/2022  12:00:55 PM  EST
Cancel = True
Application.ScreenUpdating = False
If Target.Column = 1 Then
On Error GoTo M
Dim ans As String
Dim Lastrow As Long
Dim r As Long
ans = Target.Value
r = Target.Row
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Cells(Lastrow, 1)
Rows(r).Delete
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You double clicked on:  " & ans & vbNewLine & "There is no sheet by that name."
Application.ScreenUpdating = True
End If
End Sub

Thank you so much mate. Really appreciate for your help :D

You said:
I need a VBA Macro Script that automatically moves the data from "sheet 1" into the new tab based on the "Profession Column".

There is hardly anything that happens automatically when using Excel.

I suggest you use this script:
When you double click on any Sheet name in column A of your sheet 1
That row of data will be copied to the sheet name you double clicked on, and the same row will be deleted from sheet 1

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab of your sheet 1
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  11/5/2022  12:00:55 PM  EST
Cancel = True
Application.ScreenUpdating = False
If Target.Column = 1 Then
On Error GoTo M
Dim ans As String
Dim Lastrow As Long
Dim r As Long
ans = Target.Value
r = Target.Row
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Cells(Lastrow, 1)
Rows(r).Delete
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You double clicked on:  " & ans & vbNewLine & "There is no sheet by that name."
Application.ScreenUpdating = True
End If
End Sub

Thank you so much mate. Really appreciate for your help :D This code is working perfectly.

 

[My Aswer Is This]

Thank you so much mate. Really appreciate for your help :D

Thank you so much mate. Really appreciate for your help :D

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[jolivanes]

Glad we could be of help.
Thanks