Can you copy a row from one worksheet to another dependant on drop menu selection

[Steve_f]

Hi All, I have a spreadsheet and would like to know if it is possible if somebody selects "yes" from adrop menu options of "yes/no" that the row is atomatically copied from one sheet to another that is password protected?
I want the first sheet to accumulate the workflow coming into my team but only copy the items that are ready to the second spreadsheet that will be password protected for thei use only.
Any help will be greatly appeciated please?
Steve

 

[Steve_f]

Hello All, I have tried to re-write the question to make it easier to understand and correct a few spelling mistakes. I am worse at spelling than I am at using Excel unfortunately
What I would like to do is to create a work request spreadsheet, the first worksheet would collect all the information on work coming into my work centre this would have a column with drop menu options of "yes/no".
If somebody selects "Yes" then that complete row of information would be automatically copied from the first worksheet to a second on the next tab this will be password protected?
Any help will be greatly appreciated please? I have searched the few books that I have and the internet but cannot see if this is possible or not?
Steve

 

[vcoolio]

Hello Steve,

As we don't have any idea of how your worksheets are set out, let's assume that:

- The data in your source worksheet starts in Row2 with headings in Row1.
- The data extends from Columns A:I.
- The criteria column (with Yes/No dropdowns) is Column I.
(You'll need to change any of the above to suit yourself).

Next, paste this code into the source sheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(9)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Sheet2.Unprotect "Your password here"  '---->Sheet2 is the sheet code not the sheet name.

Application.ScreenUpdating = False

If Target.Value = "Yes" Then
        Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        'Target.EntireRow.Delete
End If

Sheet2.Protect "Your password here"

Application.ScreenUpdating = True

End Sub

If you wish to delete the row of "used" data from the source sheet once the data transfer is complete, remove the apostrophe from in front of the delete line of code.

To implement this code:-

- Right click on the source sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[Steve_f]

Hello vcoolio, thank you very much for responding. Apart from providing the sheet details I should also state that I am a beginner when it comeso to VBA code.
The source sheet (Sheet 1) has columns A to L and the data to be copied starts at row 4. The sheet to be pasted into is (Sheet 2) starting at Row 4 also.

As per your instructions I have Right clicked on the sheet 1 tab and pasted your code into the window with what I think are the correct alterations.
Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Columns(12)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub


Sheet2.Unprotect "trial1"


Application.ScreenUpdating = False


If Target.Value = "Yes" Then
Target.EntireRow.Copy Sheet2.Range("A4") .End(3)(2)
Target.EntireRow.Delete
End If


Sheet2.Protect "trial1"


Application.ScreenUpdating = True


End Sub
I then clicked Save and saved the spreadsheet as a "Macro enabled". But when I click "Run" a "Maros" widow appears. Do I need to create the macro in this window again?
It would be so helpful if you could add screen grabs here to explain?
Thanks in advance
Steve

 

[Steve_f]

Hello vcoolio, please ignore my previous reply. It is working as required but (there is always a but) it is over writing row 4 everytime?

How can I change the code so that is copies and pasted each entry onto the next empty row?
Thanks
Steve

 

[vcoolio]

Hello Steve,

I'm assuming that you have headings in Row3 of sheet2 so change this line of code:-

[COLOR=#0000cd]Target.EntireRow.Copy Sheet2.Range("A4") .End(3)(2)[/COLOR]

back to

Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)

The code is an event code so no buttons are required and there's no need to click on "Run" in the VB Editor. The code will execute each time you select "Yes" from the drop downs.

I hope that this helps.

Cheerio,
vcoolio.

 

[Steve_f]

Hi vcoolio, no it still pastes only to row 4 even if i select yes on various rows on sheet 1?

Also I need to share the spreadsheet once it is working and with the shared switched on I get a "unprotect error runtime error 1004"?
Steve

 

[vcoolio]

Did you change the line of code back to the way it was in post #3 ?
Do you have headings in row3 of sheet2?

Cheerio,
vcoolio.

 

[Steve_f]

Hi, yes I copied the whole line and replaced the line in the code with no alterations.
Yes I have headings in the first 3 rows.
Steve

 

[vcoolio]

Hi Steve,

So it works now? If not, upload a sample of your workbook to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Make sure that the sample is an exact replica of your actual workbook and if your data is sensitive then please use dummy data.

Cheerio,
vcoolio.