nitrammada
Board Regular
- Joined
- Oct 10, 2018
- Messages
- 78
- Office Version
- 365
- Platform
- Windows
Hi Everyone,
I've had tremendous help from so many on this forum to get me this far, I have adopted peoples suggestions and the code I have below works, but it only works on the active sheet.
I'm trying to get this to work on the very hidden sheets. Would any one be kind enough to tell me where I'm going wrong please? I'm trying to copy a formula to cell W7 and then copy the formula from cell W7 to cell W8 downwards. Data in column D dictates the number of rows in each sheet. The last row will vary on each of the 140 odd very hidden sheets.
Thanks in advance for any suggestions.
adam
I've had tremendous help from so many on this forum to get me this far, I have adopted peoples suggestions and the code I have below works, but it only works on the active sheet.
I'm trying to get this to work on the very hidden sheets. Would any one be kind enough to tell me where I'm going wrong please? I'm trying to copy a formula to cell W7 and then copy the formula from cell W7 to cell W8 downwards. Data in column D dictates the number of rows in each sheet. The last row will vary on each of the 140 odd very hidden sheets.
VBA Code:
Sub CopyToW8()
'Copy & paste formula to all sheets on each row in col W
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect
Dim lWS_Visible_State As Long
Dim ws As Worksheet
For Each ws In Worksheets
' lWS_Visible_State = ws.Visible
' ws.Visible = xlSheetVisible
ws.Unprotect
If ws.Range("A1") = "L2" And ws.Range("M6") = "H5" Then
ws.Range("W7") = "=PRODUCT(Q7:V7)"
End If
' ws.Visible = lWS_Visible_State
ws.Protect
Next ws
Dim Count, i As Long
Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
'MsgBox Count
i = 8
If Range("A1").Value = "L2" Then
Do While i <= Count
Range("W7").Select
Selection.Copy
Range(Cells(i, 23), Cells(i, 23)).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
i = i + 1
Loop
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Thanks in advance for any suggestions.
adam