collecting fata from all sheets into one sheet

[lana.b]

Dear all,

i have one worksheet divided into 20 tab( sheets) all sheets with the same
templates but for different users, but i want sheet 21 have all data from the
other sheets.
note: each sheet has a password for its user , so i need sheet 21 to be the
master sheet and updated at the same, if one user makes some changes on any
record,this should automatically reflected in sheet 21 .
plz plz help ASAP

 

[jon]

Hi Lana,
Thought I'd try to help you whilst I'm waiting for someone to help me out.

In your master sheet, click in field A1 (or where ever you want to show the
data) then press =, then click user1's sheet and click the A1 field and
press return.
When you return to your master sheet, field A1 will have the value of field
A1 from user1's sheet.
Alternatively you can just enter the reference manually so if user1's sheet
is called sheet1 then enter =Sheet1!A1 in your master sheet to see the value
in A1 from user1.
Hope that makes sense.

I'm not sure how password protection works, so this may hinder my answer.

Jon

 

[lana.b]

Thank so much Jon
let me try it and i 'll refer back to u.


--
with regards
L.B

Hi Lana,
Thought I'd try to help you whilst I'm waiting for someone to help me out.

In your master sheet, click in field A1 (or where ever you want to show the
data) then press =, then click user1's sheet and click the A1 field and
press return.
When you return to your master sheet, field A1 will have the value of field
A1 from user1's sheet.
Alternatively you can just enter the reference manually so if user1's sheet
is called sheet1 then enter =Sheet1!A1 in your master sheet to see the value
in A1 from user1.
Hope that makes sense.

I'm not sure how password protection works, so this may hinder my answer.

Jon

 

[Neil W]

I have done a similar thing for various inputs and secured them.

I would attach the file if I knew how.

Thank so much Jon
let me try it and i 'll refer back to u.

 

[Horacio]

I have something similar for my coins collection.
Many worksheets and then I join all them in one named "Master List"
Try this code:

Sub Summarize()
Dim ws As Worksheet
Dim lastRng As Range
Application.ScreenUpdating = False ' speed up code

ThisWorkbook.Sheets("Master
List").Range("B2:M65536").ClearContents 'clear

For Each ws In ThisWorkbook.Worksheets
Set lastRng = ThisWorkbook.Sheets("Master
List").Range("b65536").End(xlUp).Offset(1, 0)

Select Case ws.Name
Case "Master List" 'exlude
'do nothing
Case "Folha1"
Case Else
ws.Activate

'copy data from individual sheets
Range("A250", Range("X65536").End(xlUp)).Copy lastRng

End Select
Next
Application.CutCopyMode = False 'clear clipboard
Application.ScreenUpdating = True
Sheets("Master List").Activate
Cells.Select
End Sub


lana.b escreveu: