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

N

nikzulfaizan

New Member
Joined
Oct 9, 2014
Messages
14
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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Summary [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Region[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Floor[/TD]
[TD="align: center"]Section[/TD]
[TD="align: center"]Cubicle[/TD]
[TD="align: center"]Department[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Region A[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]Audit[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Region A[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]Audit[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Region B[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Operation[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Region C[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Operation[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Region D[/TD]
[TD="align: center"]EEE[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Admin[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Region C[/TD]
[TD="align: center"]FFF[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]Admin[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Region A[/TD]
[TD="align: center"]GGG[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]HR[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]Region D[/TD]
[TD="align: center"]HHH[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]Admin[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Whenever changes was made in the Master Data
Click to expand...
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 ?
 
Upvote 0
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.
Code: 
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
 
Upvote 0
Hello Nikzulfaizan,

Another option:-


Code: 
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.
 
Upvote 0
Michael M said:
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 ?
Click to expand...

Hi .. thank you for your response...

My answer : Yes, to overwrite and it's applies to all.
 
Upvote 0
My Aswer Is This said:
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.
Code: 
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
Click to expand...

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...
 
Last edited:
Upvote 0
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.​


nikzulfaizan said:
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...
Click to expand...
 
Upvote 0
vcoolio said:
Hello Nikzulfaizan,

Another option:-


Code: 
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.
Click to expand...

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

My sample header range : "A1:G3"
 
Upvote 0
My Aswer Is This said:
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.​
Click to expand...

I can write a script that runs when you click a button. .--> Yes... Really appreciate it .... Btw, my sample header ranges : "A1:G3" .....
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
361
strugglingaccountant
S
willow1985
  • Solved
VBA Select all rows from active cell (selected cell) on table to 2nd last row
Replies
3
Views
256
rlv01
R
V
  • Locked
  • Question Question
Looking to pull specific rows from one sheet to another within the same workbook that will update when one is updated
Replies
1
Views
817
Fluff
Fluff
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
181
NdNoviceHlp
NdNoviceHlp
Nlhicks
  • Solved
Take Region, Find Right Sheet, Find Right Sub, Copy Update to Right Row
Replies
8
Views
241
RajeshKumar1313
R

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top