multiple workbooks

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Multiple workbooks

Multiple workbooks

resolvedResolved · Medium Priority · Version 2016

Sara has attended:
Excel Intermediate course

Multiple workbooks

Protection over multiple pages rather than individual

and Data Manipulation

RE: Multiple workbooks

Hi Sara,

Thank you for the forum question.

As I said on the course, you will need VBA codes to protect many sheets.

The code below will protect all sheets in the workbook with the same password. When you execute the code you will be prompt for a password.


Sub ProtectAll()

Dim wSheet As Worksheet

Dim Pwd As String

Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")

For Each wSheet In Worksheets

wSheet.Protect Password:=Pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True, _

AllowFormattingColumns:=True, AllowFormattingRows:=True

Next wSheet

End Sub


If you want to unprotect all sheets you can use the code below.

Sub UnProtectAll()

Dim wSheet As Worksheet

Dim Pwd As String

Pwd = InputBox("Enter your password to unprotect all worksheets", "Password Input")

On Error Resume Next

For Each wSheet In Worksheets

wSheet.Unprotect Password:=Pwd

Next wSheet

If Err <> 0 Then

MsgBox "You have entered an incorrect password. All worksheets could not " & _

"be unprotected.", vbCritical, "Incorrect Password"

End If

On Error GoTo 0

End Sub



I do not know if you have any knowledge of adding the code to the file.

The link below will show you how to get the developer tab in Excel.

https://www.youtube.com/watch?v=JLQ8OuW0FlY

The link below will show you how to create a module in Excel from the deloper tab where you need to paste the code above.

https://www.youtube.com/watch?v=IIuKCxPlaTY



When you have added the code you must save the workbook as a macro enable workbook (.xlsm instead of .xlsx)

I am not sure what you mean by "and Data Manipulation". Please be more specific and I will be happy to help you.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Multiple workbooks

Hi,

Is there a way of setting up formulas so that I can the just put in the formulas and it pulls the relevant data across be it text or number.

Sara

RE: Multiple workbooks

Hi Sara,

I am not sure I understand the question, but if you mean if there is a way of setting up a formula that reference data in another workbook, the answer is yes.

You will need to put the file path, the name of the file inside square brackets, the sheet name, and cell references inside the formula.

In the example below I use a sum function to reference a data range in an external workbook.

=SUM('D:\usb\Excel Intermediate\Muliple workbooks\[Multiple South.xlsx]Sheet1'!$B$3:$D$3)

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Server loaded in 0.12 secs.