Solved Auto Insert New Row on Different Worksheet and copy info

November 16, 2017 at 06:53:00
Specs: Windows 10
I have a spread sheet with 1 summary page and 3 other sheet in which the information for the summary page is pulled from. There is a tab on my 3 "other" sheets which signals yes or no. I will be constantly adding more information to these sheets and want to set up something in excel that when i flip the switch to yes for any of these lines, i want it to add a new row in the summary sheet and copy all the information over.

See More: Auto Insert New Row on Different Worksheet and copy info

Reply ↓  Report •

#1
November 16, 2017 at 07:54:35
✔ Best Answer
You haven't supplied a lot of specifics, so it's hard for us to offer specific solutions. That said...

re: "There is a tab on my 3 "other" sheets which signals yes or no."

I'm not sure what you mean by a "tab..that signals". "Tab" usually refers to the Sheet names at the bottom of your Excel app.

In any case, here is a shot at some generic code. If Yes is entered (or chosen from a drop-down) in any cell in Column D, that Row will be copied to the next empty Row in a sheet named "Summary".

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if Yes was selected in Column D
  If Target.Column = 4 Then
    If Target = "Yes" Then
'Determine next emoty row in Summary sheet and copy target row
      nxtRw = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 1
      Target.EntireRow.Copy Sheets("Summary").Cells(nxtRw, 1)
    End If
  End If
End Sub

Note: This code must be stored in the Sheet module for each Sheet in which the "Yes" entry will be made. Right-click the sheet tab, chose View Code and paste the code into the window that opens.

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


Reply ↓  Report •
Related Solutions


Ask Question