show columns on other worksheet without using formulas

N

nicholas

Hi,

I have 1 worksheet containing lot's records.
Just like in access, each line is 1 record, and each column contains data.

ex:

WORKSHEET 1:

name title address city
1 dfjw kjhk kjhkj kjhk
2 lmkj lkjlk lkjkuf guyg
3 drdtg xcx yjutuy hgyy


Now I want to have a selection of columns on worksheet 2:
ex, only name and title:

WORKSHEET 2:

name title
1 dfjw kjhk
2 lmkj lkjlk
3 drdtg xcx


I will only add/modify rows in worksheet 1.
And I want that this is filled automaticaly in worksheet 2.
I do not want formulas in worksheet 2: like for example: cell A1 =
worksheet1!A1
And I do not want to run a macro each time I add or modify a row in
worksheet 1

How could I do this??
All ideas are welcome !

THX everyone,
Nic.
 
N

Norman Jones

Hi Nic,

Try:
'==================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("A1:B100")

Application.EnableEvents = False
If Not Intersect(Target, rng) Is Nothing Then
Sheets("Sheet2").Range(Target.Address).Value _
= Target.Value
End If
Application.EnableEvents = True

End Sub
'<<==================

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

*******************************************
Right-click the worksheet's tab

Select 'View Code' from the menu and paste the code.

Alt-F11 to return to Excel.
*******************************************

With this code in place, any changes to the range A1:B100 on Sheet1 will
automaticall be recorded in the corresponding cells on Sheet2.

Change the range and sheet names to suit your requirements.
 
G

Guest

Hi

There is a camera within Excel. If you go to View/Toolbars/Tools down near
the bottom of the list is a camera option. Drag this to your toolbar.
Select the cells you wish to see and click the camera. You can now go to
your main sheet and drag your cursor to create a window within Excel that
will show what you have just captured - and it updates too!
There must be a keyboard shortcut for the camera, but I don't know what it
is.
It might be of interest.

Andy.
 
N

nicholas

excellent !!!

THX
Nic

Hi

There is a camera within Excel. If you go to View/Toolbars/Tools down near
the bottom of the list is a camera option. Drag this to your toolbar.
Select the cells you wish to see and click the camera. You can now go to
your main sheet and drag your cursor to create a window within Excel that
will show what you have just captured - and it updates too!
There must be a keyboard shortcut for the camera, but I don't know what it
is.
It might be of interest.

Andy.
Click to expand...
 
Top