vba copy & paste to variable range

[nitrammada]

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.

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

 

[Crystalyzer]

Happy to help

 

[joseguambe]

Hi everyone

Thank you for you support; Can you please help me to change this code, istead of copying the row B13:Y13, copy the LASTROW in each worksheet to the master.

Sub Copy_Data()

Application.ScreenUpdating = False

Dim i As Integer

Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 5 To Sheets.Count

Sheets(i).Range("B13:Y13").Copy Cells(Lastrow, 2)

Cells(Lastrow, 1).Value = Sheets(i).Name

Lastrow = Lastrow + 1

Next

Application.ScreenUpdating = True

End Sub

 

[nitrammada]

Hi everyone

Thank you for you support; Can you please help me to change this code, istead of copying the row B13:Y13, copy the LASTROW in each worksheet to the master.

Sub Copy_Data()

Application.ScreenUpdating = False

Dim i As Integer

Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 5 To Sheets.Count

Sheets(i).Range("B13:Y13").Copy Cells(Lastrow, 2)

Cells(Lastrow, 1).Value = Sheets(i).Name

Lastrow = Lastrow + 1

Next

Application.ScreenUpdating = True

End Sub

Hello Joseguambe, welcome to Mr Excel.
I'm sorry but I do not feel very qualified to answer your question as I am a beginner too. However, you have pasted you question on the thread of my question so not many people will see it. Could I suggest you paste your question in the main forum. That way 1000's of experts will be able to see your question and help you. Try and paste your code in the VBA code window with the button that looks like this:
"</>" it will assist others and make it easier to read. All the best.
Adam

 

[joseguambe]

Hello Joseguambe, welcome to Mr Excel.
I'm sorry but I do not feel very qualified to answer your question as I am a beginner too. However, you have pasted you question on the thread of my question so not many people will see it. Could I suggest you paste your question in the main forum. That way 1000's of experts will be able to see your question and help you. Try and paste your code in the VBA code window with the button that looks like this:
"</>" it will assist others and make it easier to read. All the best.
Adam

Great thanks, I will do that.