Copy Row To Another Sheet Depending On Existence Of Specific Value In Specific Column

[peter renton]

Hi i have attached a sample wb

On it i have two sheets (but they will end up with 1 to 31) named 1 ton 31

i can get any row tagged with the word copy in sheet1 to be coppied onto sheet 2, how can i then get any row tagged in sheet 2 to be coppied to 3 then 3 to 4 etc?
it would be good if the copied row did not carry over the word copy onto the new sheet.

Would i need to put a code in each sheet????


Any help would be appriciated

[Excel Fox]

Peter, you have been guided regarding thread posting. Next time, please adhere to Thread Posting guidelines.

Here's a modified version of your existing code. This can be used for all the sheets, except the last sheet in the workbook.



Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
On Error GoTo Err_Execute
'Start search in row 6
LSearchRow = 6
'Start copying data to row 13 in Sheet2 (row counter variable)
LCopyToRow = 13
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If value in column E = "Copy", copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = "Copy" Then
With Sheets(ActiveSheet.Next.Name)
.Range(LCopyToRow & ":" & LCopyToRow).Value = _
Range(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Value
.Cells(LCopyToRow, "E").ClearContents
End With
'Move counter to next row
LCopyToRow = LCopyToRow + 1
End If
LSearchRow = LSearchRow + 1
Wend

'Position on cell A3
Range("A3").Select
MsgBox "All Selected Rows Have Been Copied To The Next Sheet ."
Exit Sub
Err_Execute:
MsgBox "An error occurred."

End Sub

[peter renton]

Hi
Thank you for the code,
I have replaced my old code with yours in mod 1 and it works from sheet1 to sheet 2, but when i add another sheet it does not copy from 2 to 3 do i need to do something else for it to recognise future added sheets???

Peter

[Excel Fox]

No you don't. You just need to assign the same macro to each button on further sheets

[peter renton]

Hi

Again forgive my stupidity, but i just copy a clone page and this copies everything, the time start macro works, but the copy wont, when i look on the sheet code
the macro code is there???


peter

[Excel Fox]

The macro should not be in the sheet, but in the module. And no matter how many times you copy over a sheet, the macro will always remain that single one in the module. It's just that the button in each of the sheets will be assigned that macro.

[peter renton]

Hi
I still cant get this to work (dont know what i am doing wrong)
if a copy a sheet after sheet one the copy button will work regardless of what the second sheet is named, but if i add another sheet when i press the button the pop up says all items coppied but nothing happens.
When i copy the sheet the call searchforstring is in the coding in each sheet? there are no errors ?? have you any ideas

Did you get it to work on my test file ???

Peter

[Excel Fox]

It worked on your test file. Like I said, the code should be in the code module. It should NOT be in the sheet code module. When you copy a sheet, the code DOESN'T get copied over and over again.

[peter renton]

Hi

I have been copying sheets by holding down the mousebutton and ctrl which copies every thing, is this not the way to do it ?

Peter