Solved Excel - Auto copy a row to another sheet

September 23, 2011 at 02:31:31
Specs: Windows XP
I have a main database on one worksheet with the different types of sectors i.e. health, finance and whether they are active or not.

I wish for the information to be copied to another sheet dependant on the sector i.e all health to health tab and all finance to finance tab regardless of whether they are active or not and then for all active to go the active tab and all non active to go to non active tab, regardless of sector.

Any clues.


See More: Excel - Auto copy a row to another sheet

Report •


✔ Best Answer
October 3, 2011 at 09:45:44
You didn't label the columns in your example, so I am assuming that they are Columns A & B.

Right click the sheet tab for your Main sheet, choose View Code and paste this code into the window that opens.

Then go back to your Main sheet and change some Drop Downs. That row of data should be copied as you requested.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Change was made to Column A or Column B
  If Target.Column = 1 Or Target.Column = 2 Then
'If True, then determine the next available Row in Column A of Target sheet
    nxtRw = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row from Main sheet to Target sheet
    Range(Target.Address).EntireRow.Copy _
       Destination:=Sheets(Target.Value).Range("A" & nxtRw)
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 23, 2011 at 02:46:09
You will need a macro to accomplish this and you will need to provide a lot more detail about how the workbook is laid out before any assistance can be offered.

We would need to know where the "sector" names can be found and where the active/non-active information can be found.

Other information such as whether the sheets for the sectors already exist, etc. would be required.

Keep in mind that we can't see your workbook from where we're sitting, so you need to be specific with your description.

Please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 23, 2011 at 03:10:19
Hi DerbyDad03,

Thanks for your quick response.

Ok, info as follows....

All relevant sheets exists i.e Main Database (source), Active, Non Active, Finance, Health.

The database is set as follows:

	Status	Sector
1	Active	Public
2	Active	Other
3	Active	Other
4	Active	Service
5	Active	Service
6	Active	IT

So anything which is active, regardless of sector goes to the Active sheet in the next available row.

Anything which is in the relevant sector goes to the sector sheet in the next available row, regardless of status.

The Status and Sector columns have drop down menus with preset details, so other details cannot be typed in.

Does this help?


Report •

#3
September 23, 2011 at 04:47:06
Is this a one time "clean-up" or something that you want to happen each time a drop down is changed or both?

If you want to clean it up once and then have it be automatic it will take 2 sets of code.

If you want it to be automatic, which drop down change should trigger it, a change in sector, a change in status or both?

BTW I'm about to get into my car for a 7 hour drive to visit my daughter at college...I can't say that I'll be working on this over the weekend..

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
September 23, 2011 at 04:56:23
Hi DerbyDad03,

I would want this to happen each time a drop down is changed should it be a change in either sector or status as if the status changes then it goes into the relevant status sheet and should for some reason one had the company under the wrong sector,once changed the details will show up in the correct sector sheet.

You help is much appreciated.

I hope you had a great time visiting your daughter.


Report •

#5
October 3, 2011 at 09:45:44
✔ Best Answer
You didn't label the columns in your example, so I am assuming that they are Columns A & B.

Right click the sheet tab for your Main sheet, choose View Code and paste this code into the window that opens.

Then go back to your Main sheet and change some Drop Downs. That row of data should be copied as you requested.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Change was made to Column A or Column B
  If Target.Column = 1 Or Target.Column = 2 Then
'If True, then determine the next available Row in Column A of Target sheet
    nxtRw = Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Row from Main sheet to Target sheet
    Range(Target.Address).EntireRow.Copy _
       Destination:=Sheets(Target.Value).Range("A" & nxtRw)
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 4, 2011 at 03:43:51
Thanks DerbyDad03, this works fab!

I have another query though, I now need to make sure there is no duplicate data in the work sheets, except the ones which the rows were copied into.... i.e. if I change a status from Deactive to Active, then I need to make sure that the row in the Deactive tab has now gone and only appears on Active and the Main database.

I the below macro from Humar, on another post. Is it simply changing the relevant names to the names on my worksheets? (I have 10 sectors and 4 statuses)

'now delete it from any other destination worksheet
'loop through all worksheets
'test if it is one of the four named destination sheets
'but not the one we just copied it to
For Each wsDest In ActiveWorkbook.Worksheets()
If (wsDest.Name = "in_progress" _
Or wsDest.Name = "Active" _
Or wsDest.Name = "Deactive" _
Or wsDest.Name = "Prospective") _
And wsDest.Name <> rngDest.Worksheet.Name Then
'find matching ID - work from end of used range
'just in case there is a duplicate entry
For n = wsDest.UsedRange.Rows.Count To 2 Step -1
'find matching Task
If wsDest.Range("A" & CStr(n)).Text = _
rngSrc.Offset(0, -1).Text Then
'match found, so delete row
wsDest.Range("A" & CStr(n)).EntireRow.Delete
End If
Next n
End If
Next wsDest

'now test the worksheet we just copied to - for duplicates
'start at row above the one we copied to
With rngDest.Worksheet
For n = rngDest.Row - 1 To 2 Step -1
If .Range("A" & CStr(n)).Text = _
rngSrc.Offset(0, -1).Text Then
'delete row
.Range("A" & CStr(n)).EntireRow.Delete
End If
Next n
End With
End If

Again, thank you for all your help so far!


Report •


Ask Question