Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Hi, I posted a question here but recieved no responses so I shall re-phrase
the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
there are two way of doing theis. First to use a worksheet change function
when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Hi Joel,
Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Private Sub CommandButton1_Click()
If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
That works a treat, thanks.
Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Use pastespecial to pate values only
Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues ..activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Nice one.
Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B'
Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Private Sub CommandButton1_Click()
If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B").value = .Range("B" & Newrow) Sheets("Sheet2").Range("E").value = .Range("E" & Newrow) Sheets("Sheet2").Range("F").value = .Range("F" & Newrow) Sheets("Sheet2").Range("G").value = .Range("G" & Newrow) Sheets("Sheet2").Range("H").value = .Range("H & Newrow) Sheets("Sheet2").Range("K").value = .Range("K" & Newrow) Sheets("Sheet2").Range("L").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Sorry, there were errors in the code. Try this
Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B" & Newrow).value = _ .Range("B" & RowNo) Sheets("Sheet2").Range("E" & Newrow).value = _ .Range("E" & RowNo) Sheets("Sheet2").Range("F" & Newrow).value = _ .Range("F" & RowNo) Sheets("Sheet2").Range("G" & Newrow).value = _ .Range("G" & RowNo) Sheets("Sheet2").Range("H" & Newrow).value = _ .Range("H" & RowNo) Sheets("Sheet2").Range("K" & Newrow).value = _ .Range("K" & RowNo) Sheets("Sheet2").Range("L" & Newrow).value = _ .Range("L" & RowNo) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Hi Joel,
Still wrestling with this. I get a Compile error message: Invalid or unqualified reference with the ".Range" highlighted in this first row: Sheets("Sheet2").Range("B").Value = .Range("B" & Newrow) Cheers -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B").value = .Range("B" & Newrow) Sheets("Sheet2").Range("E").value = .Range("E" & Newrow) Sheets("Sheet2").Range("F").value = .Range("F" & Newrow) Sheets("Sheet2").Range("G").value = .Range("G" & Newrow) Sheets("Sheet2").Range("H").value = .Range("H & Newrow) Sheets("Sheet2").Range("K").value = .Range("K" & Newrow) Sheets("Sheet2").Range("L").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Look at thje newer code I sent. I saw that problem earlier this morning. "Jock" wrote: Hi Joel, Still wrestling with this. I get a Compile error message: Invalid or unqualified reference with the ".Range" highlighted in this first row: Sheets("Sheet2").Range("B").Value = .Range("B" & Newrow) Cheers -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B").value = .Range("B" & Newrow) Sheets("Sheet2").Range("E").value = .Range("E" & Newrow) Sheets("Sheet2").Range("F").value = .Range("F" & Newrow) Sheets("Sheet2").Range("G").value = .Range("G" & Newrow) Sheets("Sheet2").Range("H").value = .Range("H & Newrow) Sheets("Sheet2").Range("K").value = .Range("K" & Newrow) Sheets("Sheet2").Range("L").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Thanks Joel, got there in the end!
-- Traa Dy Liooar Jock "Joel" wrote: Sorry, there were errors in the code. Try this Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B" & Newrow).value = _ .Range("B" & RowNo) Sheets("Sheet2").Range("E" & Newrow).value = _ .Range("E" & RowNo) Sheets("Sheet2").Range("F" & Newrow).value = _ .Range("F" & RowNo) Sheets("Sheet2").Range("G" & Newrow).value = _ .Range("G" & RowNo) Sheets("Sheet2").Range("H" & Newrow).value = _ .Range("H" & RowNo) Sheets("Sheet2").Range("K" & Newrow).value = _ .Range("K" & RowNo) Sheets("Sheet2").Range("L" & Newrow).value = _ .Range("L" & RowNo) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy specific data to another worksheet
Hi Joel,
I'm looking for solution to a similar problem that Jock is having and hoping that you could help me tweak the code a little bit to meet my need. The main idea is the same as Jock's but here are something else that I need: 1. Instead of "ActiveCell.Row", I'd like to have a code that will go through entire column (i.e. Col K) of Sheet1 to find the "CH" (as in Jock's case) and copy and paste it over to Sheet2. 2. I'd also like to clear the content of Sheet1 that has already been copied to Sheet2. I was thinking of Cut&Paste but the problem with that is it will Remove the line from Sheet1 and I don't want that since my set up is to certain number of lines in Sheet1. If we use "ClearContent" code then the line will look empty without sorting. This is some thought I have based on my knowledge of how to get this done, but if you have any brilliant solution to this, that would be great! I hope you'll receive my message and be willing to help me with this since you're already solve half of the problem. Thank you so much!! Neon520 "Joel" wrote: Sorry, there were errors in the code. Try this Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("B" & Newrow).value = _ .Range("B" & RowNo) Sheets("Sheet2").Range("E" & Newrow).value = _ .Range("E" & RowNo) Sheets("Sheet2").Range("F" & Newrow).value = _ .Range("F" & RowNo) Sheets("Sheet2").Range("G" & Newrow).value = _ .Range("G" & RowNo) Sheets("Sheet2").Range("H" & Newrow).value = _ .Range("H" & RowNo) Sheets("Sheet2").Range("K" & Newrow).value = _ .Range("K" & RowNo) Sheets("Sheet2").Range("L" & Newrow).value = _ .Range("L" & RowNo) .activate End With End If End Sub "Jock" wrote: It's cell B also cells E to H and K to L inclusive (i.e. omitting A, C, D, I and J) Thanks for your help on this - much appreciated! -- Traa Dy Liooar Jock "Joel" wrote: Is it "B" to "B" and "E" to "E" or "B" to "A" and "E" to "B' Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 end with Sheets("Sheet2").Range("A").value = .Range("B" & Newrow) Sheets("Sheet2").Range("B").value = .Range("E" & Newrow) Sheets("Sheet2").Range("C").value = .Range("F" & Newrow) Sheets("Sheet2").Range("D").value = .Range("G" & Newrow) Sheets("Sheet2").Range("E").value = .Range("H & Newrow) Sheets("Sheet2").Range("F").value = .Range("K" & Newrow) Sheets("Sheet2").Range("G").value = .Range("L" & Newrow) .activate End With End If End Sub "Jock" wrote: Nice one. Can one 'specify' which cells are copied over? I.E. B, E-H, K-L only That would help my cause! Thanks -- Traa Dy Liooar Jock "Joel" wrote: Use pastespecial to pate values only Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy .Range("A" & NewRow).pastespecial paste:=xlPasteValues .activate End With End If End Sub "Jock" wrote: That works a treat, thanks. Is it possible to just paste values ie, no formatting? Also can the user be taken to Sheet 2 when the button is clicked? That would help a lot too! There's already a button there to return the user to the active cell on Sheet 1. Thanks -- Traa Dy Liooar Jock "Joel" wrote: Private Sub CommandButton1_Click() If ActiveCell.Value = "CH" Then RowNo = ActiveCell.Row With ActiveSheet Set CopyRange = .Range("A" & RowNo & ":K" & RowNo) End With With Sheets("Sheet2") Lastrow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = Lastrow + 1 CopyRange.Copy Destination:=.Range("A" & NewRow) End With End If End Sub "Jock" wrote: Hi Joel, Good thinking about the button to reduce wrong entries. The drop down list is a Data Validation and is located in each cell in column 'M' on Sheet 1. Cheers, -- Traa Dy Liooar Jock "Joel" wrote: there are two way of doing theis. First to use a worksheet change function when the drop down box is changed to move the data. but this is not really a great way of doing this because if the wrong value is selected unwanted data will be copied. the second method is to use a Button to perform the operation after the value is selected which willreduce the number of wrong items theat will be moved. 1) Is the Drop down List a Data validation or an Autofilter? 2) What cell is the Drop down list Located? "Jock" wrote: Hi, I posted a question here but recieved no responses so I shall re-phrase the question in the hope that someone will take the bait. Sheet 1. When a specific value (CH) is selected from a drop down list (in column M), I would like to have all the data from cells on the same row (A-K) copied to the next empty row in Sheet 2. So, the theory is that data is entered by the user in cells A-M. When the option 'CH' is selected from the list of options in cell M, A-K is copied to Sheet 2 and the user is taken to Sheet 2 to add text if necessary. Can someone advise if this is achievable or do I have to try a different approach? Thanks. -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy specific data to second worksheet | New Users to Excel | |||
Copy specific records from a worksheet to another | Excel Worksheet Functions | |||
Copy specific data over to other worksheet | Excel Programming | |||
Copy Data from Workbook into specific Worksheet in other Workbook? | Excel Discussion (Misc queries) | |||
Copy and pasting specific sheet data to a master worksheet | Excel Programming |