Autopopulation

[Chaddyb]

I manage projects. I have a Workbook with a master sheet i named details. I want excel to populate a sheet called savings every time i change the project status on the details sheet to Completed. is there anyway to do this?

 

[Fluff]

Hi & welcome to the board.
That's perfectly possible.
A few questions
1) what column on sheet Details contains Completed.
2) How will that column be changed? Manually entered, data validation, Formula, or something else
3) Do you want to copy the entire row to the Savings sheet?
4) Once copied do you want that row removed from Details?

 

[Chaddyb]

Hi & welcome to the board.
That's perfectly possible.
A few questions
1) what column on sheet Details contains Completed.
2) How will that column be changed? Manually entered, data validation, Formula, or something else
3) Do you want to copy the entire row to the Savings sheet?
4) Once copied do you want that row removed from Details?

1. Column H
2. Manually entered
3. Ideally Not, i would only like to select a few of the columns to the move across
4. No i would like to keep all the rows on the master sheet

 

[Fluff]

Which cols do you want to copy, & where should they go?

 

[Chaddyb]

Columns B,C, G,W amd Z.
I am not sure what you mean by where they should go

 

[Fluff]

Do you want Col B to go to Col B, Col C to go to Col C etc, or do you want Col B to go to Col A , C to go to B, G to go to C etc?

 

[Chaddyb]

Oh Column B to Column A , Column C to column B, Column G to column C and so on.

 

[Fluff]

Ok try this

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim NxtRw As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.column = 8 Then Exit Sub
   If LCase(Target.Value) = "complete" Then
      With Sheets("Savings")
         NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).row
         .Range("A" & NxtRw).Resize(, 2).Value = Range("B" & Target.row).Resize(, 2).Value
         .Range("C" & NxtRw).Value = Range("G" & Target.row).Value
         .Range("D" & NxtRw).Value = Range("W" & Target.row).Value
         .Range("E" & NxtRw).Value = Range("Z" & Target.row).Value
      End With
   End If
End Sub

This needs to go in the sheet module.
Right click the Details sheet tab > select view Code > paste the code above into the window that appears.
Make sure that the workbook is macro enabled(ie xlsm, or xlsb)
Whenever you type "complete" into col H, the relevant data will get copied.

 

[Chaddyb]

Ok try this

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim NxtRw As Long
   
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.column = 8 Then Exit Sub
   If LCase(Target.Value) = "complete" Then
      With Sheets("Savings")
         NxtRw = .Range("A" & Rows.Count).End(xlUp).Offset(1).row
         .Range("A" & NxtRw).Resize(, 2).Value = Range("B" & Target.row).Resize(, 2).Value
         .Range("C" & NxtRw).Value = Range("G" & Target.row).Value
         .Range("D" & NxtRw).Value = Range("W" & Target.row).Value
         .Range("E" & NxtRw).Value = Range("Z" & Target.row).Value
      End With
   End If
End Sub

This needs to go in the sheet module.
Right click the Details sheet tab > select view Code > paste the code above into the window that appears.
Make sure that the workbook is macro enabled(ie xlsm, or xlsb)
Whenever you type "complete" into col H, the relevant data will get copied.

Hi it doesnt run when i hit the run button i get a box telling me to create a new macro

 

[Fluff]

It's designed to run automatically, whenever you type the word complete into col H