gd6noob,
I created three macros which can be made to operate with form control buttons (see below for instructions).
Following Logit's suggestion to hide sheet 1 and make it visible using a password requires the first two macros.
It is logical to hide sheet1 again after viewing... that requires a 3rd button which would go on sheet 1.
You have not furnished the following information:
1 - How many (maximum number of) columns on each of sheets 2 - 5?
2 - Are the column headers all in one row, or multiple rows? In what row/cell does the actual data begin...A2?
3 - Is there one column that will always have data down to the last row? Column A?
3 - On sheets 2 - 5...'Is the input cleared (from each sheet) every time after it is transferred to sheet1?' (Requested by keithmct in post
#4 )
So, I made the noted assumptions in the macros below. You can run them on a copy of your workbook using Alt_F11 to open the Visual Basic Editor, then paste a copy of the 3 macros into the large window that opens, close the Editor window, and SaveAs macro enabled (*.xlsm).
To run the macros go to one of the sheets 2 to 5, Press Alt+F8, then select 'AddTOSheetOne', then 'Run'. Sheet1 will become hidden.
To see sheet1 again, Press Alt+F8, then select 'ViewSheetOneWithPW' then 'Run'. You will be asked for the password...enter 'mypassword' without the single quotes, then press 'OK'.
To hide sheet1 again either run 'AddTOSheetOne' again from another sheet, OR run 'HideSheetOne'.
I have included instructions on how to install a Form Control Button below the macros.
Perpa
Code:
Sub AddTOSheetOne()
Dim LastCol, LastRow As Long
Dim wsStart As Worksheet
Set wsStart = ActiveSheet
With ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row 'Assumes column A will always have data to the last row - Change to suit
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'Assumes row 1 is the header row for ALL columns - Change to suit
Set Rng = .Range(Cells(2, 1), Cells(LastRow, LastCol)) 'Assumes cell A2 is first cell with data - Change to suit
Rng.Select
Rng.Copy ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
'wsStart.Rng.ClearContents 'Commented out to keep contents on sheets 2-5, remove single quote preceeding 'wsStart if they should be cleared
wsStart.Range("A2").Select 'Unselects copied range and puts cursor in cell A2
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetHidden
End Sub
Sub ViewSheetOneWithPW()
Dim pw As String
pw = InputBox(prompt:="Enter Password to Copy Your Sheet to Sheet1")
If pw = "mypassword" Then 'Change 'mypassword' to your password - put in double quotes
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible
End If
End Sub
Sub HideSheetOne()
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetHidden
End Sub
To add a Form Control Button to an Excel Worksheet:
You need the Developer Tab in the ribbon at the top of the sheet activated to do this. If you need this installed...
1st - Click on the Windows icon, the 4 colored boxes in the circle at the top lefthand of the screen, next to 'Home'. At the bottom of that tab click on 'Excel Options', then click on the 'Popular', then 'Show Developer tab in the Ribbon'. Then click 'OK' at the bottom of that tab. You will notice that the 'Developer' tab has been added to the ribbon. Now save your workbook.
2nd - Select the sheet where the button will go. Left click on the Developer tab. In the middle of that tab is an icon labled 'Insert'. Click on that icon and you will see 2 sets of controls, you want the 'Form Control' group, hover the cursor over the icon that looks like a rectangular button. It should say 'Button - (Form Control)'. Click on that icon, then down where you want the top LH corner to be, left click, hold and drag down to where you want the lower RH corner. The button is labled 'Button 1' by default. You can change that later, but first you should be promted to select a macro to assign to the button. Select 'AddTOSheetOne' from 'This Workbook'. You can now right click on the 'Button 1' name and you can change that to 'Copy to Sheet 1' or whatever. Then click outside the button and save your workbook again. You are now ready to use the button. Repeat to create a second button for 'ViewSheetOneWithPW'. You will need to do this for sheets 2 to 5.
Put a similar button on Sheet1 for the 'HideSheetOne' macro. Obviously, Sheet1 does not use the other 2 buttons.
