Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Running a Macro in Multiple Worksheets

0

I maintain a simple staff spreadsheet in a workbook containing 5 worksheets to which I have created a fairly straighforward macro to update information for those worksheets linked from the master worksheet which I have named data.

Whenever I delete a row or rows of information from the master worksheet I run the marco to refresh all the cells in the other worksheets. This systems works OK other than the fact that it becomes tedius to repeat the process for every worksheet.

The macro code is shown below;

Sub Refresh_data()
'
' Refresh_data Macro
' Updates all cells of MASTER worksheet to reflect changes to data worksheet
' Keyboard Shortcut: Ctrl+Shift+R
    Range("A3:AV3").Select
    Selection.AutoFill Destination:=Range("A3:AV1795"), Type:=xlFillDefault
    Range("A3:AV1795").Select
    ActiveWindow.ScrollRow = 1770
    ActiveWindow.ScrollRow = 1766
    ActiveWindow.ScrollRow = 1763
    ActiveWindow.ScrollRow = 1753
    ActiveWindow.ScrollRow = 1747
    ActiveWindow.ScrollRow = 1667
    ActiveWindow.ScrollRow = 1535
    ActiveWindow.ScrollRow = 1512
    ActiveWindow.ScrollRow = 1509
    ActiveWindow.ScrollRow = 1506
    ActiveWindow.ScrollRow = 1419
    ActiveWindow.ScrollRow = 1172
    ActiveWindow.ScrollRow = 992
    ActiveWindow.ScrollRow = 986
    ActiveWindow.ScrollRow = 976
    ActiveWindow.ScrollRow = 802
    ActiveWindow.ScrollRow = 635
    ActiveWindow.ScrollRow = 590
    ActiveWindow.ScrollRow = 587
    ActiveWindow.ScrollRow = 581
    ActiveWindow.ScrollRow = 510
    ActiveWindow.ScrollRow = 382
    ActiveWindow.ScrollRow = 295
    ActiveWindow.ScrollRow = 285
    ActiveWindow.ScrollRow = 282
    ActiveWindow.ScrollRow = 189
    ActiveWindow.ScrollRow = 144
    ActiveWindow.ScrollRow = 57
    ActiveWindow.ScrollRow = 47
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 3
    Range("B1").Select
End Sub

Ideally I would like to have the marco run on all worksheets except the master worksheet and have the marco run by a button or icon in the taskbar.

Is this possible? 

What code would I need to add to do this?

I am self taught on Excel and keen to improve especially in the Marco & VBA area.

I welcome any assistance or advice that is forthcoming.

Thank you

Answer
Discuss

Answers

0

assume your worksheet you do NOT want macro ran on is 'Data' :

Sub Test()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Data" Then

   
    '***** the rest of your macro code goes here *****

   
  End If
Next ws
End Sub

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login