Hi There

I would like to create a macro to transfer information between two password protected sheets when users click on a button.

Sheets in question

***Source sheet is called "variation tracking register" and the defined name for the source data range is called Variation_tracking_data (='Variation tracking register'!$B$16:$D$165)

***Destination sheet is called "progress claim breakdown" and the data needs to be pasted (paste special - values only) into cells b82:d231 on that sheet (no defined name ATM but I am happy to set one up if it makes it easier).

That's the easy part.

Once the data has been pasted into the destination cells, I would like to then automatically hide all the rows that have no user entered data in them. I say "user entered data" because the source sheet is populated by way of a series of defined names from other sheets and if users have not entered any values each cell in the range contains a 0 (zero), which I have hidden using conditional formatting.

After the data has been pasted and the empty rows have been hidden, I would like to password protect both sheets again and select cell E82 on the "progress claim breakdown" sheet.

I already have some code, which does most of this automatically whenever any of the data changes in any of the sheets but it causes the workbook to pause and flash every time you make a change on any of the 152 sheets in the workbook and this detracts from its usability. Hence my desire to change this to a macro initiated only when a user clicks a button.

Cheers

Marcus

Existing code below

Private Sub Worksheet_Calculate()
Sheets("Progress Claim Breakdown").Unprotect Password:="secret"
Dim i As Long, CellsAcrossRow

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
For i = 82 To 231
If WorksheetFunction.CountIf(Range("B" & i & ":D" & i), 0) = 3 Then
Rows(i).Hidden = True
Else
Rows(i).Hidden = False
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
Sheets("Progress Claim Breakdown").Protect Password:="secret"
End Sub