Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
I know I have seen this one posted before, but I can not find it.
I have a workbook with 5 sheets - OPEN, PENDING, TRANSFERS, HIRE, COMPLETE. This is also the status of our open positions which is column A. Depending on what Column A is on any of the sheets, I would like the row to go to the bottom of the proper sheet (and leave no blank row). I believe this is a workbook event change function, but cannot seem to figure it out. Help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
Hi Mike,
Place this code in ThisWorkbook Module. Private Sub Workbook_SheetActivate(ByVal Sh As Object) Application.Goto Sh.[A65536].End(xlUp) End Sub -- Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "Mike Reisinger" wrote in message m... I know I have seen this one posted before, but I can not find it. I have a workbook with 5 sheets - OPEN, PENDING, TRANSFERS, HIRE, COMPLETE. This is also the status of our open positions which is column A. Depending on what Column A is on any of the sheets, I would like the row to go to the bottom of the proper sheet (and leave no blank row). I believe this is a workbook event change function, but cannot seem to figure it out. Help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
If you want the event macro to work across all sheets, use the
Workbook_SheetChange() event macro. You don't say what values in column A, nor how they're inputted (direct entry? validation dropdown?). I'll assume that it's a direct entry that triggers the _SheetChange event. Adapt to suit: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Dim sShtName As String Dim sAddr As String With Target If .Count 1 Then Exit Sub If .Column = 1 Then Select Case UCase(Left(.Value, 1)) Case "O" sShtName = "Open" Case "P" sShtName = "Pending" Case "T" sShtName = "Transfers" Case "H" sShtName = "Hire" Case "C" sShtName = "Complete" Case Else MsgBox "Enter (O)pen, (P)ending, " & _ "T(ransfers), (H)ire, or (C)omplete" End Select If sShtName < "" Then Application.EnableEvents = False sAddr = .Address .EntireRow.Cut Sheets(sShtName).Range( _ "A" & Rows.Count).End(xlUp).Offset(1, 0) Sh.Range(sAddr).EntireRow.Delete Application.EnableEvents = True End If End If End With End Sub In article , "Mike Reisinger" wrote: I know I have seen this one posted before, but I can not find it. I have a workbook with 5 sheets - OPEN, PENDING, TRANSFERS, HIRE, COMPLETE. This is also the status of our open positions which is column A. Depending on what Column A is on any of the sheets, I would like the row to go to the bottom of the proper sheet (and leave no blank row). I believe this is a workbook event change function, but cannot seem to figure it out. Help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
JE - sorry to leave out some key info. The row data is typically inputted
in the OPEN sheet. Column A (status) is a validation list. As the status changes to HIRED, TRANSFER, etc, I would like the entire row to be cut and pasted into the appropriate sheet. I would like it to be able to move around between all sheets depending on the Column A (status). Also, where exactly to I paste this? Thanks! "J.E. McGimpsey" wrote in message ... If you want the event macro to work across all sheets, use the Workbook_SheetChange() event macro. You don't say what values in column A, nor how they're inputted (direct entry? validation dropdown?). I'll assume that it's a direct entry that triggers the _SheetChange event. Adapt to suit: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Dim sShtName As String Dim sAddr As String With Target If .Count 1 Then Exit Sub If .Column = 1 Then Select Case UCase(Left(.Value, 1)) Case "O" sShtName = "Open" Case "P" sShtName = "Pending" Case "T" sShtName = "Transfers" Case "H" sShtName = "Hire" Case "C" sShtName = "Complete" Case Else MsgBox "Enter (O)pen, (P)ending, " & _ "T(ransfers), (H)ire, or (C)omplete" End Select If sShtName < "" Then Application.EnableEvents = False sAddr = .Address .EntireRow.Cut Sheets(sShtName).Range( _ "A" & Rows.Count).End(xlUp).Offset(1, 0) Sh.Range(sAddr).EntireRow.Delete Application.EnableEvents = True End If End If End With End Sub In article , "Mike Reisinger" wrote: I know I have seen this one posted before, but I can not find it. I have a workbook with 5 sheets - OPEN, PENDING, TRANSFERS, HIRE, COMPLETE. This is also the status of our open positions which is column A. Depending on what Column A is on any of the sheets, I would like the row to go to the bottom of the proper sheet (and leave no blank row). I believe this is a workbook event change function, but cannot seem to figure it out. Help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
Hi Mike,
Thank you for posting in MSDN managed newsgroup! You may need to define one variable which will store the last position for your record in each sheet, for example iSheet2Lowest which stores the lowest value for the columnA in worksheet2. I write the sample codes for you in the worksheet change event function: 'Code begin------------------- 'first, check whether the change is raised by the cell you specified which contains the state: open, pending ... 'Then use the code below to copy the cell content to another sheet based on the scenario 'Note please substitue the sheet name according your scenario Sheets("Sheet1").Select Range("A9").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet2").Select Dim strPos strPos = "A" & iSheet2Lowest Range(strPos).Select Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'Please note, after the paste, update the value of lowest varaiable iSheet2Lowest = iSheet2Lowest + 1 'Code end--------------------- Please feel free to let me know if you have any further questions. Does this answer your question? Thank you for using Microsoft NewsGroup! Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
cut & paste between sheets based on cell data
One way:
If it's only to operate in the sheet named OPEN, put this in the sheet's code module (right-click on the sheet's tab and select View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sAddr As String With Target If .Count 1 Then Exit Sub sAddr = .Address Application.EnableEvents = False .EntireRow.Cut Sheets(.Value).Range( _ "A" & Rows.Count).End(xlUp).Offset(1, 0) Range(sAddr).EntireRow.Delete Application.EnableEvents = True End With End Sub In article , "Mike Reisinger" wrote: sorry to leave out some key info. The row data is typically inputted in the OPEN sheet. Column A (status) is a validation list. As the status changes to HIRED, TRANSFER, etc, I would like the entire row to be cut and pasted into the appropriate sheet. I would like it to be able to move around between all sheets depending on the Column A (status). Also, where exactly to I paste this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste data based on multiple criteria | Excel Worksheet Functions | |||
Compare two wk sheets with common data using copy paste macro | Excel Worksheet Functions | |||
Export data to sheets based on value | Excel Discussion (Misc queries) | |||
populating sheets based on data from parent sheets | Excel Discussion (Misc queries) | |||
Cut & Paste Data into different worksheet based on specific criteria | Excel Programming |