| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy row to sheet as per dropdown
Hi, I have dropdown in ColH with all the names similar to sheet names. When I
select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE
using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Thanks Jacob.. Works just the way I wanted..
Can I also have the sheet name from which the row is pasted? in colI (sheet where the row is pasted) "Jacob Skaria" wrote: Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Kashya, you can also avoid the loop..
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveSheet.Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveWorkbook.Sheets(strSName).Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp ActiveWorkbook.Sheets(strSName).Range("I" & lngLastRow) = ActiveSheet.Name End If End Sub If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Thanks Jacob.. Works just the way I wanted.. Can I also have the sheet name from which the row is pasted? in colI (sheet where the row is pasted) "Jacob Skaria" wrote: Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Oops...a mistake
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveWorkbook.Sheets(strSName).Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Kashya, you can also avoid the loop.. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveSheet.Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Hey Jocob, this code worked..
can you help me in editing the line lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row I now need this to be updated in row xldown +2 Thanks.. "Jacob Skaria" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveWorkbook.Sheets(strSName).Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp ActiveWorkbook.Sheets(strSName).Range("I" & lngLastRow) = ActiveSheet.Name End If End Sub If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Thanks Jacob.. Works just the way I wanted.. Can I also have the sheet name from which the row is pasted? in colI (sheet where the row is pasted) "Jacob Skaria" wrote: Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
If you mean 2 rows from the last row....
lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row + 2 If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hey Jocob, this code worked.. can you help me in editing the line lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row I now need this to be updated in row xldown +2 Thanks.. "Jacob Skaria" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveWorkbook.Sheets(strSName).Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp ActiveWorkbook.Sheets(strSName).Range("I" & lngLastRow) = ActiveSheet.Name End If End Sub If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Thanks Jacob.. Works just the way I wanted.. Can I also have the sheet name from which the row is pasted? in colI (sheet where the row is pasted) "Jacob Skaria" wrote: Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy row to sheet as per dropdown
Thank you so much.. :)
"Jacob Skaria" wrote: If you mean 2 rows from the last row.... lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row + 2 If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hey Jocob, this code worked.. can you help me in editing the line lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row I now need this to be updated in row xldown +2 Thanks.. "Jacob Skaria" wrote: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row arrTemp = ActiveSheet.Range("A" & Target.Row & ":G" & Target.Row) ActiveWorkbook.Sheets(strSName).Range("A" & lngLastRow & ":G" & lngLastRow) = arrTemp ActiveWorkbook.Sheets(strSName).Range("I" & lngLastRow) = ActiveSheet.Name End If End Sub If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Thanks Jacob.. Works just the way I wanted.. Can I also have the sheet name from which the row is pasted? in colI (sheet where the row is pasted) "Jacob Skaria" wrote: Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. On the left treeview double click 'This Workbook '. Drop down to get the SheetChange event. Save. Get back to Workbook. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 8 Then strSName = Cells(Target.Row, 8) lngLastRow = ActiveWorkbook.Sheets(strSName).Cells(Rows.Count, "A").End(xlUp).Row For lngCol = 1 To 7 ActiveWorkbook.Sheets(strSName).Cells(lngLastRow + 1, lngCol) = ActiveSheet.Cells(Target.Row, lngCol).Value Next End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi, I have dropdown in ColH with all the names similar to sheet names. When I select a name from dropdown macro should copy that row from A:G and paste the same at A2 & xdown as per the sheet selected. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy dropdown list | Excel Discussion (Misc queries) | |||
| Dropdown Sheet | Excel Discussion (Misc queries) | |||
| How to copy Dropdown boxes? | Excel Programming | |||
| adding data from one sheet to another sheet as a dropdown list bo. | Excel Discussion (Misc queries) | |||
| How do I copy my dropdown box with data to another worksheet? | Excel Worksheet Functions | |||
Linear Mode
