Excel VBA automatically copy entire row to different sheets based on cell value

[nikzulfaizan]

Hi ...My excel have multiple sheets as below:

1) Master Data
2) Region A
3) Region B
4) Region C
5) Region D

I need to have a VBA coding that will COPY entire row automatically into a respective sheet if the cell value match according to the "REGION".

Whenever changes was made in the Master Data, it should auto reflect also at the respected sheets.

Below is the sample data for your ease of reference. Hope someone can help me and thank you in advanced...

SHEET : MASTER DATA

A	B	C	D	E	F	G
1	Summary
2
3	Region	Name	Floor	Section	Cubicle	Department
4	Region A	AAA	32	A	12	Audit
5	Region A	BBB	23	C	23	Audit
6	Region B	CCC	2	A	2	Operation
7	Region C	DDD	1	A	3	Operation
8	Region D	EEE	4	D	5	Admin
9	Region C	FFF	2	C	11	Admin
10	Region A	GGG	11	B	14	HR
11	Region D	HHH	4	A	24	Admin

<tbody>
</tbody>

 

[Michael M]

Whenever changes was made in the Master Data

Are these changes to overwrite data in other sheets, or be added to the other sheets.
Does the exisiting data change ?
So do all region sheets need to be cleared each time new ata is added ?

 

[My Aswer Is This]

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

When you enter a sheet name in column B that row of data will be copied to that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/10/2019  11:28:43 PM  EDT
If Target.Column = 2 And Target.Row > 3 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim Lastrow As Long
Lastrow = Sheets(Target.Value).Cells(Rows.Count, "B").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(Target.Value).Rows(Lastrow)
End If
Exit Sub
M:
MsgBox "The sheet named  " & Target.Value & "  Does not exist"
End Sub

 

[vcoolio]

Hello Nikzulfaizan,

Another option:-

Sub Test()

          Dim ar As Variant, i As Integer
          Dim ws As Worksheet, sh As Worksheet
          ar = [{"Region A","Region B","Region C","Region D";"Region A","Region B","Region C","Region D"}]
          Set ws = Sheets("Master Data")
          
Application.ScreenUpdating = False

    For i = 1 To UBound(ar, 2)
           Set sh = Sheets(ar(1, i))
           sh.UsedRange.Offset(1).Clear
           With ws.Range("A3", ws.Range("A" & ws.Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2)
                    sh.Columns.AutoFit
                    .AutoFilter
           End With
    Next i
    
Application.ScreenUpdating = True

End Sub

The code assumes that data in the Master Data sheet starts in Row4 with headings in Row3.
Each destination sheet is refreshed each time the code is executed. No data is deleted from the Master Data sheet.

I hope that this helps.

Cheerio,
vcoolio.

 

[nikzulfaizan]

Are these changes to overwrite data in other sheets, or be added to the other sheets.
Does the exisiting data change ?
So do all region sheets need to be cleared each time new ata is added ?

Hi .. thank you for your response...

My answer : Yes, to overwrite and it's applies to all.

 

[nikzulfaizan]

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

When you enter a sheet name in column B that row of data will be copied to that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  9/10/2019  11:28:43 PM  EDT
If Target.Column = 2 And Target.Row > 3 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim Lastrow As Long
Lastrow = Sheets(Target.Value).Cells(Rows.Count, "B").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(Target.Value).Rows(Lastrow)
End If
Exit Sub
M:
MsgBox "The sheet named  " & Target.Value & "  Does not exist"
End Sub

Greattttt ..... I love it ... How can i make it more flexible likes copy the entire data without enter 1 by 1. Actually the data i extract it from mysql database (export to excel format).... From raw data i copy and paste it to the sheet that we work on it. Once i paste it to the worksheet, the coding should be able to identify and run the coding...

 

[My Aswer Is This]

Your Subject Title said:

Excel VBA automatically copy entire row

My script runs when you enter the sheet name in column B

What do you mean by automatically?

Nothing happens automatically as far as I know.

I can write a script that runs when you click a button.​

Greattttt ..... I love it ... How can i make it more flexible likes copy the entire data without enter 1 by 1. Actually the data i extract it from mysql database (export to excel format).... From raw data i copy and paste it to the sheet that we work on it. Once i paste it to the worksheet, the coding should be able to identify and run the coding...

 

[nikzulfaizan]

Hello Nikzulfaizan,

Another option:-

Sub Test()

          Dim ar As Variant, i As Integer
          Dim ws As Worksheet, sh As Worksheet
          ar = [{"Region A","Region B","Region C","Region D";"Region A","Region B","Region C","Region D"}]
          Set ws = Sheets("Master Data")
          
Application.ScreenUpdating = False

    For i = 1 To UBound(ar, 2)
           Set sh = Sheets(ar(1, i))
           sh.UsedRange.Offset(1).Clear
           With ws.Range("A3", ws.Range("A" & ws.Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(3)(2)
                    sh.Columns.AutoFit
                    .AutoFilter
           End With
    Next i
    
Application.ScreenUpdating = True

End Sub

The code assumes that data in the Master Data sheet starts in Row4 with headings in Row3.
Each destination sheet is refreshed each time the code is executed. No data is deleted from the Master Data sheet.

I hope that this helps.

Cheerio,
vcoolio.

Great .... I love it .... How can i copy the entire header and paste it....

My sample header range : "A1:G3"

 

[James006]

You should know the rules about double posting ...

https://www.ozgrid.com/forum/forum/...re-row-to-different-sheet-based-on-cell-value

 

[nikzulfaizan]

Your Subject Title said:

Excel VBA automatically copy entire row

My script runs when you enter the sheet name in column B

What do you mean by automatically?

Nothing happens automatically as far as I know.

I can write a script that runs when you click a button.​

I can write a script that runs when you click a button. .--> Yes... Really appreciate it .... Btw, my sample header ranges : "A1:G3" .....