How to Edit Multiple Excel Worksheets Simultaneously and Automatically Rename Multiple Worksheets

This video shows how you can edit data on multiple worksheets simultaneously, and then rename multiple selected worksheets automatically.

In this example we have data on seven different worksheets arranged in the same columns (A to C) and the same rows (1 to 15) on each worksheet.

We want to redact the content of each worksheet except Franklin, and also redact the worksheet names.

I'm using a redaction macro to redact the data on the first worksheet. See www.litigationsupporttipofthenight.com for information on this macro at a later date.

Copy the merged cell which results from the use of the redaction macro and then select each of the worksheets except for Franklin. To facilitate this you can right click on any worksheet and select 'Select All Sheets' and then deselect certain worksheets holding down the CTRL key.

Paste the contents of the clipboard on the first selected worksheet. You will find that it gets copied to all of the selected worksheets.

Next we're going to rename all of the worksheets except for Franklin in order to indicate that they were redacted.

Press ALT + F11 to Open Visual Basic.

Right click on the spreadsheet name in the Project menu and select Insert . . . Module. Then paste in this macro:

Sub Sheets_Naming_current()

Dim ws As Worksheet, wb As Workbook Dim count As Integer

Set wb = ActiveWorkbook count = 2 On Error Resume Next

For Each ws In ActiveWindow.SelectedSheets With Sheets("Sheet1") If ws.Name <> "Sheet1" And .Cells(count, 1) <> "" Then ws.Name = .Cells(count, 1) count = count + 1 End If End With Next ws

End Sub

Thanks to Santosh for posting this macro here: http://stackoverflow.com/questions/16757465/rename-selected-sheets-with-names-from-a-list

We use this macro, which makes reference to Sheet1, and the first column on that spreadsheet by designating - count, 1 - in this case 1 equaling column A.

Insert a new worksheet and enter the new names for the worksheets in column A.

This macro will only work if one or more worksheets are selected, and the worksheet you create matches the reference in the VBA code - in this case 'Sheet1'.

We press play in Visual Basic to run the macro . . . .

. . . and Presto! the selected worksheets are renamed.