Collecting data from different sheet
 

Hi Everyone
I have 5 sheets example sht1,sht2,sht3,sht4,sht5,
4 sheets is reserved for entry (Sht1-Sht4)
and sht5 Accumulate the data from all these sheets
Now I have 6 entries in sheet1 start from Columns B1 to B6
Same for sht 2 8 entries from Columns B1 to B8
Same for sht 3 9 entries from Columns B1 to B9
Same for sht 2 3 entries from Columns B1 to B3
Sheet 5 will collect this info in this order start from sheet 1 then 2,3,4
sheet5 will be like this: Columns b1 to b6 info from sheet1,
Columns b7 to b14 info from sheet2,Columns b15 to b23 info from sheet3
and Columns b24 to b26 info from sheet4
What I need now if i input any data on sheet1 in the last empty cell in columnB that is B7 i need that the info goes in sheet 5 on b7 and continue like this if i insert info in last empty cell is sheet2 that is B9 he info goes with same sequence and at the end if i have completed 90 entries on sheet 1 this 90 entries goes on sheet5 as follow B2 to B91 followed by info from sheet 2 same scanario and sht3 ,4

Thanks for ur help

Bakar

Several Password
 

Bakar,

This might work, although you'll want to password protect the code for the userform before circulating your final workbook. The idea of this series of macros is that all but one sheet are "veryhidden" when the file closes. The one visible sheet will have a message that alerts users to ensure macros are enabled, and may also include a button for unlocking the workbook (the code of which would simply be Userform1.Show).

Once you have this first sheet set up, go to the "ThisWorkbook" module and paste the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.CodeName = "Sheet1" Then
ws.Visible = True
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub

Private Sub Workbook_Open()
UserForm1.Show
End Sub

Then, create a Userform with two textboxes (username and password) and two buttons (OK and Cancel). On the password textbox, set the PasswordChar Property to "*" or another masking character if you wish. Then, in the Userform module, paste this code:

Private Sub CommandButton1_Click()
Dim i As Long
Dim ws As Worksheet
Dim sPWord(1 To 5) As String
Dim sUser(1 To 5) As String

sUser(1) = "Test"
sPWord(1) = "Testme"
'fill in others as well...

Select Case TextBox1
Case sUser(1)
i = 1
Case sUser(2)
i = 2
Case sUser(3)
i = 3
Case sUser(4)
i = 4
Case sUser(5)
i = 5
Case Else
MsgBox "Invalid User ID or Password"
Exit Sub
End Select

If TextBox2 = sPWord(i) Then
For Each ws In Worksheets
If ws.CodeName = "Sheet1" Then
'Do nothing
Else
ws.Visible = True
End If
Next ws
Sheet1.Visible = xlSheetVeryHidden
Unload Me
Else
MsgBox "Invalid User ID or Password"

End If

End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub



The first few variables will be the usernames and passwords. If the textbox info matches a username and its corresponding password, all sheets will be made visible except for Sheet1, which will become "VeryHidden". If the entries do not match, the users will receive an error message.

You can customize this code from here, but I think this will get you started.

Ben

Thanks this is fantastic
BUT I need to give access to one user only sheet 2 and now all sheets are opened
I must give to different user different sheet to work

Example user 1 sheet3 only remaining sheet is hidden etc

Thnxs again

Bakar