Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Base, copy information one sheet to another automatically
I have a data base with prospective projects worldwide. I use a workbook with
worksheet for every country within my markets. The worksheets all have the same headers, date entry, type, contact, value, etc. Each worksheet has the same column and row size. At the end of each row there is a column with the header HOT LIST. I would like to be able to place a "X" in that last cell if the project becomes hot, and when I do this, the data in this row is copied to a HOT LIST worksheet within my Prospect List workbook database. I hope someone out there can help, thanks in advance -- Bill Bula Sales Manager Westad Industries |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Base, copy information one sheet to another automatically
OK, two steps for this to work:
1. Copy the following code in the code window for each sheet except the HOT LIST Sheet and any other sheet that does not contain the data you want copied: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 11 And Target.Value = "X" Then Call COPYrow(Target.Row) End If End Sub In the above code, replace the "11" with the number of the column you use to insert the X flag. 2. Insert a new module in your project and paste the following code: Public Sub COPYrow(R As Integer) Dim N As String Dim M As Long N = ActiveSheet.Name Rows(R).Select Selection.Copy Sheets("HOT LIST").Select M = ActiveSheet.Rows.Count Range("A" & M).Select Selection.End(xlUp).Select Cells(ActiveCell.Row + 1, 1).Select ActiveSheet.Paste Sheets(N).Select End Sub This code will copy the row you just inserted X in, change to HOT LIST sheet, find its first empty row and paste the original row there. Then it will return to the sheet you were before. Hope this helps. http://www.exciter.gr Custom Excel Applications and Functions! On Nov 5, 1:48 pm, Bill Bula wrote: I have a data base with prospective projects worldwide. I use a workbook with worksheet for every country within my markets. The worksheets all have the same headers, date entry, type, contact, value, etc. Each worksheet has the same column and row size. At the end of each row there is a column with the header HOT LIST. I would like to be able to place a "X" in that last cell if the project becomes hot, and when I do this, the data in this row is copied to a HOT LIST worksheet within my Prospect List workbook database. I hope someone out there can help, thanks in advance -- Bill Bula Sales Manager Westad Industries |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Base, copy information one sheet to another automatically
Thanks for help
-- Bill Bula Sales Manager Westad Industries "www.exciter.gr: Custom Excel Application" wrote: OK, two steps for this to work: 1. Copy the following code in the code window for each sheet except the HOT LIST Sheet and any other sheet that does not contain the data you want copied: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 11 And Target.Value = "X" Then Call COPYrow(Target.Row) End If End Sub In the above code, replace the "11" with the number of the column you use to insert the X flag. 2. Insert a new module in your project and paste the following code: Public Sub COPYrow(R As Integer) Dim N As String Dim M As Long N = ActiveSheet.Name Rows(R).Select Selection.Copy Sheets("HOT LIST").Select M = ActiveSheet.Rows.Count Range("A" & M).Select Selection.End(xlUp).Select Cells(ActiveCell.Row + 1, 1).Select ActiveSheet.Paste Sheets(N).Select End Sub This code will copy the row you just inserted X in, change to HOT LIST sheet, find its first empty row and paste the original row there. Then it will return to the sheet you were before. Hope this helps. http://www.exciter.gr Custom Excel Applications and Functions! On Nov 5, 1:48 pm, Bill Bula wrote: I have a data base with prospective projects worldwide. I use a workbook with worksheet for every country within my markets. The worksheets all have the same headers, date entry, type, contact, value, etc. Each worksheet has the same column and row size. At the end of each row there is a column with the header HOT LIST. I would like to be able to place a "X" in that last cell if the project becomes hot, and when I do this, the data in this row is copied to a HOT LIST worksheet within my Prospect List workbook database. I hope someone out there can help, thanks in advance -- Bill Bula Sales Manager Westad Industries |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste cahrt to new sheet-update data automatically? | Charts and Charting in Excel | |||
How do I automatically copy data from one sheet to another? | Excel Worksheet Functions | |||
Automatically copy selected information from one worksheet to anot | Excel Discussion (Misc queries) | |||
How do I automatically copy contact information? | Excel Discussion (Misc queries) | |||
how can i automatically copy the information in one cell on a she. | Excel Worksheet Functions |