# Solved: Excel conditional copy



## rarodrig (Aug 2, 2002)

I want to enter a row of data (17 cells) on one (detail) sheet and then if a conditional being true, auto copy that row of data to another (summary) sheet.


Currently I enter new data manually on my detail sheets and then reenter or manually copy the data to my summary sheet. Once the data expires, I want to keep it on the detail sheet, but no longer on the summary sheet. So I have to manually delete it from the summary sheet.

Bonus question: Since I have multiple detail sheets, is there a way to vary the size of the target area on the summary sheet where the rows would be copied? The target area is in the middle of a summary page and it would be cool to not size the target for worst case "true" results.

Thanks!


----------



## terabytecomputer (Apr 20, 2009)

A little more detail is needed.

Can you provide an example of the data in the 17 cells (no private data, please)
What is the true condition that would cause the row of data to be copied to the summary sheet?
How does your data become 'expired' (certain # of days, value, other)?
Can you provide an example of what you mean by 'varied sizes' for the target area (I assume you mean a varied number of cells)?


----------



## rarodrig (Aug 2, 2002)

Here is an example worksheet. The conditional for copying is a test for either a blank or "open" in one of the source cells. It could be either way since the format of the source sheets is my choice.

thanks for your help.


----------



## rarodrig (Aug 2, 2002)

The summary sheet is only for keeping track of open projects, so I could have 1 or 2 big projects open at once; or I could have multiple small projects open instead. So the summary sheet could need only one row to display my only one big project or it could have multiple rows to display several small ones. 

It would be cleaner to only have the number of rows needed to show the current number of open projects. But I have no idea how to get a variable number of rows to be inserted in the middle of a worksheet, or even if it's possible.

Used vlookup in attached file to get the data to summary page, but it does not address the issue of needing to have multiple (empty) rows on the summary sheet to handle the worst case number of open projects.

I may just have to re format the summary sheet and put the project data at the bottom and allow for worst case.


----------



## terabytecomputer (Apr 20, 2009)

This might be what you're after, or at least a start to what you're after. In my code, replace the values for 'myCopyRow' and 'myPasteRow' to whatever your live spreadsheet requirements are ('myCopyRow' being the first row on sheet 'FCX' where you want to look for open projects, 'myPasteRow' being the first row where you want to begin showing the open projects on your 'Summary' sheet).

I created a Command Button just to work with the code in testing. You can either use a button or have it launch on an event such as when the file opens or when the sheet updates. If you need help with that, let me know, or I'm sure someone else will chime in with their method.

Here's the Command Button code:

Private Sub CommandButton1_Click()
Dim myCopyRow, myPasteRow As Integer

myCopyRow = 3
myPasteRow = 9

With Worksheets("Summary")
'Clear out current summary
Do While .Cells(myPasteRow, 1) <> ""
.Rows(myPasteRow).Clear
myPasteRow = myPasteRow + 1
Loop
End With

myPasteRow = 9

'Display new summary
With Worksheets("FCX")
Do While .Cells(myCopyRow, 1) <> ""
.Rows(myCopyRow).Copy
Worksheets("Summary").Paste Destination:=Worksheets("Summary").Rows(myPasteRow)
myCopyRow = myCopyRow + 1
myPasteRow = myPasteRow + 1
Loop
End With
End Sub


----------



## rarodrig (Aug 2, 2002)

Your code worked great. I executed from the developer tab, but do not know how to get it to run from a button.

I'm looking at the MS site for button control , but haven't found it get.

You have saved be lots of time, and hopefully reduced by (many?) operator errors!!!

Thanks!!!


----------

