| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
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. |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Getting Data from a 2nd sheet to show in a dropdown box on sheet 1 | Kelly******** | Microsoft Excel Programming | 0 | 22nd Dec 2009 08:11 AM |
| Using dropdown in protected sheet | veggies27 | Microsoft Excel Worksheet Functions | 2 | 14th Apr 2009 03:46 AM |
| Dropdown Sheet | =?Utf-8?B?d2hhdHp6dXA=?= | Microsoft Excel Misc | 8 | 2nd Nov 2007 12:24 AM |
| adding data from one sheet to another sheet as a dropdown list bo. | =?Utf-8?B?Z2F0b3JndXk=?= | Microsoft Excel Misc | 1 | 18th Feb 2005 10:51 PM |
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | =?Utf-8?B?Um9uTWM1?= | Microsoft Excel Misc | 9 | 3rd Feb 2005 12:51 AM |
