Data Base, copy information one sheet to another automatically

[Bill Bula]

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

 

[www.exciter.gr: Custom Excel Applications!]

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!

 

[Bill Bula]

Thanks for help
--
Bill Bula
Sales Manager
Westad Industries

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!