Home/ Questions/Q 6119
Next
In Process
Computing Staff
  • 3
Asked: 2021-11-18T06:56:47+00:00 In:

Excel – Auto Copy A Row To Another Sheet

  • 3

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.

Share

    1 Answer

    1. 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

      • 0