VBA Code: copy rows to a new worksheet after putting the date and considering category

poltorak1

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi this i my first thread here and I need some help with creating a VBA code that will copy rows to a new worksheet based on the criteria.

1. First tab is a master tab (worksheet)
2. First row is a header
3. In column B there will be like 6 categories, lets name it cat1, cat2, cat3, cat4, cat5, cat6
4. In columns A I will put the date of calling

When I put the date in column A, I want to automatically copy the entire row to a new tab (worksheet) but considering the category from column B. So if in column B we have cat1 it should be copied to worksheet "cat1"

Your help will be appreciated
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This assumes that column B data is an exact match for each of your worksheet names. If not, the code fails.
The code should be copied and pasted into the sheet code module. It will run when entries are made in colujmn A.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Target.EntireRow.Copy
        Sheets(Range("B" & Target.Row).Value).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
    End If
End Sub
 

poltorak1

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
This assumes that column B data is an exact match for each of your worksheet names. If not, the code fails.
The code should be copied and pasted into the sheet code module. It will run when entries are made in colujmn A.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or Target = "" Then Exit Sub
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Target.EntireRow.Copy
        Sheets(Range("B" & Target.Row).Value).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = False
    End If
End Sub
Thats perfect. Thanks man!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top